我想把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
预期输出:(按id排序),其中69是最后一条记录。
[item_id] [num]
69 4
70 2
发布于 2018-06-09 04:09:51
给定样本数据和预期结果,不需要使用子查询:
select item_id, count(*)
from notifications
group by item_id
where uid = 3 and seen = 0
order by max(id) desc;
发布于 2018-06-09 04:00:00
有根据的猜测是您想要一个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;
尚不清楚您是否也希望在子查询中包含过滤条件。
https://stackoverflow.com/questions/50767573
复制相似问题