mysql—sql查询中的列计数

r1wp621o 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(292)

我想把 COUNT(item_id) 在此声明中：

SELECT * FROM `notifications` WHERE `uid` = '3' AND `seen` = '0' AND id IN (
SELECT MAX(id), COUNT(item_id)
FROM `notifications`
GROUP BY item_id
)  ORDER BY id DESC

但发生了此错误：操作数应包含1列。
表格：

[id] [uid] [item_id] [seen]
 1     3       69       0
 2     3       69       0
 3     3       70       0
 4     3       69       0
 5     3       70       0
 6     3       69       0

预期输出：（order by id desc），其中69是最后一条记录。

[item_id] [num]
    69      4
    70      2

sql mysql

来源：https://stackoverflow.com/questions/50767573/column-count-in-a-sql-query

2条答案

按热度按时间

4ioopgfo1#

给定示例数据和预期结果，不需要子查询：

select item_id, count(*)
from notifications
group by item_id
where uid = 3 and seen = 0
order by max(id) desc;

示例演示

赞(0）回复(0）举报 2021-06-21

wbgh16ku2#

一个有根据的猜测说你想要一个 JOIN :

SELECT n.*, nmax.cnt
FROM notifications n JOIN
     (SELECT item_id,  MAX(id) as max_id, COUNT(item_id) as cnt
      FROM notifications
      GROUP BY item_id
     ) nmax
     ON n.item_id = nmax.item_id AND nmax.id = nmax.max_id
WHERE n.uid = 3 AND n.seen = 0  -- removed the single quotes because these are probably numbers
ORDER BY n.id DESC;

目前还不清楚您是否希望子查询中也包含过滤条件。

赞(0）回复(0）举报 2021-06-21

我来回答

mysql—sql查询中的列计数

2条答案

相关问题

热门标签

最新问答