Sql Query获取数据时出现问题 [英] Sql Query Problem in fetching data

问题描述

尊敬的先生/女士





我使用的是sql enterprise 2000



表名值



表是



id得分

1 10

1 20

2 5

2 10



先生我想要这样的结果

优秀是id 1得分的总和，good是id 2得分的总和



非常好

30 15

解决方案

SELECT ID，SUM（SCORE）''得分''，当ID = 1时为' '优秀''当ID = 2那么''好''结束''表现''来自ID_SCORE GROUP BY ID

在这里你去!!

< pre lang =SQL> DECLARE @ t TABLE （Id INT ，得分 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 < span class =code-keyword> 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'

这篇关于Sql Query获取数据时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！