用sql查询多个表中某字段出现的次数大于某个数值的语句有哪些？

Question

如题所述

Answer 1

例如现有表1和表2，两个表里各有一个字段Q里面存放各种可能相同的数据，现在要查询出两个表中字段Q里数据出现次数总计大于10的数据。

SELECT Q,COUNT(*) 出现总次数
FROM (SELECT Q FROM 表1 UNION ALL SELECT Q FROM 表2)
GROUP BY Q

HAVING COUNT(*)>10

以此内推

表 a ....,total,..

.表 b  ...,total,... 

查询 a,b 中 total 含有  各种可能相同的数据

select * from a

where total in (select total from a group by total having count(total) > 10)

union
select * from b

where total in (select total from b group by total having count(total) > 10)