多个计数和一个分组 [英] Multiple counts and a group by

问题描述

我有一个与此数据库类似的数据库(MySQL 5.5)

I have a database (MySQL 5.5) similar to this:

ID    Name   Page Visited   Date
1     Tim     Page A         11-2-2000
1     Tim     Page B         11-3-2000
1     Tim     Page B         11-3-2000
2     Jeff    Page C         11-5-2000
2     Jeff    Page A         11-11-2000

我想建立一个查询(目前尝试)，结果将与此类似:

I want to build a query (trying to at the moment), where the results would be similar to this:

ID    Name    Page A Visits  Page B Visits  Page C Visits
1     Tim          1             2               0

我假设我需要针对一个子集运行以下查询(我的问题是我该如何使用3个计数来执行此操作)?:

I assume that I need to run the following query against a subset (my question is how do I do this with essentially 3 counts)?:

SELECT * From database.mytable GROUP BY ID HAVING COUNT(*) >=1

推荐答案

SELECT ID, Name,
        SUM(CASE WHEN `Page Visited` = 'Page A' THEN 1 ELSE 0 END) `Page A Visit`,
        SUM(CASE WHEN `Page Visited` = 'Page B' THEN 1 ELSE 0 END) `Page B Visit`,
        SUM(CASE WHEN `Page Visited` = 'Page C' THEN 1 ELSE 0 END) `Page C Visit`
FROM tableName
GROUP BY ID, Name

• SQLFiddle演示
• SQLFiddle Demo

如果您不知道page的数量，也可以PreparedStatement

if you have unknown number of page, you can also PreparedStatement

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN `Page Visited` =''',
      `Page Visited`,
      ''' then 1 ELSE 0 end) AS ',
      CONCAT('`',`Page Visited`, ' Visits`')
    )
  ) INTO @sql
FROM TableName;

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

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

• SQLFiddle演示
• SQLFiddle Demo

这篇关于多个计数和一个分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！