mysql - 将返回值显示为sql中的列名

Question

我正在尝试在 mysql 中运行查询以返回所有产品及其规范。我设法进行如下查询:

select `brands`.`name`,`products`.`reference`,`specifications`.`name`,`specs-product`.`value` 

from `specs-product` 

inner join `products` on `specs-product`.`product-id`=`products`.`product-id`
inner join `specifications` on `specs-product`.`specification-id`=`specifications`.`specification-id`  
inner join `brands` on `products`.`brand-id`=`brands`.`brand-id`

where 

`specs-product`.`product-id` in (1,2,3,4,5,6,7,8)

and

(
`specs-product`.`specification-id`='88' or 
`specs-product`.`specification-id`='103' or 
`specs-product`.`specification-id`='18' or 
`specs-product`.`specification-id`='15' or 
`specs-product`.`specification-id`='157' or 
`specs-product`.`specification-id`='89' or 
`specs-product`.`specification-id`='9' or 
`specs-product`.`specification-id`='223' or 
`specs-product`.`specification-id`='224' or 
`specs-product`.`specification-id`='29' or 
`specs-product`.`specification-id`='87' or 
`specs-product`.`specification-id`='219' or 
`specs-product`.`specification-id`='218' or 
`specs-product`.`specification-id`='220'
);

它返回所有值并且工作正常。但我试图显示产品的所有规范都显示在一行中的值。因此实际上返回的结果只有 8 行。

我怎样才能实现这个目标？

Answer 1

试试这个:

SELECT
  b.name AS brand,
  p.reference AS reference,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 88
      AND `product_id` = p.`product_id`
  ) AS weight,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 103
      AND `product_id` = p.`product_id`
  ) AS toneryield,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 18
      AND `product_id` = p.`product_id`
  ) AS stdpapercapacity,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 15
      AND `product_id` = p.`product_id`
  ) AS speed,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 157
      AND `product_id` = p.`product_id`
  ) AS resolution,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 89
      AND `product_id` = p.`product_id`
  ) AS powerreqs,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 9
      AND `product_id` = p.`product_id`
  ) AS maxmonthdutycycle,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 223
      AND `product_id` = p.`product_id`
  ) AS lowprice,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 224
      AND `product_id` = p.`product_id`
  ) AS highprice,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 29
      AND `product_id` = p.`product_id`
  ) AS documentfeeder,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 87
      AND `product_id` = p.`product_id`
  ) AS dimensions,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 219
      AND `product_id` = p.`product_id`
  ) AS colorimpressions,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 218
      AND `product_id` = p.`product_id`
  ) AS blackimpressions,
  (
    SELECT value
    FROM `specs-product`
    WHERE `specification_id` = 220
      AND `product_id` = p.`product_id`
  ) AS blacktonerinsqrfeet
FROM products p
INNER JOIN brands b ON b.`brand_id` = p.`brand_id`