sql-具有多个WHERE条件的count - sql - count with multiple WHERE condition

sql - count with multiple WHERE condition

本文关键字：条件 count WHERE sql- | 更新日期: 2023-09-27

我有这样的表宽度结构：

record  | color| status
--------------------
record1 | blue | x
record1 | blue | y
record2 | red | x
record3 | yellow  | y

我想计数按"颜色"分组的记录，每个记录计数"x"answers"y"。我想得到这样的结果：

color=蓝色，数量=2、x=1和y=1color=红色，数量=1、x=1、y=0color=黄色，数量=1、x=0和y=1

我的查询：

SELECT color as clr,
       count(record) as cnt
FROM table
WHERE status IN (x, y)
GROUP BY  week(color)

我在php中打印结果，如下所示：

while ($row= mysql_fetch_assoc($query)){
     echo "color=".$row['clr'].", quantity=".$row['cnt'].", x=".$row['cnt'][0].", y=".$row['cnt'][1];
}

所以问题是我在php中打印结果的方法不起作用。

像这样尝试

查询：

   SELECT color,
   count(color) as quantity,
   LENGTH(GROUP_CONCAT(status)) - LENGTH(REPLACE(GROUP_CONCAT(status), 'x', '')) as 'x',
   LENGTH(GROUP_CONCAT(status)) - LENGTH(REPLACE(GROUP_CONCAT(status), 'y', '')) as 'y'
   FROM table 
   WHERE status IN('x','y')
   GROUP BY color

PHP:

   while ($row= mysql_fetch_assoc($query)){
     echo "color=".$row['color'].", quantity=".$row['quantity'].",x=".$row['x'].", y=".$row['y'];
   }

使用条件聚合：

select color,
       count(*) as quantity,
       sum(status = 'x') as x,
       sum(status = 'y') as y
from t
group by color;