求助高人,这样的语句,如何简化
SELECT COUNT(id) AS classcount,
(SELECT COUNT(id) AS classcount
FROM dbo.class
WHERE (f_grade_id = 1) AND type = 1) AS ptb,
(SELECT COUNT(id) AS classcount
FROM dbo.class
WHERE (f_grade_id = 1) AND type = 2) AS cwb,
(SELECT COUNT(id) AS classcount
FROM dbo.class
WHERE (f_grade_id = 1) AND type = 3) AS clb,
(SELECT COUNT(id) AS classcount
FROM dbo.class
WHERE (f_grade_id = 1) AND type = 4) AS tsb
FROM dbo.class
WHERE (f_grade_id = 1)
[解决办法]
樓上應該沒有理解語句的意思。
可以這麼簡化
SELECT COUNT(id) AS classcount,
SUM(Case When type = 1 Then 1 Else 0 End) AS ptb,
SUM(Case When type = 2 Then 1 Else 0 End) AS cwb,
SUM(Case When type = 3 Then 1 Else 0 End) AS clb,
SUM(Case When type = 4 Then 1 Else 0 End) AS tsb
FROM dbo.class
WHERE (f_grade_id = 1)
