主页 / dba / 问题 / 323130
Accepted
lifeisajourney
Asked: 2023-02-07 03:50:00 +0800 CST

编写查询以在 SQL Server 2012 中获取分层数据

  • 772

我有三个表：：product, users, and orders

product所有产品及其状态的列表。该表有一个名为 的字段previousProductId。产品的代码可以更改多次，如果是，则将previousProductId包含它的直接前身代码。例如，如果产品的代码为 1057，然后为该产品添加新代码 1060 的新行，则 previousLineId 的代码将为 1057。每次为新代码添加新行时都会重复此操作现有产品。
users: list of all users
orders: 列表所有用户下的订单

现在我必须编写一个查询来获取所有包含活动产品的订单。我必须确保所有订购活动产品以前版本的用户也包含在结果集中。我想使用递归 CTE 沿着链向上检索给定产品的所有先前产品 ID 并以这种方式检索必要的信息，但该方法似乎有点过于庞大。我想知道是否有更好更简单的方法。谢谢你！

Create table #product
    (
    productId Int primary key Identity(1,1),
    name varchar(100),
    price numeric(18,5),
    previousProductId Int,
    active bit
    )
    
    Create table #users
    (
    userId Int Primary key,
    name varchar(100)
    )
    
    Create table #orders
    (
    orderId Int primary key,
    userId Int,
    productId Int
    )
    
    Insert Into #product values ('Sony TV', 200.23, null, 0), ('Sony Tv New', 240.43, 1, 0), ('Apple Watch', 300.45, null, 1)
                                ,('Samsung Mobile',1050, null,0),('Sony TV Advanced', 400, 2,1)
    
    Insert Into #users values (1,'John'), (2,'Mary'), (3,'Kevin'), (4,'Joe'), (5,'Andy'),(6,'Jim'),(7,'Pam')
    
    Insert Into #orders values (1, 1, 1), (2, 2, 1),(3, 3,2), (4,4,3), (5,5,3),(6,6,4),(7,7,5)
    
    select t_p.productId, t_p.name, t_p.price, t_p.previousProductId, t_p.active
,t_u.name as username, t_o.orderId
    from #product t_p
    join #orders t_o
    on t_p.productId = t_o.productId 
    join #users t_u
    on t_u.userID = t_o.userId
    where t_p.active = 1
    
    drop table if exists #product
    drop table if exists #orders
    drop table if exists #users

预期结果： 在此处输入图像描述

sql-server

1 个回答

  1. Best Answer

    我不得不稍微清理一下你的 DDL/DML，因为它有一个语法错误：

    DECLARE @product TABLE (productId INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(100), price NUMERIC(18,5), previousProductId INT, active BIT)
DECLARE @users   TABLE (userId INT PRIMARY KEY, name VARCHAR(100))
DECLARE @orders  TABLE (orderId INT PRIMARY KEY, userId INT, productId INT)
    
INSERT INTO @product (name, price, previousProductId, active) VALUES 
('Sony TV', 200.23, null, 0),   ('Sony Tv New', 240.43, 1, 0), ('Apple Watch', 300.45, null, 1),
('Samsung Mobile',1050, null,0),('Sony TV Advanced', 400, 2,1)
INSERT INTO @users (userId, name) VALUES 
(1,'John'), (2,'Mary'), (3,'Kevin'), (4,'Joe'), (5,'Andy'), (6,'Jim'), (7,'Pam')
INSERT INTO @orders (orderId, userId, productID) VALUES 
(1, 1, 1), (2, 2, 1), (3, 3,2), (4,4,3), 
(5,5,3),   (6,6,4),   (7,7,5)

    为此，我更喜欢使用表变量，因为无需担心清理问题。

    关于你的问题。我不确定您定义的预期输出是否是您真正想要的，因为它没有引用基本 ID 或父 ID。它似乎也缺少 orderId 6。

    获取“基础”（最终父级）的 rCTE 技术可以这样写：

    ;WITH baseProduct AS (
SELECT productId AS baseID, productId
  FROM @product
 WHERE previousProductId IS NULL
 UNION ALL
SELECT a.baseID AS baseID, r.productId
  FROM @product r
    INNER JOIN baseProduct a
      ON r.previousProductId = a.productId
)

SELECT p.productId, p.name, p.price, p.previousProductId, p.active, u.name, o.orderId, bp.baseID
  FROM @orders o
    LEFT OUTER JOIN baseProduct bp
      ON o.productId = bp.productId
    INNER JOIN @product p
      ON o.productId = p.productId
    INNER JOIN @users u
      ON o.userId = u.userId
    产品编号 姓名 价格 上一个产品编号 积极的 姓名 订单号 基地编号
    1个 索尼电视 200.23000 0 约翰 1个 1个
    1个 索尼电视 200.23000 0 玛丽 2个 1个
    2个 索尼电视新品 240.43000 1个 0 凯文 3个 1个
    3个 苹果手表 300.45000 1个 乔 4个 3个
    3个 苹果手表 300.45000 1个 安迪 5个 3个
    4个 三星手机 1050.00000 0 吉姆 6个 4个
    5个 索尼电视高级版 400.00000 2个 1个 帕姆 7 1个

    我可能误解了你的意图，你实际上只想要 previousProductID（在 orderId 7 的情况下，2 而不是 1）如果是这种情况，你应该能够用 rCTE 的递归端a.baseID AS baseID替换a.productId AS baseID

    • 4

相关问题