34,593
社区成员
发帖
与我相关
我的任务
分享外联统计问题 有点急 题已写好
--这是一个常数表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
...全文
请发表友善的回复…
发表回复
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
- 打赏
- 举报
看不懂，，，
