代码之家 › 专栏 › 技术社区 › Alec

MySQL:在HAVING子句之前限制结果集的问题

mysql

Alec · 技术社区 · 14 年前

我一直在以一种有效的方式从MySQL数据库中检索数据。

我有一张桌子,上面摆着很多东西。每个项目可以有状态1、2或3。我想选择具有特定状态的项目,这取决于到其他表中这些记录的链接。

例子:

一张桌子 items 带字段:ID、名称、状态…
一张桌子 bought 带字段:itemid、userid
一张桌子 rented 带字段:itemid、userid

说我要这个给userid 123 :

所有购买的记录,状态1
所有租用的记录,状态为1或2
所有未购买或租赁的记录,状态3

我的选择查询现在看起来如下所示:

SELECT
  i.id,
  i.name,
  i.status,
  // bought by this user?
  SUM(IF(b.userId = 123, 1, 0)) AS bought,
  // rented by this user?
  SUM(IF(r.userId = 123, 1, 0)) AS rented,

FROM items i

LEFT JOIN bought b
  ON b.itemId = i.id

LEFT JOIN rented r
  ON r.itemId = r.id

GROUP BY i.id

HAVING
  // bought and status 1
  (bought > 0 AND status = 1)
  // rented and status 1 or 2
  OR (rented > 0 AND status IN (1, 2)
  // not bought or rented and status 3
  OR (bougth = 0 AND rented = 0 AND status = 3)

ORDER BY i.name ASC

问题1
select子句中的sum部分是否是确定另一个表中是否有链接到某个项的条目的好方法?假设每个用户只有一个条目,那么总和将是1或0,这就提供了我需要的信息。但似乎……奇怪的是。

问题2
尽管这是可行的,但有一个大问题:它基本上可以检索 所有项目 然后使用 HAVING 条款。由于有相当多的条目,这样的查询速度太慢了。我正在设法解决这个问题。

我第一次尝试 WHERE 条款。。但是怎么做呢?

...
WHERE
  // only items with status 1 if bought or rented
  (t.status = 1 AND (bought > 0 OR rented > 0))
  // only items with status 2 if rented
  OR (t.status = 2 AND rented > 0)
  // only items with status 3 if not bought or rented
  OR (t.status = 3 AND bought = 0 AND rented = 0)
...

但不能使用 SELECT 条款。因为没有柱子 租来的 或 买了 在项目表中,这不起作用。

我还尝试使用用户可定义变量,但这也不起作用:

SELECT
  ...
  @bought := SUM(IF(b.userId = 123, 1, 0)) AS bought,
...
WHERE @bought = ... // does not work

然后我尝试了一个子查询,但我无法让它使用主查询项ID:

...
WHERE
  ...
  // only items with status 2 if rented
  OR (
    t.status = 2
    AND (
      SELECT COUNT(r2.userId)
      FROM rented r2
      WHERE r2.userId = 123
        AND r2.itemId = i.itemId // it doesn't recognize i.itemId
    ) > 0
  )
  ...

有什么想法吗?我还想将所有内容保存在一个查询中。这只是一个简单的例子,但实际的例子相当大。我相信我可以把所有的东西分开,使用各种查询来分别收集所有的东西,但这只会增加很多更多的代码,并不会使可维护性变得更容易。

1 回复 | 直到 14 年前

SteveCav Flater 14 年前

使用两个子查询(一个用于购买,一个用于租用),然后左键将它们联接到主查询中的用户表中。

编辑: 原谅我的MySQL,已经有一段时间了,但我想的是:

select i.id, i.name, i.status, ifnull(b.TotalBought,0) AS ItemsBought, ifnull(r.TotalRented,0) AS ItemsRented
FROM items i 
LEFT JOIN (select itemid, COUNT(*) AS TotalBought FROM bought WHERE userid=123 GROUP BY itemid) AS b ON b.itemid=i.itemid
LEFT JOIN (select itemid, COUNT(*) AS TotalRented FROM rented WHERE userid=123  GROUP BY itemid) AS r ON r.itemid=i.itemid
WHERE (i.status=1 AND ifnull(b.TotalBought,0)>0)
OR (ifnull(r.TotalRented,0) >0 AND i.status in(1,2)
OR (ifnull(b.TotalBought,0)=0 AND ifnull(r.TotalRented,0) =0 AND i.status=3)
ORDER BY i.name ASC