27,579
社区成员
发帖
与我相关
我的任务
分享
SELECT [name]+N'同学本学期共得5分'+RTRIM(SUM(CASE WHEN [score]=N'5分' THEN 1 ELSE 0 END))+
N'个,其他'+RTRIM(SUM(CASE WHEN [score]!=N'5分' THEN 1 ELSE 0 END))+
N'个,共有'+RTRIM(COUNT(*))+
N'次成绩,得5分的次数占总次数的百分比是'+
RTRIM(CAST(100.0*SUM(CASE WHEN [score]=N'5分' THEN 1 ELSE 0 END)/COUNT(*) AS DECIMAL(4,2)))+N'%'
AS Display
FROM #T AS a
GROUP BY [name]
CREATE TABLE #t
(
NAME NVARCHAR(32),
score INT
)
INSERT INTO #t(name,score) VALUES(N'张三',5)
INSERT INTO #t(name,score) VALUES(N'张三',4)
INSERT INTO #t(name,score) VALUES(N'张三',5)
INSERT INTO #t(name,score) VALUES(N'李四',3)
INSERT INTO #t(name,score) VALUES(N'李四',5)
INSERT INTO #t(name,score) VALUES(N'李四',5)
INSERT INTO #t(name,score) VALUES(N'李四',4)
SELECT t1.NAME, t1.FiveScoreCounter,CAST(t1.FiveScoreCounter * 1.0 / t2.SumCounter AS DECIMAL(12,4)) 'ratio' FROM (
SELECT NAME ,COUNT(NAME) 'FiveScoreCounter' FROM #t WHERE score = 5
GROUP BY NAME)t1 INNER JOIN
(SELECT NAME,COUNT(NAME) 'SumCounter' FROM #t GROUP BY NAME)t2 ON t1.NAME = t2.name
ORDER BY t1.NAME