mysql - 按范围计数仅返回一行

我正在尝试获取一个输出以用于另一个调用。
我想要的输出是

Row   |   Col   |  Data  |  Seq

<70%  | Voters  |  5519  |  8
<80%  | Voters  |  4352  |  8
<90%  | Voters  |  3251  |  8
100%  | Voters  |  2511  |  8

我的密码是

SELECT CASE
        WHEN (poll_propensity >= 0.70 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 70%'
        WHEN (poll_propensity >= 0.80 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 80%'
        WHEN (poll_propensity >= 0.90 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 90%'
        WHEN (poll_propensity >= 1 AND perm_absentee = 'N' AND mail_propensity = 0)     THEN '100%'
       END AS Row,

'Voters' AS Col,
COUNT(voter_id) AS Data,
8 AS Seq

FROM (  SELECT p.voter_id, p.perm_absentee, vp.poll_propensity, vp.mail_propensity
        FROM VoterProfile p
             INNER JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
             INNER JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
        WHERE d.district_id= 3 AND vp.election_type= 'T' ) as derived
GROUP BY Row

但我得到的结果是

Row   |   Col   |  Data  |  Seq

<70%  | Voters  |  5519  |  8

如果有人能帮忙的话，只有一个能通过

最佳答案

只需更改WHEN语句的顺序，因为1>.7中的1与.7合并在一起，您需要从.1开始，然后转到.7。

SELECT CASE
        WHEN (poll_propensity >= 1 AND perm_absentee = 'N' AND mail_propensity = 0)     THEN '100%'
        WHEN (poll_propensity >= 0.90 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 90%'
        WHEN (poll_propensity >= 0.80 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 80%'
        WHEN (poll_propensity >= 0.70 AND perm_absentee = 'N' AND mail_propensity = 0)  THEN '< 70%'
       END AS Row,

'Voters' AS Col,
COUNT(voter_id) AS Data,
8 AS Seq

FROM (  SELECT p.voter_id, p.perm_absentee, vp.poll_propensity, vp.mail_propensity
        FROM VoterProfile p
             INNER JOIN DistrictPrecinct d ON d.precinct_id=p.precinct_code
             INNER JOIN VoterPropensity vp ON p.voter_id=vp.voter_id
        WHERE d.district_id= 3 AND vp.election_type= 'T' ) as derived
GROUP BY Row

或者，您可以将操作从>=更改为

关于mysql - 按范围计数仅返回一行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/14822416/