SELECT COUNT( sendbook.id ) AS total, SUM( sendbook.num ) AS num, (
SELECT COUNT( sendbook.id )
FROM sendbook
INNER JOIN clients ON clients.id = sendbook.clientid
WHERE sendbook.issueid = '29'
AND clients.area >1000
AND clients.area <2000
) AS area1000, (
SELECT COUNT( sendbook.id )
FROM sendbook
INNER JOIN clients ON clients.id = sendbook.clientid
WHERE sendbook.issueid = '29'
AND clients.area >2000
AND clients.area <3000
) AS area2000, (
SELECT COUNT( sendbook.id )
FROM sendbook
INNER JOIN clients ON clients.id = sendbook.clientid
WHERE sendbook.issueid = '29'
AND clients.area >3000
AND clients.area <4001
) AS area2000
FROM `sendbook`
WHERE sendbook.issueid = '29'
total num area1000 area2000 area2000
8 438 3 3 2
您知道简化此查询的方法吗？
认为
最佳答案
您需要使用case语句，如下所示：
SELECT COUNT( sendbook.id ),
(CASE
WHEN clients.area between '1000' and '2000' THEN 1
WHEN clients.area between '2000' and '3000' THEN 2
WHEN clients.area between '3000' and '4000' THEN 3
END) AS myrange FROM mytable
GROUP BY myrange