我有桌子,下面的结构。
待定
id name
1 AAA
2 BBB
3 BBB
4 BBB
5 AAA
6 CCC
select count(name) c from tbl
group by name having c >1
返回以下结果的查询:
AAA(2) duplicate
BBB(3) duplicate
CCC(1) not duplicate
重复的名字是AAA和BBB。最终结果,我想要的是此重复记录的计数。
结果应如下所示:重复产品总数(2)
发布于 2012-09-21 18:24:16
方法是使用一个每个重复一行的嵌套查询和一个仅返回内部查询结果计数的外部查询。
SELECT count(*) AS duplicate_count
FROM (
SELECT name FROM tbl
GROUP BY name HAVING COUNT(name) > 1
) AS t
发布于 2012-09-21 18:29:55
使用IF语句获取所需的输出:
SELECT name, COUNT(*) AS times, IF (COUNT(*)>1,"duplicated", "not duplicated") AS duplicated FROM <MY_TABLE> GROUP BY name
输出:
AAA 2 duplicated
BBB 3 duplicated
CCC 1 not duplicated
发布于 2016-02-22 18:25:21
列表的:
SELECT COUNT(`name`) AS adet, name
FROM `tbl` WHERE `status`=1 GROUP BY `name`
ORDER BY `adet` DESC
总计数的:
SELECT COUNT(*) AS Total
FROM (SELECT COUNT(name) AS cou FROM tbl GROUP BY name HAVING cou>1 ) AS virtual_tbl
//总数:5
https://stackoverflow.com/questions/12528644
复制相似问题