转置输出范围的MySQL查询的结果

Question

我的源表(wplott_wpkl_winner)包含带1至6个数字的字段"lottery_number“和相应的"draw_date”。

    lottery_number | draw_date
==================================
    0024           | 2018-11-10
    4456           | 2018-11-10
    3895           | 2018-11-10
    4557           | 2018-11-10
    4225           | 2018-11-10
    2896           | 2018-11-10
    3354           | 2018-11-10
    1895           | 2018-11-10
    78466          | 2018-11-10
    998556         | 2018-11-10

我当前的MYSQL查询如下(我正在尝试将数据分组到范围中)

select
        count(case when wplott_wpkl_winner.lottery_number between 0 and 999 then 1 end) `0-999`,
        count(case when wplott_wpkl_winner.lottery_number between 1000 and 1999 then 1 end) `1000-1999`,
        count(case when wplott_wpkl_winner.lottery_number between 2000 and 2999 then 1 end) `2000-2999`,
        count(case when wplott_wpkl_winner.lottery_number between 3000 and 3999 then 1 end) `3000-3999`,
        count(case when wplott_wpkl_winner.lottery_number between 4000 and 4999 then 1 end) `4000-4999`,
        count(case when wplott_wpkl_winner.lottery_number between 5000 and 5999 then 1 end) `5000-5999`,
        count(case when wplott_wpkl_winner.lottery_number between 6000 and 6999 then 1 end) `6000-6999`,
        count(case when wplott_wpkl_winner.lottery_number between 7000 and 7999 then 1 end) `7000-7999`,
        count(case when wplott_wpkl_winner.lottery_number between 8000 and 8999 then 1 end) `8000-8999`,
        count(case when wplott_wpkl_winner.lottery_number between 9000 and 9999 then 1 end) `9000-9999`
    from wplott_wpkl_winner
    where CHAR_LENGTH(wplott_wpkl_winner.lottery_number) = 4 AND wplott_wpkl_winner.draw_date > '2013-06-30'

它提供了以下输出

    0-999 | 1000-1999 | 2000-2999 | 3000-3999 | 4000- 4999 .... etc
=====================================================================
    1     | 1         | 1         | 2         | 3

但是，我想获得以下格式的输出。

    Range     | Count
=======================
    0-999     | 1
    1000-1999 | 1
    2000-2999 | 1
    3000-3999 | 2
    4000-4999 | 3
    .
    .
    .

任何帮助都是非常感谢的。我确实搜索了一个类似的答案，但没有一个答案对我的特殊情况有所帮助。

提前感谢！

Answer 1

一种方法使用一系列工会：

SELECT
    `range`,
    count
FROM
(
    SELECT 1 AS pos, '0-999' AS `range`, COUNT(*) AS count
    FROM wplott_wpkl_winner
    WHERE draw_date > '2013-06-30' AND lottery_number BETWEEN 0 AND 999
    UNION ALL
    SELECT 2, '1000-1999', COUNT(*)
    FROM wplott_wpkl_winner
    WHERE draw_date > '2013-06-30' AND lottery_number BETWEEN 1000 AND 1999
    UNION ALL
    ...      -- fill in remaining ranges here
) t
ORDER BY pos;

请注意，我引入了计算列pos，以便在最终输出中保持所需的范围排序。另外，我删除了对CHAR_LENGTH的lottery_number的检查，因为条件和已经处理了这个逻辑。