使用 AND 、 JOIN 对多个列进行 IN 子句

发布于 2024-08-21 04:31:17 字数 756 浏览 9 评论 0原文

以下哪些查询是正确的

SELECT
 ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE
 STATUS IN('OPEN','UPDATED')
AND
 ITEM_TYPE IN ('ITEM1','ITEM2')
AND
 CREATED_TIME BETWEEN 'XX' AND 'YY'
AND
 UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         STATUS IN('OPEN','UPDATED')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         ITEM_TYPE IN ('ITEM1','ITEM2')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         CREATED_TIME BETWEEN 'XX' AND 'YY'
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

原文

Which of the following queries are correct

SELECT
 ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE
 STATUS IN('OPEN','UPDATED')
AND
 ITEM_TYPE IN ('ITEM1','ITEM2')
AND
 CREATED_TIME BETWEEN 'XX' AND 'YY'
AND
 UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         STATUS IN('OPEN','UPDATED')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         ITEM_TYPE IN ('ITEM1','ITEM2')
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         CREATED_TIME BETWEEN 'XX' AND 'YY'
 JOIN

 SELECT
         ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
 WHERE
         UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏日浅笑〃 2024-08-28 04:31:17

第一个是正确的，除非您想要自连接呈指数爆炸式增长。

回复收藏 0 原文

野心澎湃 2024-08-28 04:31:17

第一个

JOINS 必须位于 WHERE 子句之前，

尽管您可以使用 EXISTS 重写 JOINS，然后将其放入 WHERE 子句中

回复收藏 0 原文

一梦等七年七年为一梦 2024-08-28 04:31:17

第一个查询是正确的，第二个查询会给出，嗯....很多结果。

为了回应您的评论，正如 SQLMenace 指出的那样，如果您想匹配 任何条件：

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE STATUS IN('OPEN','UPDATED')
   OR ITEM_TYPE IN ('ITEM1','ITEM2')
   OR CREATED_TIME BETWEEN 'XX' AND 'YY'
   OR UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

或者，如果您想始终限制时间，但满足第一个条件中的任一，则使用换行的 OR：

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE (STATUS IN('OPEN','UPDATED') OR ITEM_TYPE IN ('ITEM1','ITEM2'))
  AND CREATED_TIME BETWEEN 'XX' AND 'YY'
  AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

The first query is correct, the second would give, well....a LOT of reuslts.

In response to your comment, as SQLMenace pointed out, use OR instead if you want to match any of the conditions:

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE STATUS IN('OPEN','UPDATED')
   OR ITEM_TYPE IN ('ITEM1','ITEM2')
   OR CREATED_TIME BETWEEN 'XX' AND 'YY'
   OR UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

Or, if you wanted to always restrict on the time, but meet either of the first conditions, then use a wrapped OR:

SELECT ID, STATUS, ITEM_TYPE,CREATED_TIME,UPDATED_TIME
WHERE (STATUS IN('OPEN','UPDATED') OR ITEM_TYPE IN ('ITEM1','ITEM2'))
  AND CREATED_TIME BETWEEN 'XX' AND 'YY'
  AND UPDATED_TIME BETWEEN 'XX' AND 'ZZ'

回复收藏 0 原文

~没有更多了~