SQL 查出数据合并

SQL 查出数据合并

1、使用UNION
select count() as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’
or video_monitor = ‘在线’)
UNION
select count() as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’
or video_monitor = ‘在线’)
AND mine_class = ‘1’
UNION
select count(*) as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’
or video_monitor = ‘在线’)
AND mine_class = ‘0’

2、使用sql拼接
select ifnull(sum(total.num),0) as totalNum,
ifnull(sum(lt.num),0) as ltNum,
ifnull(sum(jg.num),0) as jgNum
from
(select coalmine_code, SUPER_DEPT_ID,count(coalmine_code) as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’ or video_monitor = ‘在线’) group by SUPER_DEPT_ID) total
left join
(select coalmine_code, SUPER_DEPT_ID,count(coalmine_code) as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’ or video_monitor = ‘在线’) AND mine_class = ‘1’ group by SUPER_DEPT_ID) lt
on total.SUPER_DEPT_ID = lt.SUPER_DEPT_ID
left join
(select coalmine_code, SUPER_DEPT_ID,count(coalmine_code) as num from table where (security_monitor = ‘在线’ or person_position = ‘在线’ or video_monitor = ‘在线’) AND mine_class = ‘0’ group by SUPER_DEPT_ID) jg
on total.SUPER_DEPT_ID = jg.SUPER_DEPT_ID

3、备注 其中得table可以是多张表。