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

Question

请有人帮我解决这个问题。我似乎无法理解我需要得到我想要的东西的 SQL 语句。下面是我的表格。

| product_id  | product_name |  product_thumb_image  | 
------------------------------------------------------
|     1       |   ProductA   |  images/img_A.jpg    |
|     2       |   ProductB   |  images/img_B.jpg    |
|     3       |   ProductB   |  images/img_B2.jpg   |
|     4       |   ProductC   |  images/img_C.jpg    |
|     5       |   ProductD   |  images/img_D.jpg    |

注意有 2 个 ProductB。

我想要一个 SQL 语句来选择“product_thumb_image”，以便它为每个产品名称返回一行，但是如果产品有第二张图片，那么第二列也会返回。

例如

| mages/img_A.jpg  |  NULL/0/''          |
| mages/img_B.jpg  |  images/img_B2.jpg  |
| mages/img_C.jpg  |  NULL/0/''          |
| mages/img_D.jpg  |  NULL/0/''          |

任何帮助将不胜感激，谢谢。

Answer 1

用排名函数解决这个问题要容易得多，但 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