代码之家 › 专栏 › 技术社区 › Ben G

MySQL连接帮助

join mysql sql

1

Ben G · 技术社区 · 15 年前

我有两个表,Sales(ISBN,Sale_Time,[销售数据字段])和Affiliate_Sales(ISBN,Affiliate_Sale-Time,[Affiliate Data Fields])。

我想查询给定一组ISBN的所有未过期销售(24小时前)和未过期关联方销售(72小时前)。即使有未到期的销售,但没有未到期的关联方销售,我也希望有一行返回给定的ISBN,反之亦然(但如果没有,则不应返回任何内容)。我认为这被称为完全加入。

我不认为工会会在这里工作,因为除了ISBN以外,所有领域的表格都不一样。

这个问题的答案是一个查询,它执行正确的连接并检查两个表中的过期时间。

更新:正如你从我下面的评论中看到的,我正在尝试获取所有的销售数据和所有的关联方销售数据,而不仅仅是ISBN和times。

ISBN    Sale_DateTime   [Sale Fieldsâ¦]  Affiliate_Sale_DateTime [Affiliate Sale Fieldsâ¦]
1   11/6/2010 11:28    All the sale Fields  11/6/2010 0:28     All the affiliate Sale fields
2   NULL               NULLs                11/6/2010 0:28     All the affiliate Sale fields
3   11/6/2010 11:28    All the sale Fields  NULL               NULLs

如果要实现这一点,需要在SQL端进行更多的处理,而不是像Bruno的长查询所建议的那样只执行两个基本的SELECT查询,那么也许我应该只执行两个呢?

4 回复 | 直到 15 年前

1

0

Unreason 15 年前

定义需求

让我给你列出

您希望列出isbn,它是两个表中两个特定子集(未过期销售和未过期关联销售)的元素之一
你不要空的

(我使用术语子集是因为在概念级别上,指定未过期子集的条件并不重要)

这里有几个暗示

必须另一方为空;这与3相矛盾

如果ISBN是唯一的

现在,如果您的isbn在两个表中都是唯一的,那么Bruno的答案似乎指向了正确的方向(虽然没有完全通过他的SQL,但看起来确实有点过于复杂)。

在mysql中实现外部连接的方法有

UNION ALL of LEFT JOIN和RIGHT EXCLUSION JOIN(UNION ALL可能更快,特别是在大结果集上);此选项具有直接对称的等价物(UNION ALL of RIGHT JOIN和LEFT EXCLUSION JOIN)
合并所有内部联接、左排除联接和右排除联接(如果选择性很高,内部联接可能比左联接快得多如果排除联接可以使用索引)

如果此处使用的某些术语不清楚,请参见 Common MySQL Queries

如果ISBN不是唯一的

编辑澄清后,您可以尝试此解决方案

SELECT * FROM Sales a LEFT JOIN Affiliate_Sales b ON a.ISBN=b.ISBN
WHERE TIMESTAMPDIFF(HOUR, Sale_DateTime, Now()) < 24 
      AND (TIMESTAMPDIFF(HOUR, Affiliate_Sale_DateTime, Now()) < 72 
      OR Affiliate_Sale_DateTime IS NULL)
UNION ALL
SELECT * FROM Sales a RIGHT JOIN Affiliate_Sales b ON a.ISBN=b.ISBN
WHERE TIMESTAMPDIFF(HOUR, Affiliate_Sale_DateTime, Now()) < 72 
      AND Sale_DateTime IS NULL;

2

0

DylanW 15 年前

如果使用“AS”定义要返回的字段的名称以便它们匹配,则UNION应该起作用。例如:

SELECT ISBN, Sale_Time FROM Sales
UNION ALL
SELECT ISBN, Affiliate_Sale_Time AS Sale_Time FROM Affiliate_Sales

3

0

Vinnie 15 年前

如果销售表中始终有ISBN,则可以完全加入并获得所需的结果。否则,您将需要左加入。

select *
from Sales s
FULL OUTER JOIN Affiliate_Sales asa on asa.ISBN = s.ISBN
where DATEDIFF(hh, s.Sale_Time, GETDATE()) < 24
OR DATEDIFF(hh, asa.Sale_Time, GETDATE()) < 72

编辑-尝试以上操作或以下操作:

SELECT ISBN, Sale_Time 
FROM Sales s 
where DATEDIFF(hh, s.Sale_Time, GETDATE()) < 24
UNION ALL
SELECT ISBN, Affiliate_Sale_Time AS Sale_Time 
FROM Affiliate_Sales asa 
where DATEDIFF(hh, asa.Sale_Time, GETDATE()) < 72

编辑-选择不匹配字段的示例:

select itemid, null, path, null, name
from Catalog

union all

select itemid, dsid, null, username, null
from DataSource

4

0

Bruno Gautier 15 年前

