我有四个表需要连接在一起,其中一个表有两个不同的记录需要获取。guild
和party
表我得到了它们的名称,这样我就可以向用户显示名称而不是ID,charlog
表也有我想要显示的char-ID的创建日期和最后登录日期。这是桌子:char
表格:
----------------------------------------------------
| char_id | name | guild_id | party_id |
|---------------------------------------------------
| 150000 | char1 | 3 | 3 |
| 150001 | char2 | 2 | (NULL) |
| 150002 | char3 | (NULL) | 1 |
| 150003 | char4 | 1 | 2 |
----------------------------------------------------
guild
表格:-------------------------
| guild_id | name |
-------------------------
| 1 | guild_1 |
| 2 | guild_2 |
| 3 | guild_3 |
-------------------------
party
表格:-------------------------
| party_id | name |
-------------------------
| 1 | party_1 |
| 2 | party_2 |
| 3 | party_3 |
-------------------------
charlog
表格:----------------------------------------------------
| time | char_id | char_msg |
----------------------------------------------------
| 2015-02-14 06:45:32 | 150000 | make new char |
| 2015-02-14 06:45:58 | 150000 | char select |
| 2015-02-15 12:32:19 | 150001 | make new char |
| 2015-02-15 16:54:01 | 150000 | char select |
| 2015-02-15 19:23:54 | 150001 | char select |
| 2015-02-16 01:32:13 | 150002 | make new char |
| 2015-02-16 01:33:01 | 150003 | make new char |
| 2015-02-16 04:45:43 | 150000 | char select |
| 2015-02-16 07:43:22 | 150003 | char select |
----------------------------------------------------
如前所述,我需要从
make new char
表中获取charlog
条目,以便在创建角色时显示,以及在播放角色时显示的最后一个(按日期)条目,所有这些都使用一个char_id。总之,我想找一张这样的桌子:
---------------------------------------------------------------------------------------------------------------------
| char_id | name | guild_id | guild_name | party_id | party_name | create_time | lastlogin_time |
---------------------------------------------------------------------------------------------------------------------
| 150000 | char1 | 3 | guild_3 | 3 | party_3 | 2015-02-14 06:45:32 | 2015-02-16 04:45:43 |
我在Codeigniter中使用以下活动记录行来尝试获取所需的数据。它正确返回创建日期,但不返回上次登录时间(为空):
function get_char_info($cid) {
$this->db->select('char.*,guild.guild_id,guild.name AS guild_name,party.party_id,party.name AS party_name,charlog1.time AS create_time,charlog2.time AS lastlogin_time');
$this->db->from('char');
$this->db->where('char.char_id', $cid);
$this->db->join('guild', 'char.guild_id = guild.guild_id', 'left');
$this->db->join('party', 'char.party_id = party.party_id', 'left');
$this->db->join('charlog AS charlog1', 'char.char_id = charlog1.char_id AND charlog1.char_msg = "make new char"', 'left');
$this->db->join('charlog AS charlog2', 'char.char_id = charlog2.char_id AND charlog2.char_msg = (SELECT max(charlog.time) FROM charlog WHERE char_msg = "char select")', 'left');
$query = $this->db->get();
return $query->row();
}
如前所述,公会名称、政党名称和创建时间正确通过,但上次登录时间为空,没有错误。
我试过在活动记录子句中混淆一些东西,但无法显示上次登录的时间。任何帮助都将不胜感激。
最佳答案
子查询上的联接不正确:
应该charlog2.time = (SELECT....)
而不是charlog2.char_msg = (SELECT....)