时间戳

首页 > 解决方案 > 如何在行而不是列中获取连接表的相邻列？

sql - 如何在行而不是列中获取连接表的相邻列？

问题描述

我有三个表 A_PRODUCT、A_MODEL 和 A_AREA。可以使用 PRODUCT_ID 连接这些表。它们有不同的列数，即 A_PRODUCT 有 6 列，A_MODEL 有 4 列，A_AREA 有 3 列。

我想得到一个输出，使得 A_PRODUCT 表中的一行后面是 A_MODEL 表中的行，然后是 A_AREA 表中的行。

请注意，一个产品可以有多个版本。例如：产品“P-1”有两个版本“V-1”和“V-2”。

以下是创建示例数据的 SQL 语句：

CREATE TABLE A_PRODUCT
(
PRODUCT_ID NVARCHAR(30),
PRODUCT_VID NVARCHAR(30),
PRODUCT_NAME NVARCHAR(30),
PRODUCT_NICK_NAME NVARCHAR(30),
PRODUCT_DESC NVARCHAR(30),
PRODUCT_CATEGORY NVARCHAR(30)
);

INSERT INTO A_PRODUCT (PRODUCT_ID, PRODUCT_VID, PRODUCT_NAME, PRODUCT_NICK_NAME, PRODUCT_DESC, PRODUCT_CATEGORY)
VALUES ('P-1', 'V-1', 'LONG-SLEEVE LOGO JERSEY', 'JERSEY', 'SLEEVE JERSEY', 'SHIRT');

INSERT INTO A_PRODUCT (PRODUCT_ID, PRODUCT_VID, PRODUCT_NAME, PRODUCT_NICK_NAME, PRODUCT_DESC, PRODUCT_CATEGORY)
VALUES ('P-1', 'V-2', 'LONG-SLEEVE LOGO JERSEY', 'JERSEY', 'SLEEVE JERSEY', 'SHIRT');

INSERT INTO A_PRODUCT (PRODUCT_ID, PRODUCT_VID, PRODUCT_NAME, PRODUCT_NICK_NAME, PRODUCT_DESC, PRODUCT_CATEGORY)
VALUES ('P-2', 'V-1', 'RAY-BAN SUN GLASSES', 'RAY-BAN', 'RAY-BAN SUN GLASSES', 'GLASSES');

-- DROP TABLE A_MODEL;
CREATE TABLE A_MODEL
(
MODEL_ID NVARCHAR(30),
MODEL_NAME NVARCHAR(30),
PRODUCT_ID NVARCHAR(30),
MODEL_YEAR NVARCHAR(30)
);

INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID, MODEL_YEAR)
VALUES ('M-1', 'REEBOK', 'P-1', '2003');

INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID, MODEL_YEAR)
VALUES ('M-2', 'RAY-BAN', 'P-2', '2004');

INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID, MODEL_YEAR)
VALUES ('M-3', 'ARMANI', 'P-2', '2007');

CREATE TABLE A_AREA
(
AREA_ID NVARCHAR(30),
AREA_NAME NVARCHAR(30),
PRODUCT_ID NVARCHAR(30)
);

INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
VALUES ('A-1', 'SOUTH', 'P-1');

INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
VALUES ('A-2', 'NORTH', 'P-1');

INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
VALUES ('A-3', 'EAST', 'P-1');

INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
VALUES ('A-4', 'WEST', 'P-1');

INSERT INTO A_AREA (AREA_ID, AREA_NAME, PRODUCT_ID)
VALUES ('A-5', 'SOUTH-WEST', 'P-2');

预期的输出是这样的：

|PRODUCT|P-1|V-1|LONG-SLEEVE LOGO JERSEY|JERSEY|SLEEVE JERSEY|SHIRT|
|MODEL|M-1|REEBOK|2003|
|AREA|A-1|SOUTH|
|AREA|A-2|NORTH|
|AREA|A-3|EAST|
|AREA|A-4|WEST|
|PRODUCT|P-1|V-2|LONG-SLEEVE LOGO JERSEY|JERSEY|SLEEVE JERSEY|SHIRT|
|MODEL|M-1|REEBOK|2003|
|AREA|A-1|SOUTH|
|AREA|A-2|NORTH|
|AREA|A-3|EAST|
|AREA|A-4|WEST|
|PRODUCT|P-2|V-1|RAY-BAN SUN GLASSES|RAY-BAN|RAY-BAN SUN GLASSES|GLASSES|
|MODEL|M-2|RAY-BAN|2004|
|MODEL|M-3|ARMANI|2007|
|AREA|A-5|SOUTH-WEST|

