各位大虾,小弟在这求一条SQL,本人需要在多个表里查询出一个结果,举个例子
2007 2006 2005 2004
EI 10 5 12 3
SI 13 45 0 5
EL 4 8 21 65其中年下面的数字是EI或者SI他们在该年出现的次数(count(*))
我需要把2007年到2004年之间EI,SI,EL出现的次数统计出来合成一个表
我现在只能做到到用UINO all 查出一年的而已,和其他年的合并不了
select count(*) as countnum from lwc_worker_contractor_appl a, lwc_worker_contractor_licence b
where a.status ='LI' and
a.lic_type = 'EI' and
a.lic_num = b.lic_num and
datepart(yyyy, b.lic_valid_start_dte) ='2007'
union ALLselect count(*) as countnum from EISI_APPL——PROC a, EISI_licence_NUM b
where a.appl_status_cde ='SI' and
SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and
a.lew_lic_num = b.lic_num and
datepart(yyyy, b.valid_start_dte) = '2007'
union ALLselect count(*) as countnum from EISI_APPL a, EISI_licence b
where a.appl_status_cde ='EL' and
SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and
a.lew_lic_num = b.lic_num and
datepart(yyyy, b.valid_start_dte) = '2007'
跪求解答,感激不尽
2007 2006 2005 2004
EI 10 5 12 3
SI 13 45 0 5
EL 4 8 21 65其中年下面的数字是EI或者SI他们在该年出现的次数(count(*))
我需要把2007年到2004年之间EI,SI,EL出现的次数统计出来合成一个表
我现在只能做到到用UINO all 查出一年的而已,和其他年的合并不了
select count(*) as countnum from lwc_worker_contractor_appl a, lwc_worker_contractor_licence b
where a.status ='LI' and
a.lic_type = 'EI' and
a.lic_num = b.lic_num and
datepart(yyyy, b.lic_valid_start_dte) ='2007'
union ALLselect count(*) as countnum from EISI_APPL——PROC a, EISI_licence_NUM b
where a.appl_status_cde ='SI' and
SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and
a.lew_lic_num = b.lic_num and
datepart(yyyy, b.valid_start_dte) = '2007'
union ALLselect count(*) as countnum from EISI_APPL a, EISI_licence b
where a.appl_status_cde ='EL' and
SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and
a.lew_lic_num = b.lic_num and
datepart(yyyy, b.valid_start_dte) = '2007'
跪求解答,感激不尽
[year] = datepart(yyyy, b.lic_valid_start_dte),
count(*) as countnum
from lwc_worker_contractor_appl a
join lwc_worker_contractor_licence b on a.lic_num = b.lic_num
where a.status ='LI' and a.lic_type = 'EI'
and datepart(yyyy, b.lic_valid_start_dte) in (2004,2005,2006,2007)
group by datepart(yyyy, b.lic_valid_start_dte)union ALLselect TYPE='SI',
[year] = datepart(yyyy, b.valid_start_dte),
count(*) as countnum
from EISI_APPL_PROC a
join EISI_licence_NUM b on a.lew_lic_num = b.lic_num
where a.appl_status_cde ='SI' and SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and datepart(yyyy, b.valid_start_dte) in (2004,2005,2006,2007)
group by datepart(yyyy, b.valid_start_dte)union ALLselect TYPE='SL',
[year] = datepart(yyyy, b.valid_start_dte),
count(*) as countnum
from EISI_APPL a
join EISI_licence b on a.lew_lic_num = b.lic_num
where a.appl_status_cde ='EL' and SUBSTRING(A.APPL_NUM,1,1) = 'E' --for SI, repalce with 'SI'
and datepart(yyyy, b.valid_start_dte) in (2004,2005,2006,2007)
group by datepart(yyyy, b.valid_start_dte)然后再在应用里转成相应的表格格式。
sum(case year(日期) when 2007 then 1 else 0 end) [2007],
sum(case year(日期) when 2006 then 1 else 0 end) [2006],
sum(case year(日期) when 2005 then 1 else 0 end) [2005],
sum(case year(日期) when 2004 then 1 else 0 end) [2004]
from
(
select * from A
union all
select * from B
union all
select * from c
) t
group by type