我试图在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行。

我该如何实现?

最佳答案

试试这个:

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`

10-08 13:30
查看更多