主页 / dba / 问题 / 319397
Accepted
omerix
Asked: 2022-11-10 08:34:55 +0800 CST

非循环表连接

  • 772

我有 2 个表“订单”和“销售”。productCode 字段在该表中很常见。

我的目标是按如下方式并排“加入”2 个表。

在此处输入图像描述

我试过的

  1. partition by (row_number() over (partition by productCode order by id))
  2. 分组集
  3. 全外连接
  4. ...当前行和 1 个后续行..
CREATE TABLE [dbo].[orders](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [orderDate] [date] NULL,
    [productCode] [varchar](50) NULL,
    [qty] [float] NULL,
    [price] [float] NULL,
    [deadLine] [date] NULL,
 CONSTRAINT [PK_orders] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[sales](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [salesDate] [date] NULL,
    [productCode] [varchar](50) NULL,
    [qty] [float] NULL,
    [price] [float] NULL,
 CONSTRAINT [PK_sales] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT [dbo].[orders] ON 
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (1, CAST(N'2022-10-01' AS Date), N'a', 10, 15, CAST(N'2022-12-01' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (2, CAST(N'2022-10-02' AS Date), N'b', 20, 25, CAST(N'2023-01-01' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (3, CAST(N'2022-10-03' AS Date), N'c', 30, 35, CAST(N'2023-02-02' AS Date))
INSERT [dbo].[orders] ([id], [orderDate], [productCode], [qty], [price], [deadLine]) VALUES (4, CAST(N'2022-10-04' AS Date), N'd', 40, 45, CAST(N'2023-03-03' AS Date))
SET IDENTITY_INSERT [dbo].[orders] OFF


SET IDENTITY_INSERT [dbo].[sales] ON 
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (1, CAST(N'2022-11-01' AS Date), N'a', 3, 14)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (2, CAST(N'2022-11-02' AS Date), N'b', 4, 25)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (3, CAST(N'2022-11-03' AS Date), N'a', 1, 16)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (4, CAST(N'2022-11-04' AS Date), N'b', 3, 20)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (5, CAST(N'2022-11-05' AS Date), N'b', 8, 24)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (6, CAST(N'2022-11-06' AS Date), N'a', 5, 24)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (7, CAST(N'2022-11-07' AS Date), N'b', 41, 20)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (8, CAST(N'2022-11-08' AS Date), N'd', 10, 35)
INSERT [dbo].[sales] ([id], [salesDate], [productCode], [qty], [price]) VALUES (9, CAST(N'2022-11-09' AS Date), N'a', 4, 10)
SET IDENTITY_INSERT [dbo].[sales] OFF
ID 订购日期 产品代码 数量 价格 最后期限
1个 2022-10-01 一个 10 15 2022-12-01
2个 2022-10-02 b 20 25 2023-01-01
3个 2022-10-03 C 30 35 2023-02-02
4个 2022-10-04 d 40 45 2023-03-03
ID 销售日期 产品代码 数量 价格
1个 2022-11-01 一个 3个 14
2个 2022-11-02 b 4个 25
3个 2022-11-03 一个 1个 16
4个 2022-11-04 b 3个 20
5个 2022-11-05 b 8个 24
6个 2022-11-06 一个 5个 24
7 2022-11-07 b 41 20
8个 2022-11-08 d 10 35
9 2022-11-09 一个 4个 10

数据库小提琴

sql-server

1 个回答

  1. Best Answer

    所以你使用 a 已经完成了一半FULL JOIN，你只需要一个额外的步骤来消隐重复的行。您可以通过使用ROW_NUMBER()窗口函数枚举每组productCodes 中的行然后使用CASE语句或IFF()函数在它不是组中的第一行时清空字段来执行此操作，如下所示：

    WITH _SalesOrders AS
(
    SELECT 
        o.id AS OrderId,
        o.orderDate,
        o.productCode AS OrderProductCode,
        o.qty AS OrderQuantity,
        o.price AS OrderPrice,
        o.deadLine,
        s.id,
        s.salesDate,    
        s.productCode,
        s.qty,
        s.price,
        ROW_NUMBER() OVER (PARTITION BY o.productCode ORDER BY (SELECT 1)) AS RowId
    FROM orders o
    FULL OUTER JOIN sales s 
        ON o.productCode = s.productCode
)

SELECT
    IIF(RowId = 1, OrderId, '') AS id,
    IIF(RowId = 1, orderDate, '') AS orderDate,
    IIF(RowId = 1, OrderProductCode, '') AS productCode,
    IIF(RowId = 1, OrderQuantity, '') AS qty,
    IIF(RowId = 1, OrderPrice, '') AS price,
    IIF(RowId = 1, deadLine, '') AS deadline,
    id,
    salesDate,  
    productCode,
    qty,
    price
FROM _SalesOrders
ORDER BY productCode, RowId

    顺便说一下，注意窗口函数中的ORDER BY (SELECT 1)子句。这是一个不确定的排序子句。随意将其替换为您要用于定义RowIds 在每个分区中的顺序的字段productCode，或者如果您不关心顺序，则保留原样。

    • 1

相关问题