34,590
社区成员
发帖
与我相关
我的任务
分享求sql，统计数量
有三张表：
table1
table2
table3
求根据table2和table3中的name，type在table1中查询出
所有type在不同name下的个数（name,type是不固定的）。
结果：
table1
name type
A Q1
A Q2
A Q2
B Q3
B Q4
C Q1
C Q2
C Q1
D Q4
table2
id name
1 A
2 B
3 C
4 D
table3
id type
1 Q1
2 Q2
3 Q3
4 Q4
求根据table2和table3中的name，type在table1中查询出
所有type在不同name下的个数（name,type是不固定的）。
结果：
type A B C D
Q1 1 0 2 0
Q2 2 0 1 0
Q3 0 1 0 0
Q4 0 1 0 1
...全文
请发表友善的回复…
发表回复
xiaoliaoyun 2011-06-11
- 打赏
- 举报
CREATE TABLe table1(name VARCHAR(10),type VARCHAR(10))
INSERT INTO table1 SELECT 'A','Q1'
INSERT INTO table1 SELECT 'A','Q2'
INSERT INTO table1 SELECT 'A','Q2'
INSERT INTO table1 SELECT 'B','Q3'
INSERT INTO table1 SELECT 'B','Q4'
INSERT INTO table1 SELECT 'C','Q1'
INSERT INTO table1 SELECT 'C','Q2'
INSERT INTO table1 SELECT 'C','Q1'
INSERT INTO table1 SELECT 'D','Q4'
CREATE TABLE table2(id INT, name VARCHAR(10))
INSERT INTO table2 SELECT 1, 'A'
INSERT INTO table2 SELECT 2, 'B'
INSERT INTO table2 SELECT 3, 'C'
INSERT INTO table2 SELECT 4, 'D'
CREATE TABLE table3(id INT, type VARCHAR(10))
INSERT INTO table3 SELECT 1, 'Q1'
INSERT INTO table3 SELECT 2, 'Q2'
INSERT INTO table3 SELECT 3, 'Q3'
INSERT INTO table3 SELECT 4, 'Q4'
INSERT INTO table3 SELECT 5, 'Q5'
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT type'
SELECT @sql = @sql + ',SUM(CASE WHEN name = ''' + name + ''' THEN 1 ELSE 0 END) AS ' + NAME
FROM (SELECT name FROM table2)AS a
SELECT @sql = @sql + ' FROM (SELECT table3.type, table1.name FROM table3
LEFT JOIN table1 ON table3.type = table1.type) AS temp
GROUP BY type'
--PRINT (@sql)
EXEC (@sql)
-晴天 2011-06-11
- 打赏
- 举报
仍可以只用一个表:
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into table1 select 'B','Q3'
insert into table1 select 'B','Q4'
insert into table1 select 'C','Q1'
insert into table1 select 'C','Q2'
insert into table1 select 'C','Q1'
insert into table1 select 'D','Q4'
/*
create table table2(id int,name varchar(10))
insert into table2 select 1,'A'
insert into table2 select 2,'B'
insert into table2 select 3,'C'
insert into table2 select 4,'D'
create table table3(id int,type varchar(10))
insert into table3 select 1,'Q1'
insert into table3 select 2,'Q2'
insert into table3 select 3,'Q3'
insert into table3 select 4,'Q4'*/
go
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ Name +']' from(
select distinct [name] from table1
)t
exec('select [type],'+@s+'from table1 pivot (count([name]) for [name] in('+@s+'))b')
/*
type A B C D
---------- ----------- ----------- ----------- -----------
Q1 1 0 2 0
Q2 2 0 1 0
Q3 0 1 0 0
Q4 0 1 0 1
(4 行受影响)
*/
go
drop table table1--,table2,table3
-晴天 2011-06-11
- 打赏
- 举报
列值不确定:
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into table1 select 'B','Q3'
insert into table1 select 'B','Q4'
insert into table1 select 'C','Q1'
insert into table1 select 'C','Q2'
insert into table1 select 'C','Q1'
insert into table1 select 'D','Q4'
create table table2(id int,name varchar(10))
insert into table2 select 1,'A'
insert into table2 select 2,'B'
insert into table2 select 3,'C'
insert into table2 select 4,'D'
/*
create table table3(id int,type varchar(10))
insert into table3 select 1,'Q1'
insert into table3 select 2,'Q2'
insert into table3 select 3,'Q3'
insert into table3 select 4,'Q4'*/
go
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ Name +']' from table2
exec('select [type],'+@s+'from table1 pivot (count([name]) for [name] in('+@s+'))b')
/*
type A B C D
---------- ----------- ----------- ----------- -----------
Q1 1 0 2 0
Q2 2 0 1 0
Q3 0 1 0 0
Q4 0 1 0 1
(4 行受影响)
*/
go
drop table table1,table2--,table3
chuanzhang5687 2011-06-11
- 打赏
- 举报
pivot这是啥意思？[Quote=引用 3 楼 qianjin036a 的回复:]
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
chuanzhang5687 2011-06-11
- 打赏
- 举报
+1[Quote=引用 3 楼 qianjin036a 的回复:]
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
打一壶酱油 2011-06-11
- 打赏
- 举报
[Quote=引用 3 楼 qianjin036a 的回复:]
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
+1
貌似用不着下面两个表:
SQL code
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into……
[/Quote]
+1
-晴天 2011-06-11
- 打赏
- 举报
貌似用不着下面两个表:
create table table1(name varchar(10),type varchar(10))
insert into table1 select 'A','Q1'
insert into table1 select 'A','Q2'
insert into table1 select 'A','Q2'
insert into table1 select 'B','Q3'
insert into table1 select 'B','Q4'
insert into table1 select 'C','Q1'
insert into table1 select 'C','Q2'
insert into table1 select 'C','Q1'
insert into table1 select 'D','Q4'
/*
create table table2(id int,name varchar(10))
insert into table2 select 1,'A'
insert into table2 select 2,'B'
insert into table2 select 3,'C'
insert into table2 select 4,'D'
create table table3(id int,type varchar(10))
insert into table3 select 1,'Q1'
insert into table3 select 2,'Q2'
insert into table3 select 3,'Q3'
insert into table3 select 4,'Q4'*/
go
select [type],[A],[B],[C],[D]
from table1
pivot (count([name]) for [name] in([A],[B],[C],[D]))b
/*
type A B C D
---------- ----------- ----------- ----------- -----------
Q1 1 0 2 0
Q2 2 0 1 0
Q3 0 1 0 0
Q4 0 1 0 1
(4 行受影响)
*/
go
drop table table1--,table2,table3
wangyangyang19 2011-06-11
- 打赏
- 举报
[Quote=引用 1 楼 acherat 的回复:]
SQL code
select type,
sum(case when name = 'A' then 1 else 0 end) as A,
sum(case when name = 'B' then 1 else 0 end) as B,
sum(case when name = 'C' then 1 else 0 end) as C,
s……
[/Quote]name 是不确定的，可能是 A B C D E F ....
SQL code
select type,
sum(case when name = 'A' then 1 else 0 end) as A,
sum(case when name = 'B' then 1 else 0 end) as B,
sum(case when name = 'C' then 1 else 0 end) as C,
s……
[/Quote]name 是不确定的，可能是 A B C D E F ....
AcHerat 元老 2011-06-11
- 打赏
- 举报
select type,
sum(case when name = 'A' then 1 else 0 end) as A,
sum(case when name = 'B' then 1 else 0 end) as B,
sum(case when name = 'C' then 1 else 0 end) as C,
sum(case when name = 'D' then 1 else 0 end) as D
from table1
group by type
