MySQL 根据不同表的条件进行计数

Question

我有下表。

Table : types--------------------id | type--------------------1  | AA--------------------2  | BB--------------------3  | AA--------------------4  | BB--------------------    Table : users--------------------id | username--------------------1  | abc--------------------2  | bcd--------------------3  | cde--------------------4  | def--------------------                    Table : methods---------------------------------id | user_id | details  | type_id---------------------------------1  |  1      | detail_1 | 1---------------------------------2  |  1      | detail_2 | 3---------------------------------3  |  1      | detail_3 | 1---------------------------------4  |  1      | detail_4 | 3---------------------------------5  |  2      | detail_3 | 1---------------------------------6  |  2      | detail_5 | 2---------------------------------7  |  2      | detail_6 | 4---------------------------------8  |  2      | detail_2 | 3---------------------------------9  |  1      | detail_2 | 3---------------------------------10 |  1      | detail_2 | 3---------------------------------Desired Result : ---------------------------------------------------UserName | No_of_AA_details | No_of_BB_details |---------------------------------------------------abc      | 4                | 0                |---------------------------------------------------bcd      | 2                | 2                |---------------------------------------------------

I need to get the count of distinct details based on the type from types table.

I have tried this queries but max I am getting is all the counts and not the distinct values.

SELECT u.username,
CASE WHEN t.type = 'AA' THEN count(distinct m.details) END AS No_of_AA_details,
CASE WHEN t.type = 'BB' THEN count(distinct m.details) END AS No_of_BB_details
FROM users as u inner join methods as m on u.id = m.user_id inner join types as t on t.id = m.type_id 
GROUP BY m.user_id


SELECT u.username,
SUM(t.type = 'AA') AS No_of_AA_details,
SUM(t.type = 'AA') AS No_of_BB_details
FROM users as u inner join methods as m on u.id = m.user_id inner join types as t on t.id = m.type_id 
GROUP BY m.user_id

欢迎任何建议。

Answer 1

我无法测试它，但我认为你有一个好主意，你可以尝试一下吗:

SELECT u.username,
  m.user_id,
  CASE
    WHEN t.type = 'AA' THEN 1
    ELSE 0
  END AS No_of_AA_details,
  CASE
    WHEN t.type = 'BB' THEN 1
    ELSE 0
  END AS No_of_BB_details
FROM users as u
INNER JOIN methods as m on u.id = m.user_id
INNER JOIN types as t on t.id = m.type_id

现在你只需要做总和:

SELECT u.username,
      m.user_id,
      SUM (CASE
        WHEN t.type = 'AA' THEN 1
        ELSE 0
      END ) AS No_of_AA_details,
      SUM (CASE
        WHEN t.type = 'BB' THEN 1
        ELSE 0
      END ) AS No_of_BB_details
    FROM users as u
    INNER JOIN methods as m on u.id = m.user_id
    INNER JOIN types as t on t.id = m.type_id
    GROUP BY u.username, m.user_id