mysql 'as' 相关

Question

第一段代码工作正常，并给出了我需要的结果。我现在想做的是让它在 'as num' 上返回 3 个数字值对于“as num”上的 3 个不同值，对于同一列上的 3 个不同位置

        SELECT COUNT(garage_list.account_id) as num, 
        garage_list.account_id, 
        garage_list.tank_id,
        player_list.clan,
        player_list.nickname,
        tank_list.level
    FROM garage_list 
        RIGHT JOIN player_list ON garage_list.account_id = player_list.account_id 
        RIGHT JOIN tank_list ON garage_list.tank_id = tank_list.tank_id

      where tank_list.level='8' 

      and player_list.clan = 'bad-1'
          GROUP BY account_id 

以上作品

需要为每个“其中tank_list.level='??'”提供一个返回值”

SELECT COUNT(garage_list.account_id) as num, 
        garage_list.account_id, 
        garage_list.tank_id,
        player_list.clan,
        player_list.nickname,
        tank_list.level
    FROM garage_list 
        RIGHT JOIN player_list ON garage_list.account_id = player_list.account_id 
        RIGHT JOIN tank_list ON garage_list.tank_id = tank_list.tank_id


      where tank_list.level='6'  //return a value for this
      where tank_list.level='8'  //return a value for this
      where tank_list.level='10'  //return a value for this

      and player_list.clan = 'bad-1'

      GROUP BY account_id 

Answer 1

您需要使用 CASE 的条件 COUNT:

SELECT COUNT(case when tank_list.level='6' then garage_list.account_id end) as num1,
       COUNT(case when tank_list.level='8' then garage_list.account_id end) as num2,
       COUNT(case when tank_list.level='10' then garage_list.account_id end) as num3, 
        garage_list.account_id, 
        garage_list.tank_id,
        player_list.clan,
        player_list.nickname,
        tank_list.level
    FROM garage_list 
        RIGHT JOIN player_list ON garage_list.account_id = player_list.account_id 
        RIGHT JOIN tank_list ON garage_list.tank_id = tank_list.tank_id


      where tank_list.level in ('6','8','10')

      and player_list.clan = 'bad-1'

      GROUP BY account_id 

并且您需要检查您的 Outer Join 逻辑是否正确。