转换输出范围的mysql查询的结果

zzoitvuj 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(270)

我的源表（wplott\u wpkl\u winner）包含字段“彩票号码”，其中包含1到6位数字和相应的“抽奖日期”。

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
    .
    .
    .

非常感谢您的帮助。我确实在寻找类似的答案，但没有一个答案对我的具体情况有帮助。
提前谢谢！

mysql wordpress

来源：https://stackoverflow.com/questions/53239437/transpose-the-results-of-a-mysql-query-that-outputs-ranges

1条答案

按热度按时间

nmpmafwu1#

一种方法使用一系列联合：

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 ，因为条件和已经处理了这个逻辑。

赞(0）回复(0）举报 2021-06-18

我来回答

转换输出范围的mysql查询的结果

1条答案

相关问题

热门标签

最新问答