MySQL的，转置/枢轴行到列，变量选择 [英] mysql, transpose/pivot row to column, variable selects

问题描述

再次问好，谢谢您的帮助.

Hello again and thank you in advance for your help.

我已经检查了一些先前的问题，但找不到确切的情况.

I've checked a few prior questions and couldn't find this exact situation.

我试图将行转置/转为列，但是结果基于where子句中的日期函数，这使我的选择有些变化.

I'm trying to transpose/pivot a row to column, but the results are based on a date function in the where clause, making my selects somewhat variable.

SELECT
DATE_FORMAT(packet_details.installDate,'%m-%d-%Y') as Install_Date,
Count(packet_details.installDate)
FROM
packet_details
WHERE
 packet_details.installDate >= CURRENT_DATE - INTERVAL 7 DAY
 AND packet_details.installDate "*lessthan*" CURRENT_DATE + INTERVAL 7 DAY
GROUP BY installDate
*lessthan symbol wont show here on Stack & i don't know how to fix it

不确定这是否有意义，所以我加入了一个小提琴: http://sqlfiddle. com/#！2/5b235/3/0 像这样:

Not sure if that makes sense so I included a fiddle: http://sqlfiddle.com/#!2/5b235/3/0 So something like this:

INSTALL_DATE     COUNT
1/24/2013         2
1/25/2013         2
1/26/2013         2
1/27/2013         2
1/28/2013         2
1/29/2013         1
2/3/2013          1
2/4/2013          1
2/5/2013          5
2/6/2013          4

变成:

INSTALL_DATE    1/24/2013   1/25/2013   1/26/2013   1/27/2013   1/28/2013....   
COUNT             2             2          2             2         2               1     

推荐答案

SELECT  Install_DATE,
        MAX(CASE WHEN Install_DATE = '01-24-2013' THEN totalCount END) `01-24-2013`,
        MAX(CASE WHEN Install_DATE = '01-25-2013' THEN totalCount END) `01-25-2013`,
        MAX(CASE WHEN Install_DATE = '01-26-2013' THEN totalCount END) `01-26-2013`,
        .......
FROM
(
  SELECT DATE_FORMAT(packet_details.installDate,'%m-%d-%Y') as Install_Date,
         Count(packet_details.installDate) totalCount
  FROM   packet_details
  WHERE  packet_details.installDate >= CURRENT_DATE - INTERVAL 7 DAY AND 
         packet_details.installDate   < CURRENT_DATE + INTERVAL 7 DAY
  GROUP  BY installDate
) s

• SQLFiddle演示
• SQLFiddle Demo

对于未知数量的Install_Date，首选动态查询，

For unknown number of Install_Date, a Dynamic Query is much preferred,

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN Install_DATE = ''',
      Install_Date,
      ''' then totalCount end) AS `', Install_Date, '`' )
  ) INTO @sql
FROM 
(
  SELECT DATE_FORMAT(packet_details.installDate,'%m-%d-%Y') as Install_Date,
         Count(packet_details.installDate) totalCount
  FROM   packet_details
  WHERE  packet_details.installDate >= CURRENT_DATE - INTERVAL 7 DAY AND 
         packet_details.installDate   < CURRENT_DATE + INTERVAL 7 DAY
  GROUP  BY installDate
) s;

SET @sql = CONCAT('SELECT Install_DATE, ', @sql, ' 
                    FROM
                    (
                      SELECT DATE_FORMAT(packet_details.installDate,''%m-%d-%Y'') as Install_Date,
                             Count(packet_details.installDate) totalCount
                      FROM   packet_details
                      WHERE  packet_details.installDate >= CURRENT_DATE - INTERVAL 7 DAY AND 
                             packet_details.installDate   < CURRENT_DATE + INTERVAL 7 DAY
                      GROUP  BY installDate
                    ) s');

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

• SQLFiddle演示
• SQLFiddle Demo

这篇关于MySQL的，转置/枢轴行到列，变量选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！