sql三个表查询,该怎么处理
sql三个表查询
三张记录表a1、a2、a3,每个表存储一个活动的参加人员记录,每个表中均有activeName字段表示参加人员的名称,该字段均为唯一性列,查询至少参加了2个两个的人名。
------解决方案--------------------
------解决方案--------------------
三张记录表a1、a2、a3,每个表存储一个活动的参加人员记录,每个表中均有activeName字段表示参加人员的名称,该字段均为唯一性列,查询至少参加了2个两个的人名。
SQL
------解决方案--------------------
select
activeName
from (
select 'a1' as TabName,activeName from a1
union all
select 'a2' as TabName,activeName from a2
union all
select 'a3' as TabName,activeName from a3
) as a
group by activeName
having count(distinct TabName)>1
------解决方案--------------------
--如果 activeName在各表中是唯一的可以直接 COUNT(1)>=2
SELECT activeName,COUNT(1) FROM
(
SELECT activeName FROM A1
UNION ALL SELECT activeName FROM A2
UNION ALL SELECT activeName FROM A3
)TB
GROUP BY activeName HAVING COUNT(1)>=2