php - 使用左连接但没有显示值？

Question

我有 2 个表，game_jnship_equip_list 是存储设备的基本信息，game_jnship_equip 是存储玩家拥有的东西。

这是game_jnship_equip_list数据:

+--------+----------------+----------+----------+
| ID     | desc           | name     | type     |
+--------+----------------+----------+----------+
| 1      | hello_weapon   | weapon   | 1        |
| 2      | hello_shirt    | shirt    | 2        |
| 3      | hell_weapon    | Hweapon  | 1        |
+-----------------------------------------------+

这是game_jnship_equip数据:

+------+----------+--------------+------------+------------+
| ID   | userid   | itemcode     | atfigure   | eposition  |
+------+----------+--------------+------------+------------+
| 1    | 1        | 1            | 100        | 1          |
| 2    | 1        | 2            | 500        | 2          |
+----------------------------------------------------------+

我查询如下:

$quequip = DB::query("SELECT t1.*,t2.name AS weaponame, t2.edesc AS weapondesc, t3.atfigure AS atkbonus, t4.name AS shirtname, t4.edesc AS shirtdesc  FROM ".DB::table('game_jnship_equip')." t1 LEFT JOIN ".DB::table('game_jnship_equip_list')." t2 ON (t1.itemid = t2.id AND t1.eposition = '1') LEFT JOIN ".DB::table('game_jnship_equip')." t3 ON (t2.type = t3.eposition) LEFT JOIN ".DB::table('game_jnship_equip_list')." t4 ON (t1.itemid = t4.id AND t1.eposition = '2') WHERE t1.uid = 'userid' AND t1.status = '1'"); 
$ruequip = DB::fetch($quequip);

但是，我只能得到如下值:

$ruequip['weaponame'] = weapon;
$ruequip['weapodesc'] = hello_weapon;
$ruequip['atkbonus'] = 100;

然后关于t4全是空白。

$ruequip['shirtname'] = ;
$ruequip['shirtdesc'] = ;

我希望它显示如下值:

$ruequip['shirtname'] = shirt;
$ruequip['shirtdesc'] = hello_shirt;

那么如何解决这个问题呢？和我的 DB::query 函数，它不允许 DB::query(SELECT * FROM xxxx (SELECT * FROM))，意味着 2 select inside 1 DB::查询，系统会因安全问题拒绝。

谢谢。

Answer 1

您可以通过使用 CASE EXPRESSION 而不是 3 个左连接的条件聚合来做到这一点:

SELECT s.id,s.userid,s.itemcode,s.atfigure,s.eposition,
       MAX(CASE WHEN s.eposition = 1 THEN s.desc END) as weap_desc,
       MAX(CASE WHEN s.eposition = 1 THEN s.name END) as weap_name,
       MAX(CASE WHEN s.eposition = 2 THEN s.desc END) as shirt_desc,
       MAX(CASE WHEN s.eposition = 2 THEN s.name END) as shirt_name
FROM (SELECT t.id,t.userid,t.itemcode,t.atfigure,t.eposition,t2.desc,t2.name
      FROM  ".DB::table('game_jnship_equip')." t
      LEFT OUTER JOIN ".DB::table('game_jnship_equip_list')." t2
       ON(t1.itemid = t2.id AND t.eposition = t2.type) )s
GROUP BY s.user_id