我有一个sql查询:
SELECT p.id, p.firstname, p.lastname, p.emailcollected, p.phone, e.accountid, e.accounttype
FROM player p
JOIN bankaccountinfo e ON p.id = e.playerid
WHERE ( ( p.createtime > 0 AND p.createtime < 2000000000 ) OR
( p.updatetime > 0 AND p.updatetime < 200000000000000 ) )
AND e.accounttype IN ( 5002,5003 ) ;
此返回结果在2行中有2个枚举类型的帐户类型:
+--------------+-----------+----------+----------------+------------+---------------+-------------+
| id | firstname | lastname | emailcollected | phone | accountnumber | accounttype |
+--------------+-----------+----------+----------------+------------+---------------+-------------+
| 9 | dineshND | NineND | 1536064455 | 5175761111 | 102323 | 5002 |
| 9 | dineshND | NineND | 1536064455 | 5175761111 | 102324 | 5003 |
+--------------+-----------+----------+----------------+------------+---------------+-------------+
但我希望accountnumber以单行形式显示,即savingaccountnumber和checkingaccountnumber基于accounttype。
预期结果:
+--------------+-----------+----------+----------------+------------+---------------------+-----------------------+
| id | firstname | lastname | emailcollected | phone | savingaccountnumber | checkingaccountnumber |
+--------------+-----------+----------+----------------+------------+---------------------+-----------------------+
| 9 | dineshND | NineND | 1536064455 | 5175761111 | 102323 | 102324 |
+--------------+-----------+----------+----------------+------------+---------------------+-----------------------+
accounttype为5003时保存AccountNumber列
AccountType为5002时检查AccountNumber列
mysql版本:mysql ver14.12 distrib 5.0.51a,适用于使用readline 5.2的debian linux gnu(i486)
2条答案
按热度按时间x8goxv8g1#
从您的样本数据和预期结果,您可以尝试使用条件加重函数
j7dteeu82#
你需要加入
bankaccountinfo
两次把东西排成一行:一次是支票,一次是储蓄。假设您(a)希望返回一行,即使缺少帐户记录,并且(b)在上定义了唯一键bankaccountinfo
为了playerid, accounttype
:不管怎样,温习一下join和in做了什么,以了解为什么会得到两个结果。