SQL问题

[笨笨的菜鸟张]

1.得到每个学生的总成绩降序排列
2.将学生每科的得分情况横排显示按照总成绩升序，如：学生的姓名，语文分数，数学分数，总成绩

[笨笨的菜鸟张]

还有每个人的平均值怎么算呢

[笨笨的菜鸟张]

谢谢，我知道怎么弄了，感谢感谢

[卖水果的net]

行转换，先百度一下，有问题再来追问。

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t (
	id INT,
	[name] NVARCHAR(10),
	[type] NVARCHAR(10),
	[score] INT	
)
INSERT INTO t VALUES (1,'张三','语文',70)
INSERT INTO t VALUES (2,'张三','数学',90)
INSERT INTO t VALUES (3,'李四','语文',80)
INSERT INTO t VALUES (4,'李四','数学',90)
INSERT INTO t VALUES (5,'王二','语文',70)
INSERT INTO t VALUES (6,'王二','数学',80)
GO
SELECT [name],SUM(score) AS [总分] FROM t GROUP BY [name]
/*
name	总分
李四	170
王二	150
张三	160
*/
SELECT [name]
,(SELECT [score] FROM t AS b WHERE a.name=b.name AND b.[type]='语文') AS [语文]
,(SELECT [score] FROM t AS b WHERE a.name=b.name AND b.[type]='数学') AS [数学]
,SUM(score) AS [总分] 
FROM t AS a 
GROUP BY [name]
ORDER BY [总分] DESC
/*
name	语文	数学	总分
李四	80	    90    	170
张三	70	    90	    160
王二	70	    80	    150
*/

[吉普赛的歌]

排序弄反了， 改一下：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t (
	id INT,
	[name] NVARCHAR(10),
	[type] NVARCHAR(10),
	[score] INT	
)
INSERT INTO t VALUES (1,'张三','语文',70)
INSERT INTO t VALUES (2,'张三','数学',90)
INSERT INTO t VALUES (3,'李四','语文',80)
INSERT INTO t VALUES (4,'李四','数学',90)
INSERT INTO t VALUES (5,'王二','语文',70)
INSERT INTO t VALUES (6,'王二','数学',80)
GO
SELECT [name],SUM(score) AS [总分] 
FROM t 
GROUP BY [name]
ORDER BY [总分] DESC
/*
name	总分
李四	170
王二	150
张三	160
*/
SELECT [name]
,(SELECT [score] FROM t AS b WHERE a.name=b.name AND b.[type]='语文') AS [语文]
,(SELECT [score] FROM t AS b WHERE a.name=b.name AND b.[type]='数学') AS [数学]
,SUM(score) AS [总分] 
FROM t AS a 
GROUP BY [name]
ORDER BY [总分] ASC
/*
name	语文	数学	总分
王二	70	    80	    150
张三	70	    90	    160
李四	80	    90    	170
*/

[二月十六]

https://blog.csdn.net/sinat_28984567/article/details/79865046