MySQL根据两列对数据进行分类

Question

   **Base table:**

   col 1     col2
    25         184
    35         200
    21         75
    10         206
    55         114 
    75         190

    **Expected output:**

             0-50  50-100  100-150  >150
    0-10      0       0       0       1
    10-20     0       1       0       0
    20-30     0       0       0       1
    >30       0       0       1       2

I need to write a MySQL query to print the count of values from two columns(col1, col2) into the rite box in a matrix format (as given above).

我使用过的查询:

select 
case 
when col1 between 0 and 10 then '0-10'
when col1 between 10 and 20 then '10-20'
when col1 between 20 and 30 then '20-30'
else '>30'
end as freq,
count(
case 
when col2 between 0 and 50 and col1 between 0 and 10 then '0-10'
when col2 between 0 and 50 and col1 between 10 and 20 then '10-20'
when col2 between 0 and 50 and col1 between 20 and 30 then '20-30'
when col2 between 0 and 50 and col1 >30 then '>30'
end) '0-50',
count(
case 
when col2 between 50 and 100 and col1 between 0 and 10 then '0-10'
when col2 between 50 and 100 and col1 between 10 and 20 then '10-20'
when col2 between 50 and 100 and col1 between 20 and 30 then '20-30'
when col2 between 50 and 100 and col1 >30 then '>30'
end) '50-100',
count(
case 
when col2 between 100 and 150 and col1 between 0 and 10 then '0-10'
when col2 between 100 and 150 and col1 between 10 and 20 then '10-20'
when col2 between 100 and 150 and col1 between 20 and 30 then '20-30'
when col2 between 100 and 150 and col1 >30 then '>30'
end) '100-150',
count(
case
when col2>150 and col1 between 0 and 10 then '0-10'
when col2>150 and col1 between 10 and 20 then '10-20'
when col2>150 and col1 between 20 and 30 then '20-30'
when col2>150 and col1 >30 then '>30'
end) '>150',
count(col2)
from table_1 a 
group by freq;

我没有得到正确的记录计数。正确的计数仅出现在输出中的第一行。不知道为什么其他人会出错。非常感谢任何帮助。

谢谢。

Answer 1

这是我犯的一个错误。在包括限制时，我使用第一个 case 语句中的上限作为下一个语句中的下限。 非常感谢您的所有投入。我可以对下面给出的两个不同的查询执行相同的操作:

**Query1:**

select 
case 
when f_value between 0 and 25 then '0-25'
when f_value between 26 and 50 then '25-50'
when f_value between 51 and 75 then '50-75'
when f_value between 76 and 100 then '>75'
else 'others'
end as freq,
count(
case 
when r_value between 0 and 500 and f_value between 0 and 25 then '0-25'
when r_value between 0 and 500 and f_value between 26 and 50 then '25-50'
when r_value between 0 and 500 and f_value between 51 and 75 then '50-75'
when r_value between 0 and 500 and f_value>=76 then '>75'
end) '0-500',
count(
case 
when r_value between 501 and 1000 and f_value between 0 and 25 then '0-25'
when r_value between 501 and 1000 and f_value between 26 and 50 then '25-50'
when r_value between 501 and 1000 and f_value between 51 and 75 then '50-75'
when r_value between 501 and 1000 and f_value>=76 then '>75'
end) '500-1000',
count(
case 
when r_value between 1001 and 1500 and f_value between 0 and 25 then '0-25' 
when r_value between 1001 and 1500 and f_value between 26 and 50 then '25-50' 
when r_value between 1001 and 1500 and f_value between  51 and 75 then '50-75'
when r_value between 1001 and 1500 and f_value>=76 then '>75'
end) '1000-1500',
count(
case
when r_value> 1501  and f_value between 0 and 25 then '0-25' 
when r_value> 1501 and f_value between 26 and 50 then '25-50'  
when r_value> 1501 and f_value between 51 and 75 then '50-75' 
when r_value> 1501 and f_value>=76 then '>=75'
end) '>1501'
from em_mem_prfm_cat_hist a 
where rfm_calc_batch=4
group by freq;


**Query 2:**
SELECT 
    b.x,
    max(if(y = '0-500', sss, null)) '0-500',
    max(if(y = '501-1000', sss, null)) '501-1000',
    max(if(y = '1001-1500', sss, null)) '1001-1500',
    max(if(y = '1501-2000', sss, null)) '1501-2000'
FROM
    ((SELECT 
        x, y, count(1) as sss
    FROM
        (SELECT 
        CASE
                WHEN f_value BETWEEN 0 AND 25 THEN '0-25'
                WHEN f_value BETWEEN 26 AND 50 THEN '26-50'
                WHEN f_value BETWEEN 51 AND 75 THEN '51-75'
                WHEN f_value >= 76 THEN '76-100'
            END AS x,
            CASE
                WHEN r_value BETWEEN 0 AND 500 THEN '0-500'
                WHEN r_value BETWEEN 501 AND 1000 THEN '501-1000'
                WHEN r_value BETWEEN 1001 AND 1500 THEN '1001-1500'
                WHEN r_value >= 1501 THEN '1501-2000'
            END AS y
    FROM
        em_mem_prfm_cat_hist
    where
        rfm_calc_batch = 4) a
    GROUP BY x , y)) b
group by x
order by x