FULL OUTER JOIN 问题,但不幸的是,MySQL目前不支持这一点。

幸运的是,我们可以模仿在表A和表B之间:

从表a到表B的左外部联接,用于捕获a中不在B中的行以及a和B之间的行匹配

由于您的表在除ISBN以外的所有字段上都不同,我们需要分两步来完成:

首先我们需要提取符合到期时间标准的isbn
然后我们可以从两个表中检索先前选择的isbn的信息

CREATE TABLE sales ( 
   isbn INT NOT NULL
  ,sale_time TIMESTAMP NOT NULL
  ,sale_value VARCHAR(100)
  ,PRIMARY KEY (isbn)
);
CREATE TABLE affiliate_sales ( 
   isbn INT NOT NULL
  ,sale_time TIMESTAMP NOT NULL
  ,affiliate_sale_value VARCHAR(100)
  ,PRIMARY KEY (isbn)
);

INSERT INTO sales (isbn,sale_time,sale_value) VALUES (1,TIMESTAMPADD(HOUR,-30,NOW()),'expired_sale');
INSERT INTO sales (isbn,sale_time,sale_value) VALUES (2,TIMESTAMPADD(HOUR,-34,NOW()),'expired_sale');
INSERT INTO sales (isbn,sale_time,sale_value) VALUES (3,TIMESTAMPADD(HOUR,-23,NOW()),'unexpired_sale');
INSERT INTO sales (isbn,sale_time,sale_value) VALUES (4,TIMESTAMPADD(HOUR,-12,NOW()),'unexpired_sale');
INSERT INTO sales (isbn,sale_time,sale_value) VALUES (5,TIMESTAMPADD(HOUR,-12,NOW()),'unexpired_sale_only');

INSERT INTO affiliate_sales (isbn,sale_time,affiliate_sale_value) VALUES (1,TIMESTAMPADD(HOUR,-74,NOW()),'expired_affiliate_sale');
INSERT INTO affiliate_sales (isbn,sale_time,affiliate_sale_value) VALUES (2,TIMESTAMPADD(HOUR,-54,NOW()),'unexpired_affiliate_sale');
INSERT INTO affiliate_sales (isbn,sale_time,affiliate_sale_value) VALUES (3,TIMESTAMPADD(HOUR,-80,NOW()),'expired_affiliate_sale');
INSERT INTO affiliate_sales (isbn,sale_time,affiliate_sale_value) VALUES (4,TIMESTAMPADD(HOUR,-12,NOW()),'unexpired_affiliate_sale');
INSERT INTO affiliate_sales (isbn,sale_time,affiliate_sale_value) VALUES (6,TIMESTAMPADD(HOUR,-44,NOW()),'unexpired_affiliate_sale_only');

下面是提取所需数据的查询(抱歉,格式不正确,我找不到如何在pre块中正确显示它):

SELECT unexpired_sal.isbn
, sal.sale_time, sal.sale_value
, afs.sale_time affiliate_sale_time, afs.affiliate_sale_value
FROM (
SELECT sal.isbn

SELECT isbn FROM sales
WHERE TIMESTAMPDIFF(HOUR,sale_time,NOW()) < 24
) sal
LEFT JOIN (
SELECT isbn FROM affiliate_sales
WHERE TIMESTAMPDIFF(HOUR,sale_time,NOW()) < 72
) afs
ON afs.isbn = sal.isbn
UNION ALL
SELECT afs.isbn
从(
从销售中选择isbn

)萨尔
RIGHT JOIN (
从关联方销售中选择isbn

)自动售检票机
关于afs.isbn=sal.isbn
WHERE sal.isbn IS NULL
) unexpired_sal

SELECT * FROM sales
其中TIMESTAMPDIFF(小时,销售时间,NOW())<24
)萨尔
ON sal.isbn = unexpired_sal.isbn
左连接(
SELECT * FROM affiliate_sales

)自动售检票机
ON afs.isbn = unexpired_sal.isbn
;

得到以下输出:

+------+---------------------+---------------------+---------------------+-------------------------------+
| isbn | sale_time           | sale_value          | affiliate_sale_time | affiliate_sale_value          |
+------+---------------------+---------------------+---------------------+-------------------------------+
|    3 | 2010-11-06 11:28:08 | unexpired_sale      |                NULL | NULL                          |
|    4 | 2010-11-06 22:28:08 | unexpired_sale      | 2010-11-06 22:28:08 | unexpired_affiliate_sale      |
|    5 | 2010-11-06 22:28:08 | unexpired_sale_only |                NULL | NULL                          |
|    2 |                NULL | NULL                | 2010-11-05 04:28:08 | unexpired_affiliate_sale      |
|    6 |                NULL | NULL                | 2010-11-05 14:28:08 | unexpired_affiliate_sale_only |
+------+---------------------+---------------------+---------------------+-------------------------------+
5 rows in set (0.00 sec)