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

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

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

``````
...全文
78 点赞 收藏 18

18 条回复

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

(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

(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

*/
``````

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

(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

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

（所影响的行数为 12 行）

*/``````

dobear_0922 2007-12-19

MS-SQL Server

3.2w+

MS-SQL Server相关内容讨论专区