来自 数据库 2020-05-07 17:33 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

Partition分组使用和行列转换

代码如下:

SELECT * FROM
(
SELECT subject,name,score,ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS num FROM score

解法

--3.取每个学科的前3名数据

复制代码 代码如下:CREATE TABLE body

) T WHERE T.num <= 3 ORDER BY subject

SELECT * FROM
(
SELECT subject,name,score,Rank() OVER(PARTITION BY subject ORDER BY score DESC) AS num FROM score

body a

) T WHERE T.num <= 3 ORDER BY subject

/*以下是我对4个排名函数的类比表格:         
               排名连续性   排名并列性         
RANK()        不一定连续    有并列         
DENSE_RANK()   连续         有并列         
ROW_NUMBER()   连续         无并列         
NTILE()        连续         有并列         
*/

--4行转列

SELECT name,ISNULL([数学],0) [数学],ISNULL([英语],0) [英语],ISNULL([语文],0) [语文]
FROM score PIVOT (max(score) FOR subject IN ([数学],[英语],[语文]) ) AS pt

SELECT name,ISNULL([数学],0) [数学],ISNULL([英语],0) [英语]
FROM score PIVOT (max(score) FOR subject IN ([数学],[英语]) ) AS pt

SELECT Name AS 姓名 ,
     MAX(CASE Subject WHEN '数学' THEN score ELSE 0 END) [数学]网上澳门金莎娱乐, ,
     MAX(CASE Subject WHEN '英语' THEN score ELSE 0 END) [英语] ,
     MAX(CASE Subject WHEN '语文'  THEN score ELSE 0 END) [语文]
   FROM score GROUP BY name

--5动态行转列
declare @sql varchar(8000)
--set @sql = 'select Name as ' + '姓名'
set @sql=''
select @sql = @sql+ ' , max(case Subject when ''' + Subject + ''' then score else 0 end) [' + Subject + ']'
from (select distinct Subject from score) as a
--set @sql = @sql + ' from score group by name'
print @sql
exec(@sql)

--6 动态Pivot
declare @sql varchar(500)
declare @sql2 varchar(500)
select @sql2=  isnull(@sql2 + ',' , '') + 'isnull(['+Subject+'],0) as '+Subject+'' from score group by Subject
select @sql = isnull(@sql + ',' , '') + '['+Subject+']' from score group by Subject
print @sql
print @sql2
exec ('select Name,'+@sql2+' from (
select Name,s.Score,Subject from score s
 
) a pivot (sum(Score) for Subject in (' + @sql + ')) b')

   
SELECT name,sum(score) score,
(SELECT Subject+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS StuList,
  (SELECT cast(score as varchar(10))+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS StuList
FROM score A
GROUP BY name

 

--7 XML PATH
   select b.name,b.score,
   LEFT(b.StuList,LEN(b.StuList)-1) StuList,
   LEFT(b.ScoreList,LEN(b.ScoreList)-1) ScoreList
    from (
    SELECT name,sum(score) score,
(SELECT Subject+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS StuList,
  (SELECT cast(score as varchar(10))+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS ScoreList
FROM score A
GROUP BY name ) b  order by score desc   

  

INSERT INTO body VALUES(2,'bbbb')

select * from score

ID int,

) T WHERE T.num <= 3 ORDER BY subject

SELECT * FROM
(
SELECT subject,name,score,Dense_Rank() OVER(PARTITION BY subject ORDER BY score DESC) AS num FROM score

--这条语句的作用是按照a中的ID查找body表中ID=a.ID的所有记录,并把它转换成一个xml

select subject from score   for xml path('')
SELECT Subject+','  FROM score
    where name='林风'
  FOR XML PATH('')
 
   SELECT  cast(score as varchar)+',' FROM score
    where name='林风'
  FOR XML PATH('')
 
SELECT name ,sum(score),
(SELECT Subject+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS StuList,
 
 
  (SELECT cast(score as varchar(10))+',' FROM score
  WHERE name=A.name
  FOR XML PATH('')) AS StuList
 
 
FROM score A
GROUP BY name

)

CREATE TABLE score
(
name NVARCHAR(20),
subject NVARCHAR(20),
score INT
)
--2.插入测试数据
INSERT INTO score(name,subject,score) VALUES('张三','语文',98)
INSERT INTO score(name,subject,score) VALUES('张三','数学',80)
INSERT INTO score(name,subject,score) VALUES('张三','英语',90)
INSERT INTO score(name,subject,score) VALUES('李四','语文',88)
INSERT INTO score(name,subject,score) VALUES('李四','数学',86)
INSERT INTO score(name,subject,score) VALUES('李四','英语',88)
INSERT INTO score(name,subject,score) VALUES('李明','语文',60)
INSERT INTO score(name,subject,score) VALUES('李明','数学',86)
INSERT INTO score(name,subject,score) VALUES('李明','英语',88)
INSERT INTO score(name,subject,score) VALUES('林风','语文',74)
INSERT INTO score(name,subject,score) VALUES('林风','数学',99)
INSERT INTO score(name,subject,score) VALUES('林风','英语',59)
INSERT INTO score(name,subject,score) VALUES('严明','英语',96)

stuff((SELECT ','+BODY FROM body WHERE ID=a.ID FOR xml path('')),1,1,'')

FROM

--具体思路是这样的:

go

go

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:Partition分组使用和行列转换

关键词: