将两个查询合并为一个

发布于 2024-11-18 01:50:00 字数 550 浏览 2 评论 0原文

我不确定是否可以在一个查询中执行此操作，但我愿意。

我需要这样的所有记录：

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a, tbl_b AS b
 WHERE a.field_1 = b.field_3

并且我还想排除属于这种情况的这些记录：

IF a.field_1 IN (1,2,3,4) 
AND a.field_date < NOW()

更新：（抱歉造成混淆）

因此，如果 field_1 等于 1,2,3 或 4 并且有一个日期时间戳，即今天我需要显示记录（添加到结果）
并且如果 field_1 等于 1、2、3 或 4 并且日期时间戳小于今天（将其从结果中删除）。

将两个结果放入一个查询中的任何想法

注释（如果这有区别）：

field_a 可以有一个值 az，1-99（仅限两个字符）

原文

I'm not sure if I can do this in one query but I would like to.

I need all records like this:

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a, tbl_b AS b
 WHERE a.field_1 = b.field_3

And I would like to also exclude these records that fall into this condition:

IF a.field_1 IN (1,2,3,4) 
AND a.field_date < NOW()

UPDATE: (sorry for the confusion)

So if field_1 equals 1,2,3 or 4 and has a date timestamp that is today I need to display the record (add to results)
And if field_1 equals 1,2,3 or 4 and has a date timestamp that is less than today (remove it from the results).

Any ideas to get both results into one query

Notes (if this makes a difference):

field_a can have a value a-z, 1-99 (two characters only)

分享到QQ

分享到微博

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

发布评论

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

猫卆 2024-11-25 01:50:00

像这样的东西吗？

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a, tbl_b AS b
WHERE a.field_1 = b.field_3
AND (
CASE WHEN a.field_1 IN (1,2,3,4) THEN
      CASE WHEN A.FIELD_DATE = NOW() THEN 1 ELSE 0
      END
ELSE 1
END) = 1

Something like this?

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a, tbl_b AS b
WHERE a.field_1 = b.field_3
AND (
CASE WHEN a.field_1 IN (1,2,3,4) THEN
      CASE WHEN A.FIELD_DATE = NOW() THEN 1 ELSE 0
      END
ELSE 1
END) = 1

回复收藏 0 原文

云淡风轻 2024-11-25 01:50:00

这是一个应该可以工作的代码：

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a
INNER JOIN tbl_b AS b ON a.field_1 = b.field_3
WHERE 
(a.field_1 IN (1,2,3,4) AND a.field_date = NOW())
OR
(a.field_1 NOT IN (1,2,3,4))

请注意，如果 a.field 是 unsigned int != 0，您可以替换：
a.field_1 IN (1,2,3,4) 且 a.field_1 <=4
且 a.field_1 NOT IN (1,2,3,4) 且 a.field_1 > 4

如果出现大型记录集问题，这将使速度更快

here's a code that should work:

SELECT a.field_1, a.field_2, b.field_3, b.field_4
FROM tbl_a AS a
INNER JOIN tbl_b AS b ON a.field_1 = b.field_3
WHERE 
(a.field_1 IN (1,2,3,4) AND a.field_date = NOW())
OR
(a.field_1 NOT IN (1,2,3,4))

Note that if a.field is unsigned int != 0 you can replace:
a.field_1 IN (1,2,3,4) with a.field_1 <=4
and a.field_1 NOT IN (1,2,3,4) with a.field_1 > 4

this will make it faster in case a large recordset was in question

回复收藏 0 原文

我们的影子 2024-11-25 01:50:00

让我们用适当的 JOIN 重写它，并在要排除的条件上添加 NOT ：

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE NOT (a.field_1 IN (1,2,3,4) AND a.field_date < NOW())

由于 NOT( X AND Y ) 相当于 (NOT X) OR (NOT Y) 你可以将其重写为：

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE a.field_1 NOT IN (1,2,3,4) OR a.field_date >= NOW())

但我们还需要知道 field_1 或 field_date 中的任何一个是否可以为 NULL。如果是这种情况，则如果 foo IS NULL，则表达式“foo NOT IN (blah)”将返回 NULL。所以你需要更具体地说明这一点。

Let's rewrite this with a proper JOIN, and add a NOT on the condition to exclude :

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE NOT (a.field_1 IN (1,2,3,4) AND a.field_date < NOW())

Since NOT( X AND Y ) is equivalent to (NOT X) OR (NOT Y) you can rewrite this as :

SELECT a.field_1, a.field_2, b.field_3, b.field_4
  FROM tbl_a AS a JOIN tbl_b AS b ON (a.field_1 = b.field_3)
WHERE a.field_1 NOT IN (1,2,3,4) OR a.field_date >= NOW())

But we'd also need to know if any of field_1 or field_date can be NULL. If that is the case, the expression "foo NOT IN (blah)" returns NULL if foo IS NULL. So you'd need to be more specific about that.

回复收藏 0 原文

~没有更多了~