SQL 查询给出错误结果

发布于 2024-11-07 06:32:02 字数 619 浏览 0 评论 0原文

当我运行以下查询时，它仅显示 1 行。基于“created_date”条件，就好像我删除“created_date”条件并只保留“status_change_date”条件一样，它显示 3 行。

我想查看在指定时间内创建或修改的所有项目。我无法找到查询中有什么问题

`SELECT DISTINCT products.id AS product, status.id AS stat, COUNT(*) AS total FROM items 
      INNER JOIN products ON (items.fk_product_id = products.id) 
      INNER JOIN status ON (items.fk_status = status.id) 
      WHERE DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
      OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13' 
      AND status.id = 2 
      GROUP BY products.name, status.name order by products.name`

原文

When i run following query it shows only 1 row. Based on "created_date" condition, where as if i remove "created_date" condition and just keep "status_change_date" condition it shows 3 rows.

I want to see all items those were created or modified in specified time. I am unable to find whats wrong in the query

`SELECT DISTINCT products.id AS product, status.id AS stat, COUNT(*) AS total FROM items 
      INNER JOIN products ON (items.fk_product_id = products.id) 
      INNER JOIN status ON (items.fk_status = status.id) 
      WHERE DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
      OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13' 
      AND status.id = 2 
      GROUP BY products.name, status.name order by products.name`

分享到QQ

分享到微博

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

发布评论

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

忆梦 2024-11-14 06:32:02

我有一种感觉，条件正在这样评估

status_change_date or (created_date and status.id = 2)

您可以尝试使用括号来指定条件分组

( DATE(items.status_change_date)... OR DATE(items.created_date)... )
    AND status.id = 2

I have a feeling the conditions are being evalulated like this

status_change_date or (created_date and status.id = 2)

You might try using parentheses to designate the grouping of your conditions

( DATE(items.status_change_date)... OR DATE(items.created_date)... )
    AND status.id = 2

回复收藏 0 原文

天涯离梦残月幽梦 2024-11-14 06:32:02

您的 WHERE 子句看起来错误，请尝试：

WHERE 
    (DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
    OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13')
        AND status.id = 2 
        GROUP BY products.name, status.name order by products.name

Your WHERE clause looks wrong, try:

WHERE 
    (DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
    OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13')
        AND status.id = 2 
        GROUP BY products.name, status.name order by products.name

回复收藏 0 原文

GRAY°灰色天空 2024-11-14 06:32:02

将日期条款放在括号中：

  WHERE (DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
  OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13')
  AND status.id = 2

Put brackets around your date clauses:

  WHERE (DATE(items.status_change_date) BETWEEN '2011-04-13' AND '2011-05-13' 
  OR DATE(items.created_date) BETWEEN '2011-04-13' AND '2011-05-13')
  AND status.id = 2

回复收藏 0 原文

~没有更多了~