MySQL select, between, AND, OR子句及查询问题 [英] MySQL select, between, AND, OR clause and query problem

问题描述

我有一个名为 item 的表，它有两个属性(code 和 name).
现在我想按以下方式对它们进行分组:

I have a table named item with two attributes (code and name).
Now i want to group them in the following way:

group a: code between (5300 and 5310),(7100,7200),(8210,8290)
group b: code not between (5300 and 5310),(7100,7200),(8210,8290)

如何使用 MySQL 查询来实现?

How can i do it using MySQL query?

推荐答案

组"是什么意思?

如果你想使用 GROUP BY 你可以这样做:

If you want to use GROUP BY you can do that:

SELECT
    CASE
        WHEN
            code BETWEEN 5300 AND 5310 OR
            code BETWEEN 7100 AND 7200 OR
            code BETWEEN 8210 AND 8290
        THEN 'Group A'
        ELSE 'Group B'
    END AS grp,
    COUNT(*) AS cnt
FROM your_table
GROUP BY grp

也许你只是想订购?

SELECT
    CASE
        WHEN
            code BETWEEN 5300 AND 5310 OR
            code BETWEEN 7100 AND 7200 OR
            code BETWEEN 8210 AND 8290
        THEN 'Group A'
        ELSE 'Group B'
    END AS grp,
    *
FROM your_table
ORDER BY grp

另请注意，BETWEEN 包括两个端点，这可能不是您的意思.

Also note that BETWEEN includes both endpoints which might not be what you meant.

这篇关于MySQL select, between, AND, OR子句及查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！