导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

外联统计问题 有点急 题已写好

wuxinyuzhu 2007-12-19 04:11:48

--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3


--现在想通过联接的方法得出这样的结果
name name2 count
aa zhian 1
aa xingshi 1
aa minshi 1
aa qita 0
aa 合计 3
bb zhian 0
bb xingshi 2
bb minshi 0
bb qita 0
bb 合计 2
cc zhian 0
cc xingshi 0
cc minshi 0
cc qita 1
cc 合计 1
dd zhian 0
dd xingshi 1
dd minshi 2
dd qita 0
dd 合计 3
总计 null 9

...全文
78 点赞 收藏 18
写回复
18 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
wuxinyuzhu 2007-12-19

select isnull(d.name,'总计'),isnull(d.name2,'合计'),sum(d.counts) as counts
from (select a.name,a.id,b.name2,b.lx,(select count(*) from tab2 c where c.id=a.id and c.lx=b.lx) as counts from tab a ,tab3 b) d
group by d.name,d.name2 with rollup
回复
wuxinyuzhu 2007-12-19

--谢谢大家结贴了
--这是一个朋友给写的
--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3




select d.name,d.name2,sum(d.counts) as counts
from (select a.name,a.id,b.name2,b.lx,(select count(*) from tab2 c where c.id=a.id and c.lx=b.lx) as counts from tab a ,tab3 b) d
group by d.name,d.name2 with rollup
回复
dawugui 2007-12-19
--按lx排序的.
--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3

