求sql，统计数量
有三张表：
table1
SQL code
table2
SQL code
table3
SQL code
求根据table2和table3中的name，type在table1中查询出
所有type在不同name下的个数（name,type是不固定的）。
结果：
SQL code
table1
SQL code
name type
A Q1
A Q2
A Q2
B Q3
B Q4
C Q1
C Q2
C Q1
D Q4
table2
SQL code
id name 1 A 2 B 3 C 4 D
table3
SQL code
id type 1 Q1 2 Q2 3 Q3 4 Q4
求根据table2和table3中的name，type在table1中查询出
所有type在不同name下的个数（name,type是不固定的）。
结果：
SQL code
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
作者: wangyangyang19 发布时间: 2011-06-11
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, sum(case when name = 'D' then 1 else 0 end) as D from table1 group by type
作者: AcHerat 发布时间: 2011-06-11
引用 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……
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……
作者: wangyangyang19 发布时间: 2011-06-11
貌似用不着下面两个表:
SQL code
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 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
作者: qianjin036a 发布时间: 2011-06-11
引用 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……
貌似用不着下面两个表:
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……
+1
作者: abcjun188 发布时间: 2011-06-11
+1
引用 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……
貌似用不着下面两个表:
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……
作者: chuanzhang5687 发布时间: 2011-06-11
pivot这是啥意思？
引用 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……
貌似用不着下面两个表:
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……
作者: chuanzhang5687 发布时间: 2011-06-11
列值不确定:
SQL code
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 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
作者: qianjin036a 发布时间: 2011-06-11
仍可以只用一个表:
SQL code
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 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
作者: qianjin036a 发布时间: 2011-06-11
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 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)
作者: xiaoliaoyun 发布时间: 2011-06-11