时间戳

首页 > 解决方案 > 如何使用 SQL select 过滤具有类别的行以根据条件连接其他（多个）表？

sql - 如何使用 SQL select 过滤具有类别的行以根据条件连接其他（多个）表？

问题描述

我有以下 4 个表：

**TABLE.INFO**
ID_INFO|INFO_LABLE|CODE_INFO|CATEGORY
2222001|XXXXCCCAA001|88888001|ITEM
2222002|XXXXCCCAA002|88888002|PICKUP
2222003|XXXXCCCAA003|88888002|ITEM
2222004|XXXXCCCAA004|88888001|PICKUP
2222005|XXXXCCCAA005|88888001|PICKUP
2222006|XXXXCCCAA006|88888003|SUPPLIER

**TABLE.ITEM**
ID_ITEM|ID_INFO|DETAIL_ITEM|EXPIRE_DATE
A00001|2222001|COOKIE|5/12/2017
A00002|2222003|PIE|24/11/2017

**TABLE.PICKUP**
ID_PICKUP|ID_INFO|DETAIL_PICKUP|EXPIRE_DATE
P00001|2222004|MOTOR|8/12/2017
P00002|2222005|CAR|26/11/2017
P00003|2222002|TRUCK|2/10/2017

**TABLE.SUPPLIER**
ID_SUPPLIER|ID_INFO|DETAIL_SUPPLIER|EXPIRE_DATE
S00001|2222006|BANANA GREEN|8/12/2017

我想获得的结果是选择 TABLE.INFO 上的所有字段，条件只有 EXPIRE_DATE < '2017-11-31' 和 CODE_INFO in (88888002,88888001) 从加入其他表。IE

ID_INFO|INFO_LABLE|CODE_INFO|CATEGORY
2222002|XXXXCCCAA002|88888002|PICKUP
2222003|XXXXCCCAA003|88888002|ITEM
2222005|XXXXCCCAA005|88888001|PICKUP

如何使用连接表和 where 子句来实现这一点？我做了如下，但它不工作。

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
JOIN TABLE.ITEM AS B
ON A.ID_INFO = B.ID_INFO
JOIN TABLE.PICKUP AS C
ON A.ID_INFO = C.ID_INFO
JOIN TABLE.SUPPLIER AS D
ON A.ID_INFO = D.ID_INFO
WHERE A.CODE_INFO IN (88888002,88888001) AND B.DATE_EXPIRE < '2017-11-31' OR C.DATE_EXPIRE < '2017-11-31' OR D.DATE_EXPIRE < '2017-11-31';

如果每个表都有很大的记录。对于连接到每个表的顺序进程，它会使查询过程时间很长。是否有任何其他建议，当 TABLE.INFO 将加入另一个表（TABLE.ITEM、TABLE.PICKUP、TABLE.SUPPLIER）时，首先根据 TABLE.INFO 中的 CATEGORY 字段确定？

标签： sqlwhere-clausedb2-400

解决方案

试试看 ：

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.ITEM AS B
ON A.ID_INFO = B.ID_INFO AND B.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

UNION ALL

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.PICKUP AS C
ON A.ID_INFO = C.ID_INFO AND C.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

UNION ALL

SELECT A.ID_INFO, A.INFO_LABLE, A.CODE-INFO, A.CATEGORY
FROM TABLE.INFO AS A
INNER JOIN TABLE.SUPPLIER AS D
ON A.ID_INFO = D.ID_INFO AND  D.DATE_EXPIRE < '2017-11-31'
WHERE A.CODE_INFO IN (88888002,88888001)

推荐阅读