mysql - 如果没有结果，MySQL 将返回 NULL

Question

我有这种类型的查询

SELECT one, two, three FROM (
  ( SELECT data AS one FROM thetable WHERE `id` = '$id' AND name = 'one') t1,
  ( SELECT data AS two FROM thetable WHERE `id` = '$id' AND name = 'two') t2,  
  ( SELECT data AS three FROM thetable WHERE `id` = '$id' AND name = 'three') t3
)

并且当某一行缺少某一行时id，它只会跳过与id.

我希望得到这样的东西：

id:24     one:somedata      two:somedata      three:somedata
id:25     one:somedata      two:EMPTY         three:somedata
id:26     one:somedata      two:somedata      three:somedata
id:27     one:EMPTY         two:somedata      three:somedata

但我得到：

id:24     one:somedata      two:somedata      three:somedata
id:25     one:EMPTY         two:EMPTY         three:EMPTY
id:26     one:somedata      two:somedata      three:somedata
id:27     one:EMPTY         two:EMPTY         three:EMPTY

为什么？

标签： mysql

Answer 1

你可以试试下面的方法——

select id,
max(case when  name = 'one' then data end) as one,
max(case when  name = 'two' then data end) as two,
max(case when  name = 'three' then data end) as three
from thetable where `id` = '$id'
group by id