根据CASE和GROUP BY结果创建一个新列 [英] Make a new column based from CASE and GROUP BY result

问题描述

我有这样的表，称为表test:

I have table like this called, table test:

+------------------------+--------+
| id_laporan_rekomendasi | status |
+------------------------+--------+
|                      1 |      2 |
|                      1 |      2 |
|                      1 |      2 |
|                      1 |      3 |
|                      2 |      2 |
|                      2 |      2 |
|                      2 |      2 |
|                      2 |      3 |
|                      3 |      2 |
|                      3 |      3 |
|                      4 |      2 |
|                      5 |      2 |
|                      5 |      3 |
|                      6 |      2 |
+------------------------+--------+

当列status中的值为3时，我想按id_laporan_rekomendasi分组并创建一个新列.因此，如果列状态中没有值3，则该值将为0，但是如果值3比1.

I want to group by id_laporan_rekomendasi and make a new column when in column status there is value 3. so if there is no value 3 in column status, then the value would be 0, but if there is value 3 than 1.

我希望结果会是这样

+------------------------+------+
| id_laporan_rekomendasi | test |
+------------------------+------+
|                      1 |    1 |
|                      2 |    1 |
|                      3 |    1 |
|                      4 |    0 |
|                      5 |    1 |
|                      6 |    0 |
+------------------------+------+

我已经尝试过该查询

SELECT t1.id_laporan_rekomendasi, 
COUNT(distinct case when t1.status = 3 then 1 else 0 end) as test
FROM test t1
GROUP BY t1.id_laporan_rekomendasi

但是我得到如下结果

+------------------------+------+
| id_laporan_rekomendasi | test |
+------------------------+------+
|                      1 |    2 |
|                      2 |    2 |
|                      3 |    2 |
|                      4 |    1 |
|                      5 |    2 |
|                      6 |    1 |
+------------------------+------+

有人可以帮我做这张桌子吗?

Does anyone could help me with this table ?

推荐答案

您很亲密.在MariaDB中，您可以将其简化为:

You are close. In MariaDB, you can simplify this to:

SELECT t1.id_laporan_rekomendasi, 
       MAX( t1.status = 3 ) as test
FROM test t1
GROUP BY t1.id_laporan_rekomendasi;

MariaDB(和MySQL)将布尔表达式视为数字，"1"为true，"0"为false.所以这就是您想要的.

MariaDB (and MySQL) treat boolean expressions as numbers, with "1" for true and "0" for false. So this does what you want.

这篇关于根据CASE和GROUP BY结果创建一个新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！