如您所见，输出是产品版本的完整快照。产品的快照可以按任何顺序出现。例如以下输出是有效的：

|PRODUCT|P-1|V-2|LONG-SLEEVE LOGO JERSEY|JERSEY|SLEEVE JERSEY|SHIRT|
|MODEL|M-1|REEBOK|2003|
|AREA|A-1|SOUTH|
|AREA|A-2|NORTH|
|AREA|A-3|EAST|
|AREA|A-4|WEST|
|PRODUCT|P-1|V-1|LONG-SLEEVE LOGO JERSEY|JERSEY|SLEEVE JERSEY|SHIRT|
|MODEL|M-1|REEBOK|2003|
|AREA|A-1|SOUTH|
|AREA|A-2|NORTH|
|AREA|A-3|EAST|
|AREA|A-4|WEST|
|PRODUCT|P-2|V-1|RAY-BAN SUN GLASSES|RAY-BAN|RAY-BAN SUN GLASSES|GLASSES|
|MODEL|M-2|RAY-BAN|2004|
|MODEL|M-3|ARMANI|2007|
|AREA|A-5|SOUTH-WEST|

标签： sqlsql-server

解决方案

这并未针对性能进行完全优化，但应该很容易理解：

WITH CTE
AS (
    SELECT
        ap.PRODUCT_ID
       ,ap.PRODUCT_VID
       ,ap.PRODUCT_NAME
       ,ap.PRODUCT_NICK_NAME
       ,ap.PRODUCT_DESC
       ,ap.PRODUCT_CATEGORY
       ,am.MODEL_NAME
       ,am.MODEL_ID
       ,am.MODEL_YEAR
       ,aa.AREA_ID
       ,aa.AREA_NAME
    FROM dbo.A_PRODUCT ap
    INNER JOIN A_MODEL am
        ON ap.PRODUCT_ID = am.PRODUCT_ID
    INNER JOIN A_AREA aa
        ON ap.PRODUCT_ID = aa.PRODUCT_ID
),
CTE_P
AS (
    SELECT DISTINCT
        c.PRODUCT_ID
       ,c.PRODUCT_VID
       ,'1'                 AS [ORDER_ID]
       ,'PRODUCT'           AS [COL1]
       ,c.PRODUCT_ID        AS [COL2]
       ,c.PRODUCT_VID       AS [COL3]
       ,c.PRODUCT_NAME      AS [COL4]
       ,c.PRODUCT_NICK_NAME AS [COL5]
       ,c.PRODUCT_DESC      AS [COL6]
       ,c.PRODUCT_CATEGORY  AS [COL7]
    FROM CTE c
),
CTE_MODEL
AS (
    SELECT DISTINCT
        c.PRODUCT_ID
       ,c.PRODUCT_VID
       ,'2'             AS [ORDER_ID]
       ,'MODEL'         AS [COL1]
       ,c.MODEL_ID      AS [COL2]
       ,c.MODEL_NAME    AS [COL3]
       ,c.MODEL_YEAR    AS [COL4]
       ,NULL            AS [COL5]
       ,NULL            AS [COL6]
       ,NULL            AS [COL7]
    FROM CTE c
),
CTE_AREA
AS (
    SELECT DISTINCT
        PRODUCT_ID
       ,PRODUCT_VID
       ,'3'       AS [ORDER_ID]
       ,'AREA'    AS [COL1]
       ,AREA_ID   AS [COL2]
       ,AREA_NAME AS [COL3]
       ,NULL      AS [COL4]
       ,NULL      AS [COL5]
       ,NULL      AS [COL6]
       ,NULL      AS [COL7]
    FROM CTE
)

SELECT
       -- t.COL1
       --,t.COL2
       --,t.COL3
       --,t.COL4
       --,t.COL5
       --,t.COL6
       --,t.COL7,
        ISNULL('|' + t.COL1 + '|', '')  + 
        ISNULL(t.COL2 + '|', '')  + 
        ISNULL(t.COL3 + '|', '')  + 
        ISNULL(t.COL4 + '|', '')  + 
        ISNULL(t.COL5 + '|', '')  + 
        ISNULL(t.COL6 + '|', '')  + 
        ISNULL(t.COL7 + '|', '')  AS Result
    FROM (
        SELECT * FROM CTE_P UNION
        SELECT * FROM CTE_MODEL UNION
        SELECT * FROM CTE_AREA
    ) t
    ORDER BY
        PRODUCT_ID
       ,PRODUCT_VID
       ,ORDER_ID
       ,COL2

推荐阅读