Início / dba / Perguntas / 323130
Accepted
lifeisajourney
Asked: 2023-02-07 03:50:00 +0800 CST

Escreva uma consulta para obter dados hierárquicos no SQL Server 2012

  • 772

Tenho três tabelas: product, users, and orders

product: lista de todos os produtos e seus status. Esta tabela tem um campo chamado previousProductId. O código de um produto pode ser alterado várias vezes e, se for, previousProductIdconterá o código do predecessor imediato. Por exemplo, se um produto tem o código 1057 e então uma nova linha é adicionada para este produto com um novo código 1060 então o previousLineId terá um código 1057. Isso se repete toda vez que uma nova linha com um novo código é adicionada para um produto existente.
users: lista de todos os usuários
orders: lista de todos os pedidos feitos pelos usuários

Agora tenho que escrever uma consulta para obter todos os pedidos com produtos ativos. Preciso garantir que todos os usuários que solicitaram as versões anteriores de um produto ativo também sejam incluídos no conjunto de resultados. Quero usar o CTE recursivo para subir na cadeia para recuperar todos os IDs de produtos anteriores de um determinado produto e recuperar as informações necessárias dessa maneira, mas esse método parece um pouco volumoso. Eu queria saber se existe uma maneira melhor e mais simples de fazer isso. Obrigado!

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

Resultado esperado: insira a descrição da imagem aqui

sql-server

1 respostas

  1. Best Answer

    Tive que limpar um pouco seu DDL/DML pois estava com um erro de sintaxe:

    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)

    Eu prefiro variáveis ​​de tabela para isso, pois não há limpeza com que se preocupar.

    Vamos à sua pergunta. Não tenho certeza se a saída esperada definida é o que você realmente deseja, pois não faz referência a uma base ou ID pai. Também parece estar faltando o orderId 6.

    Uma técnica rCTE para obter a 'base' (pai final) pode ser escrita assim:

    ;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
    ID do produto nome preço anteriorProductId ativo nome ID do pedido baseID
    1 televisão sony 200,23000 0 John 1 1
    1 televisão sony 200,23000 0 Mary 2 1
    2 tv sony nova 240.43000 1 0 Kevin 3 1
    3 Apple Watch 300,45000 1 Joe 4 3
    3 Apple Watch 300,45000 1 andy 5 3
    4 Celular Samsung 1050,00000 0 Jim 6 4
    5 TV Sony avançada 400,00000 2 1 pam 7 1

    Posso ter entendido mal sua intenção e, na verdade, você só quer o PreviousProductID (no caso de OrderId 7, 2 em vez de 1), se for esse o caso, você deve ser capaz de substituir o lado recursivo do rCTE a.baseID AS baseIDpora.productId AS baseID

    • 4

relate perguntas