汇总统计

[dinglj1760]

现在有
A表结构
ID,DWDM,LXCS
1，32，1
2，32，5
(ID,单位代码,来信次数)
B表结构
ID,DWDM,LFRS
1，32344，1
2，3290，3
3，326790，7
(ID,单位代码,来访人数)
关键的地方是在LFRS字段上
按照LFRS的个数分为"个人"=1,"集体"(2-4),群体(>4)
现在要求统计的结果表结构是
DWDM,来信次数,个人访次数,集体访次数,集体访人数,群体访人数,群体访次数
－－－－－－－－请大虾不吝赐教。俺刚学SQL，不懂的太多了。。。

[vivianfdlpw]

select
DWDM,
[来信次数]=sum(case when type=1 then 1 else 0 end),
[个人访次数]=sum(case when type=1 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=1 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=1 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=1 and LXCS>4) then 1 else 0 end),
[群体访人数]=sum(case when type=1 and LXCS>4) then LXCS else 0 end)
from
(
select *，[type]=1 from A
union all
select *,2 from B
) t
group by DWDM

[子陌红尘]

create table #A(ID int,DWDM int,LXCS int)
insert into #A select 1,32,1
insert into #A select 2,32,5

create table #B(ID int,DWDM int,LFRS int)
insert into #B select 1,32344 ,1
insert into #B select 2,3290 ,3
insert into #B select 3,326790,7



select
DWDM = (case when A.DWDM is null then B.DWDM else A.DWDM end),
来信次数 = isnull(A.LXCS,0),
个人访次数 = isnull(sum(case when b.LFRS = 1 then 1 else 0 end),0),
集体访次数 = isnull(sum(case when b.LFRS between 2 and 4 then 1 else 0 end),0),
集体访人数 = isnull(sum(case when b.LFRS between 2 and 4 then B.LFRS else 0 end),0),
群体访人数 = isnull(sum(case when b.LFRS > 4 then 1 else 0 end),0),
群体访次数 = isnull(sum(case when b.LFRS > 4 then B.LFRS else 0 end),0)
from
#A A
full outer join
#B B
on
A.DWDM = b.DWDM
group by
(case when A.DWDM is null then B.DWDM else A.DWDM end),isnull(A.LXCS,0)

[vivianfdlpw]

select
DWDM,
[来信次数]=sum(case when type=1 then 1 else 0 end),
[个人访次数]=sum(case when type=2 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=2 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=2 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=2 and LXCS>4) then 1 else 0 end),
[群体访人数]=sum(case when type=2 and LXCS>4) then LXCS else 0 end)
from
(
select *，[type]=1 from A
union all
select *,2 from B
) t
group by DWDM

[子陌红尘]

--生成测试数据
create table #A(ID int,DWDM int,LXCS int)
insert into #A select 1,32,1
insert into #A select 2,32,5
create table #B(ID int,DWDM int,LFRS int)
insert into #B select 1,32344 ,1
insert into #B select 2,3290 ,3
insert into #B select 3,326790,7


--执行查询
select
DWDM = (case when A.DWDM is null then B.DWDM else A.DWDM end),
来信次数 = isnull(A.LXCS,0),
个人访次数 = isnull(sum(case when b.LFRS = 1 then 1 else 0 end),0),
集体访次数 = isnull(sum(case when b.LFRS between 2 and 4 then 1 else 0 end),0),
集体访人数 = isnull(sum(case when b.LFRS between 2 and 4 then B.LFRS else 0 end),0),
群体访人数 = isnull(sum(case when b.LFRS > 4 then 1 else 0 end),0),
群体访次数 = isnull(sum(case when b.LFRS > 4 then B.LFRS else 0 end),0)
from
(select DWDM,LXCS=sum(LXCS) from #A group by DWDM) A
full outer join
#B B
on
A.DWDM = b.DWDM
group by
(case when A.DWDM is null then B.DWDM else A.DWDM end),isnull(A.LXCS,0)

--输出结果
DWDM 来信次数 个人访次数 集体访次数 集体访人数 群体访人数 群体访次数
------- -------- ---------- ---------- ---------- ---------- ----------
3290 0 0 1 3 0 0
32344 0 1 0 0 0 0
326790 0 0 0 0 1 7
32 6 0 0 0 0 0

[vivianfdlpw]

declare @a table (ID int,DWDM int,LXCS int)
insert @a
select 1,32,1 union
select 2,32,5
declare @b table(ID int,DWDM int,LFRS int)
insert @b
select 1,32344 ,1 union
select 2,3290,3 union
select 3,326790,7

select
DWDM,
[来信次数]=sum(case when type=1 then LXCS else 0 end),
[个人访次数]=sum(case when type=2 and LXCS=1 then 1 else 0 end),
[集体访次数]=sum(case when type=2 and (LXCS between 2 and 4) then 1 else 0 end),
[集体访人数]=sum(case when type=2 and (LXCS between 2 and 4) then LXCS else 0 end),
[群体访次数]=sum(case when type=2 and LXCS>4 then 1 else 0 end),
[群体访人数]=sum(case when type=2 and LXCS>4 then LXCS else 0 end)
from
(
select *,[type]=1 from @a
union all
select *,2 from @b
) t
group by DWDM

--结果
/*
DWDM 来信次数 个人访次数 集体访次数 集体访人数 群体访次数 群体访人数
----------- ----------- ----------- ----------- ----------- ----------- --
32 6 0 0 0 0 0
3290 0 0 1 3 0 0
32344 0 1 0 0 0 0
326790 0 0 0 0 1 7

（所影响的行数为 4 行）
*/

[dinglj1760]

vivianfdlpw和libin_ftsafe(子陌红尘)的方法各有千秋,给了我很大启发.
原来SQL还可以这样写的啊..呵呵
顺便问问有哪些入门的好书啊?