时间戳

首页 > 解决方案 > 从连接表中获取列作为行而不是相邻列

sql - 从连接表中获取列作为行而不是相邻列

问题描述

我有三个表 A_PRODUCT、A_MODEL 和 A_AREA。可以使用 PRODUCT_ID 连接这些表。我想得到一个输出，使得 A_PRODUCT 表中的一行后面是 A_MODEL 表中的行，然后是 A_AREA 表中的行。以下是创建示例数据的 SQL：

CREATE TABLE A_PRODUCT
(
PRODUCT_ID NVARCHAR(30),
PODUCT_NAME NVARCHAR(30),
);

INSERT INTO A_PRODUCT (PRODUCT_ID, PODUCT_NAME)
VALUES ('P-1', 'LONG-SLEEVE LOGO JERSEY');

INSERT INTO A_PRODUCT (PRODUCT_ID, PODUCT_NAME)
VALUES ('P-2', 'GLASSES');


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

INSERT INTO A_MODEL (MODEL_ID, MODEL_NAME, PRODUCT_ID)
VALUES ('M-1', 'OLD JERSEY', 'P-1');

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

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

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|LONG-SLEEVE LOGO JERSEY|
|MODEL|M-1|OLD JERSEY|
|AREA|A-1|SOUTH|
|AREA|A-2|NORTH|
|AREA|A-3|EAST|
|AREA|A-4|WEST|
|PRODUCT|P-2|GLASSES|
|MODEL|M-2|RAY-BAN|
|MODEL|M-3|ARMANI|
|AREA|A-5|SOUTH-WEST|

我已经尝试了以下查询，但它没有给我想要的输出：

;WITH CTE AS 
(
SELECT CONCAT('|PRODUCT|', P.PRODUCT_ID,'|',P.PODUCT_NAME,'|') AS Col1,
        CONCAT('|MODEL|', M.MODEL_ID,'|',M.MODEL_NAME,'|') AS COl2,
        CONCAT('|AREA|', A.AREA_ID,'|',A.AREA_NAME,'|') AS COl3      
    FROM A_PRODUCT P
    JOIN A_MODEL M
    ON P.PRODUCT_ID = M.PRODUCT_ID
    JOIN A_AREA A
    ON P.PRODUCT_ID = A.PRODUCT_ID
 )
 SELECT Result
 FROM CTE
 CROSS APPLY (VALUES (Col1,'Col1'),(Col2,'Col2'),(Col3,'Col3')
              )DT (Result,Names);

标签： sqlsql-serverjoin

解决方案

你真的不想来JOIN这里。您需要使用 对PRODUCT_IDa 的结果进行排序UNION，并且需要根据信息来自的表强制进行额外的排序。

使用子查询以您需要的方式对数据进行分组，并使用外部查询SELECT来获取导出数据所需的格式。

SELECT
  CONCAT('|',d.DetailCol1,'|',d.DetailCol2,'|', d.DetailCol3,'|') AS DelimitedList
FROM
  (
    SELECT
      PRODUCT_ID AS SortCol1,
      1 AS SortCol2,
      'PRODUCT' AS DetailCol1,
      PRODUCT_ID AS DetailCol2,
      PRODUCT_NAME AS DetailCol3
    FROM @A_PRODUCT
    UNION ALL
    SELECT
      PRODUCT_ID AS SortCol1,
      2 AS SortCol2,
      'MODEL' AS DetailCol1,
      MODEL_ID AS DetailCol2,
      MODEL_NAME AS DetailCol3
    FROM @A_MODEL
    UNION ALL
    SELECT
      PRODUCT_ID AS SortCol1,
      3 AS SortCol2,
      'AREA' AS DetailCol1,
      AREA_ID AS DetailCol2,
      AREA_NAME AS DetailCol3
    FROM @A_AREA
  ) AS d
ORDER BY 
  d.SortCol1,
  d.SortCol2;

+---------------------------------------+
|             DelimitedList             |
+---------------------------------------+
| |PRODUCT|P-1|LONG-SLEEVE LOGO JERSEY| |
| |MODEL|M-1|OLD JERSEY|                |
| |AREA|A-1|SOUTH|                      |
| |AREA|A-2|NORTH|                      |
| |AREA|A-3|EAST|                       |
| |AREA|A-4|WEST|                       |
| |PRODUCT|P-2|GLASSES|                 |
| |MODEL|M-2|RAY-BAN|                   |
| |MODEL|M-3|ARMANI|                    |
| |AREA|A-5|SOUTH-WEST|                 |
+---------------------------------------+

推荐阅读