sql 分組查訊問題。本站提示廣大學習愛好者：（sql 分組查訊問題）文章只能為提供參考，不一定能成為您想要的結果。以下是sql 分組查訊問題正文
情形一：
表中數據
name score
aaa 11
aaa 19
bbb 12
bbb 18
ccc 19
ddd 21
希冀查詢成果以下
name score
aaa 30
bbb 30
ccc 19
ddd 21
---檢討表能否存在
if exists(select * from sysobjects where name='testSum')
drop table testSum
go
---創立表
create table testSum
(
tid int primary key identity(1,1),
tname varchar(30) null,
tscor int null
)
go
insert into testSum (tname,tscor)
select 'aaa',11
union all
select 'aaa',19
union all
select 'bbb',12
union all
select 'bbb',18
union all
select 'ccc',19
union all
select 'ddd',21
---查詢語句
select tname ,sum(tscor) from testSum group by tname
---只查詢tscor總和為30的
select tname ,sum(tscor) from testSum group by tname having sum(tscor)=30
情形二：
姓名 科目 分數
張三 語文 30
張三 數學 50
張三 英語 70
李四 語文 50
李四 數學 80
李四 英語 90
希冀查詢成果：
姓名 語文 數學 英語
張三 30 50 70
李四 50 80 90
---檢討表能否存在
if exists(select * from sysobjects where name='testScore')
drop table testScore
go
---創立表
create table testScore
(
tid int primary key identity(1,1),
tname varchar(30) null,
ttype varchar(10) null,
tscor int null
)
go
---拔出數據
insert into testScore values ('張三','語文',90)
insert into testScore values ('張三','數學',20)
insert into testScore values ('張三','英語',50)
insert into testScore values ('李四','語文',30)
insert into testScore values ('李四','數學',47)
insert into testScore values ('李四','英語',78)
---查詢
select tname as '姓名' ,
max(case ttype when '語文' then tscor else 0 end) '語文',
max(case ttype when '數學' then tscor else 0 end) '數學',
max(case ttype when '英語' then tscor else 0 end) '英語'
from testScore
group by tname
情形三：
表:table1
字段：id , name
內容：
----------------
1,aaa
1,bbb
2,ccc
2,ddd
3,eee
3,fff
--------------
願望成果：
---------------------
1 aaa bbb [aaa bbb之間半角空格辨別，以下相似]
2 ccc ddd
3 eee fff
f exists(select * from sysobjects where name='test1')
drop table test1
go
create table test1
(
tid int primary key identity(1,1),
tnum int null,
tname varchar(30) null
)
go
insert into test1 values (1,'aa')
insert into test1 values (1,'bb')
insert into test1 values (2,'cc')
insert into test1 values (2,'dd')
insert into test1 values (3,'ee')
insert into test1 values (3,'ff')
SELECT * FROM ( SELECT DISTINCT tnum FROM test1
)A
OUTER APPLY(
SELECT tname= STUFF(REPLACE(REPLACE(
(
SELECT tname FROM test1 N
WHERE tnum = A.tnum
FOR XML AUTO
), '<N tname="', ' '), '"/>', ''), 1, 1, '')
)N
情形四：
我須要將表tb中的數據select出來，獲得上面第二個表的數據，若何寫select語句？
表tb
id a flag class
----------+---------+--------+---------
1 2 1 A
2 2 1 A
3 4 1 A
4 5 2 A
5 3 2 A
6 4 1 A
7 2 1 A
8 3 2 A
9 4 2 A
10 5 3 A
11 5 1 B
12 2 1 B
13 3 1 B
14 4 1 B
15 2 3 B
16 7 3 B
17 3 2 B
18 4 1 B
19 5 1 B
20 2 2 B
21 1 1 B
22 1 1 C
23 2 3 C
24 6 3 C
25 3 2 C
...
須要拔取出以下的表，按class列停止分組，a1,a2,a3字段分離為flag=1、2、3時tb表中a字段的乞降
拔取後
a1 a2 a3 class
-----------+------------+-----------------+--------------
sum(a) sum(a) sum(a) A
sum(a) sum(a) sum(a) B
sum(a) sum(a) sum(a) C
sum(a) sum(a) sum(a) D
sum(a) sum(a) sum(a) E
sum(a) sum(a) sum(a) F
sum(a) sum(a) sum(a) G
---檢討表能否存在
if exists(select * from sysobjects where name='testFlag')
drop table testFlag
go
---創立表
create table testFlag
(
tid int primary key identity(1,1),
tname varchar(30) null,
tflag int null,
tscor int null
)
go
---拔出數據
insert into testFlag (tname,tflag,tscor)
select 'aaa',1,11
union all
select 'aaa',2,19
union all
select 'aaa',3,12
union all
select 'aaa',1,18
union all
select 'aaa',2,19
union all
select 'aaa',3,21
union all
select 'bbb',1,11
union all
select 'bbb',2,19
union all
select 'bbb',3,12
union all
select 'bbb',1,18
union all
select 'bbb',2,19
union all
select 'bbb',3,21
----查詢語句
select distinct tname,(select sum(tscor) from testFlag where tflag=1 and testFlag.tname = t.tname) as 'flag1',(select sum(tscor) from testFlag where tflag=2 and testFlag.tname = t.tname) as 'flag2',(select sum(tscor) from testFlag where tflag=3 and testFlag.tname = t.tname) as 'flag3' from testFlag t group by tname,tflag
