mysql - 统计另一个属性对应的列中多个字段的多次出现

Question

我有一个表的字段为(NAME FRUIT TIME)

|   NAME |  FRUIT |  TIME |
|--------|--------|-------|
|   AJAY |  MANGO | 10:10 |
| SACHIN |  APPLE | 12:00 |
|    RAJ |  MANGO | 10:00 |
|   AJAY |  MANGO | 12:00 |
|   AJAY |  MANGO | 11:00 |
|   AJAY |  APPLE | 12:00 |
|    RAJ | BANANA | 12:00 |
|   AJAY | BANANA | 12:00 |
| SACHIN | BANANA |  2:00 |
| SACHIN |  MANGO | 12:00 |
|    RAJ |  MANGO | 12:00 |
| SACHIN |  APPLE | 12:00 |
|   AJAY |  APPLE | 12:00 |
|   AJAY |  APPLE | 12:00 |

现在我想像这样从上表中获取输出:

|   NAME | MANGOCOUNT | APPLECOUNT | BANANACOUNT |
|--------|------------|------------|-------------|
|   AJAY |          3 |          3 |           1 |
|    RAJ |          2 |          0 |           1 |
| SACHIN |          1 |          2 |           1 |

我试过 UNION 是这样的:

SELECT NAME, COUNT(*) AS MANGOCOUNT FROM FRUIT_EAT 
 WHERE FRUIT='MANGO'  GROUP BY NAME 
UNION ALL 
SELECT NAME, COUNT(*) AS APPLECOUNT FROM FRUIT_EAT 
 WHERE FRUIT='APPLE' GROUP BY NAME 
UNION ALL 
SELECT NAME, COUNT(*) AS BANANACOUNT FROM FRUIT_EAT 
 WHERE FRUIT='BANANA' GROUP BY NAME;

我得到的结果是

NAME        MANGOCOUNT
AJAY           3
RAJ            2
SACHIN         1
AJAY           3
RAJ            0
SACHIN         2
AJAY           1
RAJ            1
SACHIN         1

我不明白我的错误。如果你能帮帮我好吗？

Answer 1

你不需要为此使用 UNION。您可以使用 SUM() 函数和 CASE 语句来执行此操作，如下所示:

SELECT Name
      ,SUM(CASE WHEN Fruit = 'Mango' THEN 1 ELSE 0 END) AS MangoCount
      ,SUM(CASE WHEN Fruit = 'Apple' THEN 1 ELSE 0 END) AS AppleCount
      ,SUM(CASE WHEN Fruit = 'Banana' THEN 1 ELSE 0 END) AS BananaCount
  FROM MyTable
 GROUP BY Name;

您还可以使用此动态查询:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN `Fruit` = ''',
      `Fruit`,
      ''' THEN 1 ELSE 0 END) AS `',
      `Fruit`, '`'
    )
  ) INTO @sql
FROM MyTable;

SET @sql = CONCAT('SELECT Name, ', @sql,'
                     FROM MyTable
                    GROUP BY Name
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

输出:

|   NAME | MANGOCOUNT | APPLECOUNT | BANANACOUNT |
|--------|------------|------------|-------------|
|   AJAY |          3 |          3 |           1 |
|    RAJ |          2 |          0 |           1 |
| SACHIN |          1 |          2 |           1 |

参见this SQLFiddle