根据 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 |
+------------------------+--------+

我想按 id_laporan_rekomendasi 分组，并在 status 列中有值 3 时创建一个新列.因此，如果列状态中没有值 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"表示真，0"表示假.所以这就是你想要的.

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屋！