我无法解决这个问题很多小时.
这是我的桌子
t1:
–––––––––––––––––––––––––––––––––––
| id | text | lang | transl_id |
–––––––––––––––––––––––––––––––––––
| 1 | first | en | 222 |
–––––––––––––––––––––––––––––––––––
| 2 | second | de | 222 |
–––––––––––––––––––––––––––––––––––
| 3 | jkj | de | 234 |
–––––––––––––––––––––––––––––––––––
| 4 | 89080 | de | 235 |
–––––––––––––––––––––––––––––––––––
这是我的查询:
SELECT
transl_id AS property,
(SELECT text FROM t1 WHERE lang='en') AS value1,
(SELECT text FROM t1 WHERE lang='de') AS value2,
FROM t1
它返回下表:
–––––––––––––––––––––––––––––––––––
| property | value1 | value2 |
–––––––––––––––––––––––––––––––––––
| 222 | first | |
–––––––––––––––––––––––––––––––––––
| 222 | | second |
–––––––––––––––––––––––––––––––––––
| 234 | jkj | |
–––––––––––––––––––––––––––––––––––
| 235 | 89080 | |
–––––––––––––––––––––––––––––––––––
每行都具有value1或value2,从没有.有没有一种方法可以将结果分组,以使属性字段具有相等值的行排成一排?我的意思是这样的:
–––––––––––––––––––––––––––––––––––
| property | value1 | value2 |
–––––––––––––––––––––––––––––––––––
| 222 | first | second |
–––––––––––––––––––––––––––––––––––
...
解决方法:
试试这个查询:
SELECT
property,
max(value1) as Value1,
max(value2) as Value2
FROM
(
SELECT transl_id AS property,
CASE when lang = 'en' then text else null end as value1,
CASE when lang = 'de' then text else null end as value2
FROM t1
) t
GROUP BY property
尝试在表中添加更多值,并获得了预期的结果in this SQLFiddle.
标签:mysql
来源: https://codeday.me/bug/20191127/2076008.html