更新时间:2022-10-16 22:07:26
SELECT ID,SUM(SCORE)''得分'',当ID = 1时为' '优秀''当ID = 2那么''好''结束''表现''来自ID_SCORE GROUP BY ID
在这里你去!!
DECLARE @ t TABLE (Id INT ,得分 INT pan>)
INSERT INTO @ t
SELECT 1 , 10
UNION ALL
SELECT 1 , 20
UNION ALL
SELECT 2 , 5
UNION ALL
SELECT 2 , 10
SELECT MAX( CASE WHEN t.Perf = ' 优秀' 那么 t.Score ELSE NULL END ) AS 非常好,
MAX( CASE WHEN t .Perf = ' Good' 那么 t .Score ELSE NULL END ) AS 好
FROM (
SELECT CASE
WHEN Id = 1 那么 'Excellent'
WHEN Id = 2 THEN ' Good'
END AS Perf,
SUM(分数) AS 得分
FROM @ t
GROUP BY
Id
) AS t
WITH UserDetails AS
(
SELECT ID,SUM(SCORE)' SCORE' FROM ID_SCORE GROUP BY ID
)
SELECT ( SELECT SCORE FROM UserDetails WHERE ID = 1)' EXCELLENT' ,( SELECT SCORE FROM UserDetails WHERE ID = 2)' GOOD'
Dear sir/ma''am
I am using sql enterprise 2000
table name value
table is
id score
1 10
1 20
2 5
2 10
sir i want the result like this
excellent is the sum of score of id 1 and good is the sum of score of id 2
excelent good
30 15
SELECT ID,SUM(SCORE)''Score'',CASE WHEN ID=1 THEN ''Excellent'' when ID=2 THEN ''Good'' END ''Performance'' FROM ID_SCORE GROUP BY ID
Here you go!!DECLARE @t TABLE (Id INT, Score INT) INSERT INTO @t SELECT 1,10 UNION ALL SELECT 1,20 UNION ALL SELECT 2,5 UNION ALL SELECT 2,10 SELECT MAX(CASE WHEN t.Perf = 'Excellent' THEN t.Score ELSE NULL END) AS Excellent, MAX(CASE WHEN t.Perf = 'Good' THEN t.Score ELSE NULL END) AS Good FROM ( SELECT CASE WHEN Id = 1 THEN 'Excellent' WHEN Id = 2 THEN 'Good' END AS Perf, SUM(Score ) AS Score FROM @t GROUP BY Id ) AS t
WITH UserDetails AS ( SELECT ID,SUM(SCORE)'SCORE' FROM ID_SCORE GROUP BY ID ) SELECT (SELECT SCORE FROM UserDetails WHERE ID=1)'EXCELLENT',(SELECT SCORE FROM UserDetails WHERE ID=2)'GOOD'