| ID | reg_No | 主題 ID | 字幕 | 分數 | 班級號 |
|---|---|---|---|---|---|
| 1 | 98 | 23 | 數學 | 90 | 2 |
| 2 | 98 | 21 | 英語語言 | 60 | 2 |
| 3 | 98 | 24 | 物理 | 78 | 2 |
| 4 | 98 | 23 | 化學 | 100 | 2 |
| 5 | 98 | 21 | 生物學 | 81 | 2 |
| 6 | 98 | 24 | 農業 | 87 | 2 |
我想選擇SUM(score)包括英語和數學在內的四 (4) 門學科中最好的。
它假設總和 90 60 100 87 = 337
但是，它總結了整個列
這是我的查詢
SELECT SUM(score)
FROM table1
WHERE reg_no = 98
AND class_id=2
ORDER BY CASE WHEN sub_title IN ('English Language','Mathematics')
THEN 0
ELSE 1 END, score DESC LIMIT 4
uj5u.com熱心網友回復：
拆分為兩個查詢的簡單方法。第一個獲取數學和英語分數值，第二個獲取剩余值中的兩個最高分。
SQL 服務器：
With CTE As (
Select Top 2 Score From table1 Where reg_no = 98 And class_id=2 And sub_title Not In ('MATHEMATICS','ENGLISH LANG')
Order by Score Desc
Union All
Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title In ('MATHEMATICS','ENGLISH LANG')
)
Select Sum(Score)
From CTE
MySQL：
With CTE As (
Select Score
From
(Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title Not In ('MATHEMATICS','ENGLISH LANG')
Order by Score Desc
Limit 2) As S
Union All
Select Score From table1 Where reg_no = 98 And class_id=2 And sub_title In ('MATHEMATICS','ENGLISH LANG')
)
Select Sum(Score)
From CTE
uj5u.com熱心網友回復：
的優先級SELECT高于LIMIT，因此，您必須使用子查詢
SELECT sum(score)
FROM
(
SELECT *
FROM tab
WHERE reg_no = 98 AND class_id=2
ORDER BY CASE WHEN sub_title IN ('English Language','Mathematics')
THEN 0
ELSE 1 END, score DESC
LIMIT 4
) t
演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/389542.html
標籤：mysql sql sql-server
上一篇：替換函式SQL