按条件分组

hec6srdp 于 2021-06-18 发布在 Mysql

关注(0)|答案(4)|浏览(291)

我必须在php中显示一些数据数据的结构是这样的

id  channel_id  added_by

1     1          9999
2     1          195
3     8          9999
4     1          180
5     1          195
6     8          9999
7     1          9999
8     1          195
9     8          9999

我只需要像这样显示通道id=1的总计数

channel_id   total  added_by

1            2           9999
1            4          -1

它的所有通道id的类似计数（由9999相加）和所有其他相加的计数都假定为（-1）
我可以像这样做

SELECT channel_id, added_by, sum(id) as total 
from table 
where channel_id = 1 
group by channel_id  ,added_by;

但它给出的结果不是我所需要的

channel_id  added_by total 
1            9999      2
1             195      3
1             180      1

mysql php

来源：https://stackoverflow.com/questions/53262733/group-by-with-conditions

4条答案

按热度按时间

4uqofj5v1#

你可以试试这个：

SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;

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

zbwhf8kr2#

http://sqlfiddle.com/#!9/437406/2

SELECT channel_id,
       IF(added_by=9999,9999,-1) new_col,
       COUNT(*)
FROM `table`
GROUP BY channel_id, new_col

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

vsmadaxz3#

使用 CASE..WHEN 表达式中，可以确定“已修改” added_by 价值，以及 GROUP BY 改为使用修改后的值。
也， SUM(id) 不会给出正确的行数。它宁愿加起来 id 价值观。你可以用 COUNT(*) 而是要计算行数：

SELECT channel_id, 
       CASE 
         WHEN added_by <> 9999 THEN -1 
         ELSE 9999 
       END AS modified_added_by, 
       COUNT(*) as total 
FROM your_table_name 
WHERE channel_id = 1 
GROUP BY channel_id, modified_added_by

结果

| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1          | -1                | 4     |
| 1          | 9999              | 2     |

db fiddle视图

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

ac1kyiln4#

希望这对你有帮助。 SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;

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