MySQL select从一列中获取2个值并返回一行

这是非常容易解决的排名函数,但MySQL还不支持他们。相反,可以模拟列组:

Select P.product_name
    , Min( Case When P.ProductRank = 0 Then product_thumb_image End ) As Image0
    , Min( Case When P.ProductRank = 1 Then product_thumb_image End ) As Image1
From    (
        Select product_id, product_name, product_thumb_image
            , (Select Count(*)
                From Products As P2
                Where P2.product_name = P1.product_name
                    And P2.product_id < P1.product_Id) As ProductRank
        From Products As P1
        ) As P
Group By P.product_name

下面是编写同一查询的另一种方法,它可能会执行得更好:

Select P.product_name
    , Min( Case When P.ProductRank = 0 Then product_thumb_image End ) As Image0
    , Min( Case When P.ProductRank = 1 Then product_thumb_image End ) As Image1
From    (
        Select P1.product_id, P1.product_name, P1.product_thumb_image
            , Count(P2.product_id) As ProductRank
        From Products As P1
            Left Join Products As P2
                On P2.product_name = P1.product_name
                    And P2.product_id < P1.product_Id
        Group By P1.product_id, P1.product_name, P1.product_thumb_image
        ) As P
Group By P.product_name

你需要两个子选区。一张是第一张,一张是第二张:

SELECT t1.product_name, t2.product_thumb_image, t3.product_thumb_image
FROM mytable t1
LEFT OUTER JOIN (SELECT x.product_thumb_image
                 FROM mytable x
                 WHERE x.product_name = t1.product_name
                 LIMIT 0,1) t2
LEFT OUTER JOIN (SELECT x.product_thumb_image
                 FROM mytable x
                 WHERE x.product_name = t1.product_name
                 LIMIT 1,1) t3
GROUP BY t1.product_name

由于每个产品的图像数量可变,编写MySQL来返回所需的列将非常困难,而且相当笨拙。相反,为什么不运行一个简单的 GROUP_CONCAT 返回逗号分隔的列表,然后在应用程序中拆分结果。在返回文件名列表时,逗号分隔符不太可能出现在文件名中:

SELECT
  `product_name`,
  GROUP_CONCAT(`product_thumb_image`) AS product_thumb_images
FROM `product`
GROUP BY `product_name`;

+--------------+------------------------------------+
| product_name | product_thumb_images               |
+--------------+------------------------------------+
| ProductA     | images/img_A.jpg                   |
| ProductB     | images/img_B.jpg,images/img_B2.jpg |
| ProductC     | images/img_C.jpg                   |
| ProductD     | images/img_C.jpg                   |
+--------------+------------------------------------+