php - 连接同一个表并忽略空值

Question

我有一张这样的 table

id userid    game   date_t       hdigit   inside  outside
1  user101    xy     02-09-2017    1       10
2  user101    xy     02-09-2017    1               40
3  user101    xx     02-09-2017    2       40
4  user101    xx     02-09-2017    3               90  

我想要显示数据，例如

id userid    game   date_t     hdigit inside outside
1  user101    xy    02-09-2017  1       10     40
3  user101    xx    02-09-2017  2       40
4  user101    xx    02-09-2017  3              90 

我已经尝试过了

SELECT h.userid,h.game,h.date_t,h.hdigit,h.inside,htwo.outside 
 FROM `hadap_game` AS h JOIN hadap_game AS htwo WHERE h.userid=:user
 AND htwo.userid=:uer AND h.hdigit=htwo.hdigit AND h.game=htwo.game 
 AND h.inside IS NOT NULL AND htwo.outside IS NOT NULL

它正在打印上面的内容，但我想打印也有空值的行

Answer 1

我认为您可以简单地使用GROUP BY来做到这一点？

SELECT 
    h.userid,
    h.game,
    h.date_t,
    h.hdigit,
    MAX(IFNULL(h.inside, htwo.inside)) AS inside,
    MAX(IFNULL(h.outside, htwo.outside)) AS outside
 FROM 
    hadap_game AS h 
    LEFT JOIN hadap_game AS htwo ON htwo.userid = h.userid AND htwo.hdigit = h.hdigit AND htwo.game = h.game AND htwo.id <> h.id
WHERE 
    h.userid = :user
GROUP BY
    h.userid,
    h.game,
    h.date_t,
    h.hdigit;

这会执行一些您可能不需要/想要的其他操作，但它基本上与您的原始查询相同，除了每个唯一引用没有两行时它会起作用。