提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

sql分组查询问题

SQL 问题 查询 分组
2023-06-13 09:14:15 时间
情景一：
表中数据
namescore
aaa11
aaa19
bbb12
bbb18
ccc19
ddd21
期望查询结果如下
namescore
aaa30
bbb30
ccc19
ddd21
复制代码代码如下:

---检查表是否存在
ifexists(select*fromsysobjectswherename="testSum")
droptabletestSum
go
---创建表
createtabletestSum
(
tidintprimarykeyidentity(1,1),
tnamevarchar(30)null,
tscorintnull
)
go
insertintotestSum(tname,tscor)
select"aaa",11
unionall
select"aaa",19
unionall
select"bbb",12
unionall
select"bbb",18
unionall
select"ccc",19
unionall
select"ddd",21
---查询语句
selecttname,sum(tscor)fromtestSumgroupbytname
---只查询tscor总和为30的
selecttname,sum(tscor)fromtestSumgroupbytnamehavingsum(tscor)=30

情景二：
姓名科目分数
张三语文30
张三数学50
张三英语70
李四语文50
李四数学80
李四英语90

期望查询结果：

姓名语文数学英语
张三305070
李四508090
复制代码代码如下:

---检查表是否存在
ifexists(select*fromsysobjectswherename="testScore")
droptabletestScore
go
---创建表
createtabletestScore
(
tidintprimarykeyidentity(1,1),
tnamevarchar(30)null,
ttypevarchar(10)null,
tscorintnull
)
go
---插入数据
insertintotestScorevalues("张三","语文",90)
insertintotestScorevalues("张三","数学",20)
insertintotestScorevalues("张三","英语",50)
insertintotestScorevalues("李四","语文",30)
insertintotestScorevalues("李四","数学",47)
insertintotestScorevalues("李四","英语",78)
---查询
selecttnameas"姓名",
max(casettypewhen"语文"thentscorelse0end)"语文",
max(casettypewhen"数学"thentscorelse0end)"数学",
max(casettypewhen"英语"thentscorelse0end)"英语"
fromtestScore
groupbytname

情景三：
表:table1
字段：id,name
内容：
----------------
1,aaa
1,bbb
2,ccc
2,ddd
3,eee
3,fff
--------------
希望结果：
---------------------
1aaabbb[aaabbb之间半角空格区分，以下类似]
2cccddd
3eeefff
复制代码代码如下:
fexists(select*fromsysobjectswherename="test1")
droptabletest1
go
createtabletest1
(
tidintprimarykeyidentity(1,1),
tnumintnull,
tnamevarchar(30)null
)
go
insertintotest1values(1,"aa")
insertintotest1values(1,"bb")
insertintotest1values(2,"cc")
insertintotest1values(2,"dd")
insertintotest1values(3,"ee")
insertintotest1values(3,"ff")
SELECT*FROM(SELECTDISTINCTtnumFROMtest1
)A
OUTERAPPLY(
SELECTtname=STUFF(REPLACE(REPLACE(
(
SELECTtnameFROMtest1N
WHEREtnum=A.tnum
FORXMLAUTO
),"<Ntname="",""),""/>",""),1,1,"")
)N

情景四：
我需要将表tb中的数据select出来，得到下面第二个表的数据，如何写select语句？
表tb
idaflagclass
----------+---------+--------+---------
121A
221A
341A
452A
532A
641A
721A
832A
942A
1053A
1151B
1221B
1331B
1441B
1523B
1673B
1732B
1841B
1951B
2022B
2111B
2211C
2323C
2463C
2532C
...
需要选取出如下的表，按class列进行分组，a1,a2,a3字段分别为flag=1、2、3时tb表中a字段的求和
选取后
a1a2a3class
-----------+------------+-----------------+--------------
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
复制代码代码如下:
---检查表是否存在
ifexists(select*fromsysobjectswherename="testFlag")
droptabletestFlag
go
---创建表
createtabletestFlag
(
tidintprimarykeyidentity(1,1),
tnamevarchar(30)null,
tflagintnull,
tscorintnull
)
go
---插入数据
insertintotestFlag(tname,tflag,tscor)
select"aaa",1,11
unionall
select"aaa",2,19
unionall
select"aaa",3,12
unionall
select"aaa",1,18
unionall
select"aaa",2,19
unionall
select"aaa",3,21
unionall
select"bbb",1,11
unionall
select"bbb",2,19
unionall
select"bbb",3,12
unionall
select"bbb",1,18
unionall
select"bbb",2,19
unionall
select"bbb",3,21
----查询语句
selectdistincttname,(selectsum(tscor)fromtestFlagwheretflag=1andtestFlag.tname=t.tname)as"flag1",(selectsum(tscor)fromtestFlagwheretflag=2andtestFlag.tname=t.tname)as"flag2",(selectsum(tscor)fromtestFlagwheretflag=3andtestFlag.tname=t.tname)as"flag3"fromtestFlagtgroupbytname,tflag

相关文章