Roll no. |Sub1|sub2|
1. |20. |50. |
2. |80. |90. |
現在給出的示例資料我需要找出主題Sub1和Sub2之和超過100的卷號
我試試這個，但沒有運氣
Select (sub1 sub2) as total
from table
where total > 100
uj5u.com熱心網友回復：
您還必須在 where 條件中撰寫計算，因為 where 條件是在 select 陳述句之前處理的。
Select (sub1 sub2) as total
from table
where (sub1 sub2) > 100
uj5u.com熱心網友回復：
可以通過使用 CTE 在查詢之外使用別名：
CREATE TABLE tblName (roll_number INT, sub1 INT, sub2 INT);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (1 ,50, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (2, 110, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (3, 120, 20);
INSERT INTO tblName (roll_number, sub1, sub2) VALUES (4, 30, 20);
WITH cte AS (
SELECT roll_number AS RollNumber, (sub1 sub2) AS Total
FROM tblName
)
SELECT * FROM cte
WHERE Total > 100
回報：
| 卷號 | 全部的 |
|---|---|
| 2 | 130 |
| 3 | 140 |
在 MySQL 8.0 上測驗。
uj5u.com熱心網友回復：
另一種做法是使用HAVING.
嘗試：
select roll_nr,
sum(sub1 sub2) as total
from my_tbl
group by roll_nr
having total >100;
演示
uj5u.com熱心網友回復：
你可以試試這個簡單的查詢
WITH
CTE1 AS (SELECT SUM(`sub1` `sub2`) AS subject_total,id as roll_num FROM `tbl` group by id )
SELECT * FROM CTE1
WHERE subject_total > 100;
演示鏈接
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/453098.html
上一篇：嘗試執行GETREQUEST時不會出現SQL語法錯誤
下一篇：更改列的值順序