仅当选定列表A匹配表B时Mysql组concat

Question

我有 2 张 table 。

table 胎

+------------------------------+----------------------------------------------------------------+--------+-------+------+
| sku                          | tyrename                                                       | width  | ratio | size |
+------------------------------+----------------------------------------------------------------+--------+-------+------+
| 2454019RE003(98W)            | 245/40 R19 98W BRIDGESTONE Potenza RE003                       | 245.00 | 40.00 |   19 |
| 2753519RE003(100W)XL         | 275/35 R19 100W BRIDGESTONE Potenza RE003 XL                   | 275.00 | 35.00 |   19 |
| 2454019CSC5P(98Y)XL(EU)      | 245/40 R19 98Y CONTINENTAL ContiSportContact 5P XL (EU)        | 245.00 | 40.00 |   19 |
| 2454518CSC3(96W)(EU)         | 245/45 R18 96W CONTINENTAL ContiSportContact3 (EU)             | 245.00 | 45.00 |   18 |
| 2454518CSC3(96Y)ESSR(EU)     | 245/45 R18 96Y CONTINENTAL ContiSportContact3 E SSR (EU)       | 245.00 | 45.00 |   18 |
+------------------------------+----------------------------------------------------------------+--------+-------+------+

和桌面车

+------+------+---------------+-------------------------------+---------+---------+---------+---------+---------+---------+
| make | year | modeltrim     | options                       | tyre1_1 | tyre1_2 | tyre1_3 | tyre2_1 | tyre2_2 | tyre2_3 |
+------+------+---------------+-------------------------------+---------+---------+---------+---------+---------+---------+
| BMW  | 2014 | 5 Series 535i | OE Fitment 01 (18")           |  245.00 |   45.00 |   18.00 |    0.00 |    0.00 |    0.00 |
| BMW  | 2014 | 5 Series 535i | OE Fitment 02 (18")           |  245.00 |   50.00 |   18.00 |    0.00 |    0.00 |    0.00 |
| BMW  | 2014 | 5 Series 535i | OE Fitment 03 (17")           |  225.00 |   55.00 |   17.00 |    0.00 |    0.00 |    0.00 |
| BMW  | 2014 | 5 Series 535i | OE Fitment 04 Staggered (19") |  245.00 |   40.00 |   19.00 |  275.00 |   35.00 |   19.00 |
| BMW  | 2014 | 5 Series 535i | Option 01 Staggered (19")     |  245.00 |   40.00 |   19.00 |  265.00 |   45.00 |   19.00 |
+------+------+---------------+-------------------------------+---------+---------+---------+---------+---------+---------+

我想将 sku 分组为 1 列以生成如下表，使用轮胎宽度、比率和尺寸连接 2 个表。

+------+------+---------------+-------------------------------+-----------------------------------------------------------------+
| make | year | modeltrim     | options                       | sku                                                             |
+------+------+---------------+-------------------------------+-----------------------------------------------------------------+
| BMW  | 2014 | 5 Series 535i | OE Fitment 01 (18")           |  2454518CSC3(96W)(EU),2454518CSC3(96Y)ESSR(EU)                  |
| BMW  | 2014 | 5 Series 535i | OE Fitment 04 Staggered (19") |  2454019RE003(98W),2454019CSC5P(98Y)XL(EU),2753519RE003(100W)XL |
+------+------+---------------+-------------------------------+-------------------------------------------------+---------------+

某些选项有 2 个轮胎尺寸，仅当两个尺寸与轮胎宽度、比率尺寸匹配时才显示该行；如果只有 1 个尺寸与轮胎表匹配，则不显示该行。

目前我正在使用下面的 SQL 查询，但它显示即使只有 1 个轮胎尺寸的行也与具有 2 个轮胎尺寸的选项匹配。对于上面的示例表，即使轮胎表中没有轮胎尺寸 265/45/19，下面的查询也将显示“Option 01 Staggered (19")”行。

SELECT unionTable.make, unionTable.year, unionTable.modeltrim, unionTable.options, group_concat(unionTable.sku order by unionTable.sku)
FROM
(SELECT
    a.make, a.modeltrim, a.year, a.options, b.sku
FROM
    vehicle a
        JOIN
    tyre b
WHERE
    a.tyre1_1 = b.width AND a.tyre1_2 = b.ratio
        AND a.tyre1_3 = b.size
UNION ALL
SELECT
    a.make, a.modeltrim, a.year, a.options, b.sku
FROM
    vehicle a
        JOIN
    tyre b
WHERE
    a.tyre2_1 = b.width AND a.tyre2_2 = b.ratio
        AND a.tyre2_3 = b.size) AS unionTable
group by unionTable.make, unionTable.modeltrim, unionTable.year, unionTable.options
order by unionTable.make, unionTable.modeltrim, unionTable.year, unionTable.options, unionTable.sku

谢谢。

Answer 1

您可以使用内部联接和group_concat

select  a.make, a.year,  a.modeltrim, a.options , group_concat(b.sku)
from  vehicle as a
inner join tyre as b on ( b.tyre1_1 = a.width AND b.tyre1_2 = a.ratio
    AND b.tyre1_3 = a.size)
gruop by a.make, a.year,  a.modeltrim, a.options
UNION     
select  a.make, a.year,  a.modeltrim, a.options , group_concat(b.sku)
from  vehicle as a
inner join tyre as b on ( b.tyre2_1 = a.width AND b.tyre2_2 = a.ratio AND b.tyre2_3 = a.size) )
gruop by a.make, a.year,  a.modeltrim, a.options