汇总统计

dinglj1760 2005-07-27 12:57:04
现在有
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,不懂的太多了。。。
...全文
228 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
dinglj1760 2005-07-27
  • 打赏
  • 举报
回复
vivianfdlpw和libin_ftsafe(子陌红尘)的方法各有千秋,给了我很大启发.
原来SQL还可以这样写的啊..呵呵
顺便问问有哪些入门的好书啊?
vivianfdlpw 2005-07-27
  • 打赏
  • 举报
回复
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 行)
*/
子陌红尘 2005-07-27
  • 打赏
  • 举报
回复
--生成测试数据
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 2005-07-27
  • 打赏
  • 举报
回复
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
子陌红尘 2005-07-27
  • 打赏
  • 举报
回复
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 2005-07-27
  • 打赏
  • 举报
回复
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