在SQL中查找具有条件的列的SUM

Question

Roll no. |Sub1|sub2|
1.        |20. |50. |
2.        |80. |90. |

现在给出了样本数据，我需要找出受试者Sub1和Sub2之和超过100 的卷号

我尝试了这个，但没有运气

Select (sub1+sub2) as total
from table 
where total > 100

Answer 1

您还必须在where条件中编写计算，因为where条件是在select语句之前处理的。

Select (sub1+sub2) as total
from table 
where (sub1+sub2) > 100

Answer 2

使用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

退货：

RollNumber	总计
2	130
3	140

您可以尝试这个简单的查询

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; 

演示链接