MYSQL中CASE中CASE的总数/计数 [英] SUM/COUNT of CASE within CASE in MYSQL

问题描述

在表格产品中的列下方:

Have below Column in table product :

a  |  b  | cat
--------------
2  |  4  |  1
3  |  1  |  1
4  |  4  |  1
2  |  1  |  1
3  |  2  |  1
5  |  3  |  1
8  |  7  |  2
5  |  3  |  2


$sql = "SELECT 
        SUM(CASE 
            WHEN
            cat = 1
            AND
            a > b  
            THEN 1 else 0 end) as prdplus
        FROM product
       ";

在上面的查询中，我获得的总和或计数的值大于 a 然后 b

in above query i am getting value for sum or count of a greater then b

这是3> 1，2> 1，3> 2，5> 3

which is 3>1 , 2>1 , 3>2 , 5>3

因此上述查询的输出将是: 4

hence output of above query will be : 4

prdplus
-------
4

我需要的 是计数值中只有的+1 ， 4

那上面的查询现在我需要+1值计数

thats with above query now i need +1 value count

3-1 = 2
2-1 = 1  // just explanation purpose , output i need in -> plus1 
3-2 = 1  // just explanation purpose , output i need in  -> plus1
5-3 = 2

因此，上面的查询只需要+1计数，即 2

hence with above query i need only +1 count which is 2

我不知道如何在包含上述查询的情况下写大小写，因为我需要两个都在下面的表中显示

i am not getting how to write case within case including above query as i need both count to show in my table as below

最终输出:

Output Final :

 prdplus | plus1 
----------------
  4      |  2

推荐答案

SELECT SUM(CASE WHEN a > b  
                THEN 1 
                else 0 
           end) as prdplus,
       SUM(CASE WHEN a - b = 1  
                THEN 1 
                else 0 
           end) as prdplus1
FROM product

这篇关于MYSQL中CASE中CASE的总数/计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！