MySQL Pivot或Excel解决方案 [英] MySQL Pivot or Excel Solution

问题描述

我有一张桌子:

CREATE TABLE `Issues` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

我还有一张桌子:

CREATE TABLE `Attachments` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `issue_id` int(11) DEFAULT NULL,
  `attachment` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

如何使数据看起来像这样:

How can I get the data to look like this:

issue_id    title    attachment1    attachment2    attachment3
--------------------------------------------------------------
1           T1       a1.png         a2.png
2           T2
3           T3       b4.gif         xyz.doc        ttt.file

我不知道的问题是如何将动态附件集放入按问题分组的动态列中.我确定一个问题的最大附件数量为12，但是每张票证的总数可以为0-12.我很困惑...

The problem I can't figure out is how to get the dynamic set of attachments into a dynamic column grouped by issue. I have determined that the maximum amount of attachments for one issue is 12, but the total per ticket can be anywhere from 0-12. I'm stumped...

我已经尝试过了 MySQL将行透视成动态列数，但由于我正在构建动态列，因此在我的情况下无法理解根据每条记录的总匹配次数...

I've tried this MySQL pivot row into dynamic number of columns, but can't make sense of it in my case because I'm building dynamic columns based on total matches per record...

任何帮助将不胜感激.请让我知道这是否没有道理.

Any help would be greatly appreciate. Please let me know if this doesn't make sense.

尼诺

推荐答案

如果知道最大数量为12，这是完成此任务的一种方法.它使用MAX和CASE，获取每个附件的行号.

Here is one way to accomplish this if you know the max number is 12. It uses MAX and CASE, getting the Row Number for each attachment.

SELECT 
    I.Id issue_id, 
    I.title, 
    MAX(CASE WHEN d.row_number = 1 THEN D.attachment END) attachment1,
    MAX(CASE WHEN d.row_number = 2 THEN D.attachment END) attachment2,
    MAX(CASE WHEN d.row_number = 3 THEN D.attachment END) attachment3,
    MAX(CASE WHEN d.row_number = 4 THEN D.attachment END) attachment4,
    MAX(CASE WHEN d.row_number = 5 THEN D.attachment END) attachment5,
    MAX(CASE WHEN d.row_number = 6 THEN D.attachment END) attachment6,
    MAX(CASE WHEN d.row_number = 7 THEN D.attachment END) attachment7,
    MAX(CASE WHEN d.row_number = 8 THEN D.attachment END) attachment8,
    MAX(CASE WHEN d.row_number = 9 THEN D.attachment END) attachment9,
    MAX(CASE WHEN d.row_number = 10 THEN D.attachment END) attachment10,
    MAX(CASE WHEN d.row_number = 11 THEN D.attachment END) attachment11,
    MAX(CASE WHEN d.row_number = 12 THEN D.attachment END) attachment12
FROM Issues I
  LEFT JOIN (
  SELECT 
      a.issue_id, 
      @running:=if(@previous=a.issue_id,@running,0) + 1 as row_number,
      @previous:=a.issue_id,
      a.attachment
  FROM Attachments a
      JOIN    (SELECT @previous := 0) r
    ORDER BY a.issue_id, a.attachment
  ) D ON I.ID = D.issue_id
GROUP BY I.Id, I.Title

这是 SQL小提琴.

我必须进行编辑以使每个组的行号重置.应该现在工作.另外，根据@ spencer7593的精彩评论，我对查询进行了小幅更新.

I had to make an edit to make the rownumber reset per group. Should be working now. Also, per @spencer7593's great comment, I've updated the query slightly.

-编辑-

针对OP关于需要动态结果的评论，这应该可行:

In response to OP's comment about needing dynamic results, this should work:

SET @sql = NULL;

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(d.row_number = ', d.row_number, ',D.attachment,NULL)) AS attachment', d.row_number)
  ) INTO @sql
FROM Issues I
  LEFT JOIN (
  SELECT 
      a.issue_id, 
      @running:=if(@previous=a.issue_id,@running,0) + 1 as row_number,
      @previous:=a.issue_id,
      a.attachment
  FROM Attachments a
      JOIN    (SELECT @previous := 0) r
    ORDER BY a.issue_id, a.attachment
  ) D ON I.ID = D.issue_id
;

SET @sql = CONCAT('SELECT I.Id issue_id, 
                          I.title, ', @sql, ' 
                  FROM Issues I
                  LEFT JOIN (
                  SELECT 
                      a.issue_id, 
                      @running:=if(@previous=a.issue_id,@running,0) + 1 as row_number,
                      @previous:=a.issue_id,
                      a.attachment
                  FROM Attachments a
                      JOIN    (SELECT @previous := 0) r
                    ORDER BY a.issue_id, a.attachment
                  ) D ON I.ID = D.issue_id
                GROUP BY I.Id, I.Title');

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

这是 SQL小提琴.

这篇关于MySQL Pivot或Excel解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！