create table [express]([id] int,[a] int,[b] varchar(2),[c] varchar(2),[d] varchar(1))
insert [express]
select 1,1,'fg','as','6' union all
select 2,2,'op','sa','1' union all
select 3,1,'fg','ss','9' union all
select 4,3,'mk','op','2' union all
select 5,3,'mk','as','6' union all
select 6,6,'as',null,null union all
select 7,null,null,'op','2' union all
select 8,1,'fg','fg','a'
--->查询
select f,sum(dcount) as dcount,sum(scount) as scount
from
(
select b as f,0 as dcount,count(1) as scount from express where b is not null group by b
union all
select c,count(1),0 from express where c is not null group by c
) t
group by f/**
f dcount scount
---- ----------- -----------
as 2 1
fg 1 3
mk 0 2
op 2 1
sa 1 0
ss 1 0(所影响的行数为 6 行)
**/
insert [express]
select 1,1,'fg','as','6' union all
select 2,2,'op','sa','1' union all
select 3,1,'fg','ss','9' union all
select 4,3,'mk','op','2' union all
select 5,3,'mk','as','6' union all
select 6,6,'as',null,null union all
select 7,null,null,'op','2' union all
select 8,1,'fg','fg','a'
--->查询
select f,sum(dcount) as dcount,sum(scount) as scount
from
(
select b as f,0 as dcount,count(1) as scount from express where b is not null group by b
union all
select c,count(1),0 from express where c is not null group by c
) t
group by f/**
f dcount scount
---- ----------- -----------
as 2 1
fg 1 3
mk 0 2
op 2 1
sa 1 0
ss 1 0(所影响的行数为 6 行)
**/
借数据一用!
create table [express]([id] int,[a] int,[b] varchar(2),[c] varchar(2),[d] varchar(1))
insert [express]
select 1,1,'fg','as','6' union all
select 2,2,'op','sa','1' union all
select 3,1,'fg','ss','9' union all
select 4,3,'mk','op','2' union all
select 5,3,'mk','as','6' union all
select 6,6,'as',null,null union all
select 7,null,null,'op','2' union all
select 8,1,'fg','fg','a'SELECT ISNULL(B,C)AS F,ISNULL(dcount,0)dcount,ISNULL(scount,0)scount FROM
(select B,count(*) as scount from express e where e.a!='' group by a ,B)AS T
FULL JOIN
(select C,count(*) as dcount from express e where e.d!='' group by d ,C)AS T1
ON T.B=T1.C
F dcount scount
---- ----------- -----------
as 2 1
fg 1 3
mk 0 2
op 2 1
sa 1 0
ss 1 0(所影响的行数为 6 行)
declare @express table([id] int,[a] int,[b] varchar(2),[c] varchar(2),[d] varchar(1))
insert @express
select 1,1,'fg','as','6' union all
select 2,2,'op','sa','1' union all
select 3,1,'fg','ss','9' union all
select 4,3,'mk','op','2' union all
select 5,3,'mk','as','6' union all
select 6,6,'as',null,null union all
select 7,null,null,'op','2' union all
select 8,1,'fg','fg','a'
--結果
select f
,SUM(dcount) dcount
,SUM(scount) scount
from (
select b as f,COUNT(1) as scount,0 as dcount from @express group by b having b is not null
union all
select c as f,0 as scount,COUNT(1) as dcount from @express group by c having c is not null
) t1
group by f
insert [express]
select 1,1,'fg','as','6' union all
select 2,2,'op','sa','1' union all
select 3,1,'fg','ss','9' union all
select 4,3,'mk','op','2' union all
select 5,3,'mk','as','6' union all
select 6,6,'as',null,null union all
select 7,null,null,'op','2' union all
select 8,1,'fg','fg','a'select f,MAX(dcount) dcount ,MAX(scount) scount
from (select b as f ,dcount = 0 ,count(*) as scount from express e where a is not null group by b
union all
select c as f, count(*) as dcount, scount=0 from express e where d is not null group by c) t
group by f
order by dcount desc
/*_____--------------------
as 2 1
op 2 1
sa 1 0
ss 1 0
fg 1 3
mk 0 2
----------------------------*/