select m.name , isnull(n.name2,'合计') name2 , m.[count] from
(
select t.name , cast(t.lx as varchar) lx , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name , cast(t.lx as varchar)
union all
select t.name , lx ='合计', [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name
union all
select name = '总计', lx = '总计' , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
) m left join tab3 n on m.lx = cast(n.lx as varchar)
order by name , m.lx

drop table tab,tab2,tab3

/*
name name2 count
-------------------- -------------------- -----------
aa zhian 1
aa xingshi 1
aa minshi 1
aa qita 0
aa 合计 3
bb zhian 0
bb xingshi 2
bb minshi 0
bb qita 0
bb 合计 2
cc zhian 0
cc xingshi 0
cc minshi 0
cc qita 1
cc 合计 1
dd zhian 0
dd xingshi 1
dd minshi 2
dd qita 0
dd 合计 3
总计 合计 9

(21 行受影响)
*/
回复
yangjiexi 2007-12-19

--强,这样更简单啊!
select name, name2, [count] from (
select name, name2, [count]=(select count(1) from tab2 c where c.id=a.id and c.lx=b.lx)
from tab a, tab3 b
union all
select name, N'合计', [count]=(select count(1) from tab2 c where c.id=a.id )
from tab a
union all
select N'总计', NULL, [count]=(select count(1) from tab2)
) as d
order by name, name2
回复
dawugui 2007-12-19
--按lx排序的.
name name2 count
-------------------- -------------------- -----------
aa zhian 1
aa xingshi 1
aa minshi 1
aa qita 0
aa 合计 3
bb zhian 0
bb xingshi 2
bb minshi 0
bb qita 0
bb 合计 2
cc zhian 0
cc xingshi 0
cc minshi 0
cc qita 1
cc 合计 1
dd zhian 0
dd xingshi 1
dd minshi 2
dd qita 0
dd 合计 3
总计 合计 9

(21 行受影响)
回复
yangjiexi 2007-12-19

select f.* from
(select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
union all

select f.name,name2=N'合计',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
group by f.name

union all
select name=N'总计',name2='NULL',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f

order by name,name2
回复
tim_spac 2007-12-19

--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3


select name, name2, cnt from (
select name, name2, cnt=(select count(1) from tab2 c where c.id=a.id and c.lx=b.lx)
from tab a, tab3 b
union
select name, '合计', cnt=(select count(1) from tab2 c where c.id=a.id )
from tab a
) as d
order by name, name2

drop table tab
drop table tab3
drop table tab2
回复
yangjiexi 2007-12-19

select h.* from
(select f.* from
(select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
union all
select f.name,name2=N'合计',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
group by f.name
) h
union all
select name=N'总计',name2='NULL',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f

order by h.name,h.name2
回复
yangjiexi 2007-12-19

--看起来有些乱,整理一下
select h.* from
(select f.* from
(select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
union all
select f.name,name2=N'合计',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
group by f.name
) h
union all
select name=N'总计',name2='NULL',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d
on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f

order by h.name,h.name2
回复
yangjiexi 2007-12-19

/*
aa minshi 1
aa qita 0
aa xingshi 1
aa zhian 1
aa 合计 3
bb minshi 0
bb qita 0
bb xingshi 2
bb zhian 0
bb 合计 2
cc minshi 0
cc qita 1
cc xingshi 0
cc zhian 0
cc 合计 1
dd minshi 2
dd qita 0
dd xingshi 1
dd zhian 0
dd 合计 3
总计 NULL 9
*/
回复
yangjiexi 2007-12-19

select h.*
from
(select f.*
from
(select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
union all
select f.name,name2=N'合计',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f
group by f.name
) h
union all
select name=N'总计',name2='NULL',sum(f.[count]) [count]
from
( select d.name,d.name2,sum(case when c.lx is not null then 1 else 0 end) [count]
from tab2 c right outer join
(select a.*,b.*
from tab a,tab3 b
) d on c.id=d.id and c.lx=d.lx
group by d.name,d.name2) f

order by h.name,h.name2
回复
liangCK 2007-12-19
学习老乌龟的.
回复
dawugui 2007-12-19
--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3

select t.name , t.name2 , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name , t.name2
union all
select t.name , name2 = '合计' , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name
union all
select name = '总计', name2 = '总计' , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
order by name , name2

drop table tab,tab2,tab3

/*
name name2 count
-------------------- -------------------- -----------
aa minshi 1
aa qita 0
aa xingshi 1
aa zhian 1
aa 合计 3
bb minshi 0
bb qita 0
bb xingshi 2
bb zhian 0
bb 合计 2
cc minshi 0
cc qita 1
cc xingshi 0
cc zhian 0
cc 合计 1
dd minshi 2
dd qita 0
dd xingshi 1
dd zhian 0
dd 合计 3
总计 总计 9

(21 行受影响)
*/
回复
dawugui 2007-12-19
--这是一个常数表1
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3

select t.name , t.name2 , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name , t.name2
union all
select t.name , name2 = '合计' , [count] = sum(case when t.id = c.id and t.lx = c.lx then 1 else 0 end)
from
(
select a.name , b.name2 , a.id , b.lx from tab a , tab3 b
) t left join tab2 c on t.id = c.id and t.lx = c.lx
group by t.name
order by name , name2

drop table tab,tab2,tab3

/*
name name2 count
-------------------- -------------------- -----------
aa minshi 1
aa qita 0
aa xingshi 1
aa zhian 1
aa 合计 3
bb minshi 0
bb qita 0
bb xingshi 2
bb zhian 0
bb 合计 2
cc minshi 0
cc qita 1
cc xingshi 0
cc zhian 0
cc 合计 1
dd minshi 2
dd qita 0
dd xingshi 1
dd zhian 0
dd 合计 3

(20 行受影响)
*/
回复
wuxinyuzhu 2007-12-19
不行 个数是0的没有统计出来
回复
liangCK 2007-12-19
好像错了.
回复
liangCK 2007-12-19
create table tab(id int, name varchar(20))
insert into tab
select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'

--这是常数表2
create table tab3(lx int,name2 varchar(20))
insert into tab3
select 0,'zhian'
union all select 1,'xingshi'
union all select 2,'minshi'
union all select 3,'qita'

--这是一个数据表 id等于常数表1的id,lx等于常数表2的lx
create table tab2(id int,name1 varchar(20),lx int)
insert into tab2
select 1,'x',0
union all select 1,'xxx',1
union all select 1,'xxx',2
union all select 2,'xxx',1
union all select 2,'xxx',1
union all select 4,'xxx',2
union all select 4,'xxx',2
union all select 4,'xxx',1
union all select 3,'xxx',3


select [name]=case when grouping(b.name2)=0 then a.name
when grouping(a.name)=0 then a.name
when grouping(a.name)=1 then '总计' end,
name2=case when grouping(b.name2)=1 then '合计'
when grouping(b.name2)=0 then b.name2 end,
[count]=count(*)
from tab a left join tab2 c on a.id=c.id left join tab3 b on b.lx=c.lx
group by a.name,b.name2
with rollup
order by a.name


drop table tab,tab2,tab3

/*
name name2 count
-------------------- -------------------- -----------
aa minshi 1
aa xingshi 1
aa zhian 1
aa 合计 3
bb xingshi 2
bb 合计 2
cc qita 1
cc 合计 1
dd minshi 2
dd xingshi 1
dd 合计 3
总计 合计 9

(所影响的行数为 12 行)

*/
回复
dobear_0922 2007-12-19
看不懂,,,
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告