数据透视表仅返回1行 [英] Pivoting table only returns 1 row

问题描述

我的SQLFiddle: http://sqlfiddle.com/#!2/729a9/1

My SQLFiddle: http://sqlfiddle.com/#!2/729a9/1

如您所见，尽管表中有两行，但仍返回了一行.

As you can see, despite there being two rows in the table, there is one row returned.

它也是最高的ID，也许与它有关?

It's also the highest id, so maybe that has something to do with it?

抱歉，我很沮丧.

SQL:

SELECT GROUP_CONCAT(distinct
             CONCAT(
              'max(case when `pat`.`name` = ''',
               `pat`.name,
               ''' then `pa`.`value` end) as `',
               `pat`.name, '`'
              )
            ) INTO @list 
FROM `product_attribute_types` pat;

SET @sql = CONCAT('select ', @list, ' 
                  from `products`  `p` 
                  LEFT JOIN `product_attributes` `pa` 
                    ON `p`.id=`pa`.`product_id`
                  LEFT JOIN `product_attribute_types` `pat`
                        ON `pa`.`type`=`pat`.`id`
                  ');

PREPARE stmt FROM @sql;
EXECUTE stmt;

推荐答案

首先:对于同一个product_id = 1，您具有两个属性，以这种方式更改表product_attributes-

Firstly: you have had two attributes for the same product_id = 1, change table product_attributes in this way -

INSERT INTO `product_attributes` (`product_id`,`type`,`value`) VALUES
  (1,1,'blue'),
  (1,2,'shirt'),
  (2,1,'green'),
  (2,2,'pants');

然后尝试这个-

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pat.name = ''', name, ''', pa.value, NULL)) AS ', name
    )
  ) INTO @sql
FROM product_attribute_types;

SET @sql = CONCAT('SELECT pa.product_id, ', @sql, ' FROM product_attributes pa INNER JOIN product_attribute_types pat ON pa.type = pat.id GROUP BY pa.product_id');

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

结果:

+------------+-------+-------+
| product_id | color | name  |
+------------+-------+-------+
|          1 | blue  | shirt |
|          2 | green | pants |
+------------+-------+-------+

如果需要，添加WHERE过滤器.

Add WHERE filter if needed.

这篇关于数据透视表仅返回1行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！