GROUP BY 子句中的 mysql IF

mysql IF in GROUP BY clause

提问by user949000

Is there a way do the if in group by clause?

有没有办法在 group by 子句中使用 if？

I have a query where I want to group the result based on the value of a column

我有一个查询，我想根据列的值对结果进行分组

if the column is Null, i want the result to remain as it is but if not, i want to group it by that value? how do you do that?

如果该列为空，我希望结果保持原样，但如果不是，我想按该值对其进行分组？你是怎样做的？

edit: sorry I think i should put a more specific example

编辑：对不起，我想我应该举一个更具体的例子

the columns below contains the id of category, thread and reply

下面的列包含类别、线程和回复的 id

it's for a forum

这是一个论坛

the ones with null values means they don't have any reply in them

具有空值的那些意味着他们没有任何回复

if the reply is null, i don't want to group it

如果回复为空，我不想将其分组

the purpose is for counting the replies and the threads inside a category

目的是计算类别内的回复和线程数

i did not set the value for reply to be null, they are like that because of the result of a join

我没有将回复的值设置为空，因为连接的结果它们是这样的

| category | thread | reply   |
-------------------------------
| 1        | 1      | 1       |
| 1        | 1      | 2       |
| 1        | 2      | 3       |
| 2        | 3      | 4       |
| 3        | 4      | 5       |
| 3        | 4      | 6       |
| 4        | 5      | null    |
| 5        | 6      | null    |

then the result would be

那么结果将是

| category | thread | reply |
-----------------------------
| 1        | 3      | 3     |
| 2        | 1      | 1     |
| 3        | 2      | 2     |
| 4        | 1      | null  |
| 5        | 1      | null  |

回答by Curt

You can actually include a CASE statement in a GROUP BY or ORDER BY clause:

您实际上可以在 GROUP BY 或 ORDER BY 子句中包含 CASE 语句：

ORDER BY CASE
            WHEN reply IS NULL THEN 1  
            ELSE 0 
         END, category

回答by Ludo - Off the record

As of mysql 5.6 you can use a IF() in the order by clause.

从 mysql 5.6 开始，您可以在 order by 子句中使用 IF()。

Like this:

像这样：

ORDER BY IF(reply=NULL, 1, 0), category

Maybe not the right answer for your specific case, but handy for other people searching for something like this.

对于您的特定情况，可能不是正确的答案，但对于其他搜索此类内容的人来说很方便。

回答by Vitaly

If I understand you right try this way:

如果我理解你的正确尝试这种方式：

(
SELECT category, COUNT(thread) AS thread, COUNT(reply) AS reply
  FROM test
  WHERE reply IS NOT NULL
  GROUP BY category
)
UNION ALL
(
SELECT category, COUNT(thread) AS thread, reply
  FROM test
  WHERE reply IS NULL
  GROUP BY category
)
ORDER BY category

SQL Fiddle

SQL小提琴

回答by Andreas Wederbrand

MySQL will group all rows with fr=null into one, just as if it was a normal value.

MySQL 会将所有 fr=null 的行归为一个，就好像它是一个普通值一样。

To solve your problem I would do a sub select of your table where a new column either got the value of fr (in the case where it's not null) or a incremented value well out of the normal scope of fr, perhaps negative numbers. And then do the outer selects group by on that new virtual column.

为了解决你的问题，我会对你的表做一个子选择，其中一个新列要么得到 fr 的值（在它不为空的情况下）或一个远远超出 fr 正常范围的增量值，可能是负数。然后在新的虚拟列上执行外部选择分组。

set @i := 0;

select if(virtualFR<0,null,virtualFR) as fr, sum(fr_sum) from
(select *, if(fr is null,@i:=@i-1,fr) as virtualFR from myTable) virtual
group by virtualFR;

sqlfiddle

sqlfiddle

回答by Bohemian

You can simply do this:

你可以简单地这样做：

select min(id) id, sum(fr_sum) fr_sum, fr
from mytable
group by 3

回答by Luv

Try using Group By

尝试使用分组依据

select 
min(id), 
sum(fr_sum), 
fr
from Table1
group by fr

SQL Fiddle

SQL小提琴