MySQL：多行到多个字段？

I have this table:

我有这张桌子:

userid | property
1 | propA
1 | propC
2 | propA
2 | propB

And need this output for an csv export/import

并且需要此输出用于csv导出/导入

userid;propA;propB;probC
1;yes;no;yes
2;yes;yes;no

Is that possible without any script language like php? It would be totally okay for me if "yes" and "no" are just "1" and "0" (e.g.) but it is important that I need just one row for each user and one field for each property.

没有像php那样的脚本语言可以吗?如果“是”和“否”只是“1”和“0”(例如),那对我来说完全没问题,但重要的是我每个用户只需要一行,每个属性需要一个字段。

1 个解决方案

#1

You can use conditional aggregation. However, your format is not CSV (hint: the "C" means comma).

您可以使用条件聚合。但是,您的格式不是CSV(提示:“C”表示逗号)。

To get that format, you can do:

要获得该格式,您可以:

select t.userid,
       max(case when t.property = 'propA' then 'Yes' else 'No' end) as PropA,
       max(case when t.property = 'propB' then 'Yes' else 'No' end) as PropB,
       max(case when t.property = 'propC' then 'Yes' else 'No' end) as PropC
from table t
group by t.userid;

This happens to work because "Yes" is later in the alphabet than "No". Personally, I would just use numbers, 0 and 1:

这恰好起作用,因为“是”后面的字母表比“否”。就个人而言,我只会使用数字,0和1:

select t.userid,
       max(t.property = 'propA') as PropA,
       max(t.property = 'propB') as PropB,
       max(t.property = 'propC') as PropC
from table t
group by t.userid;

#1