Início / dba / Perguntas / 328702
Accepted
lifeisajourney
Asked: 2023-06-28 02:22:56 +0800 CST

Escrever consulta para obter o número 'x' de linhas no SQL Server

  • 772

Eu tenho duas tabelas - Pedidos e Produtos. Cada pedido pode ter 0 ou mais produtos.

--drop table orders
--drop table products

CREATE TABLE Orders (
    OrderId INT PRIMARY KEY,
    OrderDate DATE,
    CustomerName VARCHAR(50)
);

CREATE TABLE Products (
    ProductId INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Price DECIMAL(10, 2),
    OrderId INT,
    FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
);

INSERT INTO Orders (OrderId, OrderDate, CustomerName)
VALUES (1, '2023-01-01', 'John Doe'),
       (2, '2023-01-02', 'Jane Smith'),
       (3, '2023-01-03', 'Michael Johnson'),
       (4, '2023-01-04', 'Emily Davis'),
       (5, '2023-01-05', 'Robert Wilson'),
       (6, '2023-01-06', 'Olivia Thompson'),
       (7, '2023-01-07', 'William Anderson'),
       (8, '2023-01-08', 'Sophia Martinez'),
       (9, '2023-01-09', 'James Hernandez'),
       (10, '2023-01-10', 'Ava Lee'),
       (11, '2023-01-11', 'Benjamin Walker'),
       (12, '2023-01-12', 'Mia Lewis'),
       (13, '2023-01-13', 'Ethan Hall'),
       (14, '2023-01-14', 'Charlotte White'),
       (15, '2023-01-15', 'Alexander King'),
       (16, '2023-01-16', 'Amelia Scott'),
       (17, '2023-01-17', 'Daniel Green'),
       (18, '2023-01-18', 'Harper Adams'),
       (19, '2023-01-19', 'Matthew Clark'),
       (20, '2023-01-20', 'Luna Rodriguez'),
       (21, '2023-01-21', 'Henry Carter'),
       (22, '2023-01-22', 'Lily Hill'),
       (23, '2023-01-23', 'Sebastian Turner'),
       (24, '2023-01-24', 'Avery Ward'),
       (25, '2023-01-25', 'Scarlett Foster'),
       (26, '2023-01-26', 'Joseph Diaz'),
       (27, '2023-01-27', 'Victoria Morris'),
       (28, '2023-01-28', 'David Sullivan'),
       (29, '2023-01-29', 'Penelope Price'),
       (30, '2023-01-30', 'Jackson Brooks');

INSERT INTO Products (ProductId, ProductName, Price, OrderId)
VALUES 
    -- Products for OrderId 1
    (1, 'Product A', 10.99, 1),
    (2, 'Product B', 19.99, 1),
    
    -- Products for OrderId 2
    (3, 'Product C', 5.99, 2),
    
    -- Products for OrderId 3
    (4, 'Product D', 7.50, 3),
    (5, 'Product E', 14.99, 3),
    (6, 'Product F', 8.99, 3),
    
    -- Products for OrderId 4
    (7, 'Product G', 12.99, 4),
    (8, 'Product H', 9.99, 4),
    (9, 'Product I', 6.49, 4),
    
    -- Products for OrderId 5
    (10, 'Product J', 18.50, 5),
    (11, 'Product K', 11.99, 5),
    
    -- Products for OrderId 6
    (12, 'Product L', 7.99, 6),
    (13, 'Product M', 13.99, 6),
    
    -- Products for OrderId 7
    (14, 'Product N', 9.99, 7),
    
    -- Products for OrderId 8
    (15, 'Product O', 16.99, 8),
    (16, 'Product P', 14.50, 8),
    
    -- Products for OrderId 9
    (17, 'Product Q', 8.99, 9),
    (18, 'Product R', 6.99, 9),
    
    -- Products for OrderId 10
    (19, 'Product S', 12.99, 10),
    
    -- Products for OrderId 11
    (20, 'Product T', 7.50, 11),
    
    -- Products for OrderId 12
    (21, 'Product U', 9.99, 12),
    (22, 'Product V', 11.99, 12),
    
    -- Products for OrderId 13
    (23, 'Product W', 15.99, 13),
    (24, 'Product X', 8.50, 13),
    
    -- Products for OrderId 14
    (25, 'Product Y', 9.99, 14),
    
    -- Products for OrderId 15
    (26, 'Product Z', 12.99, 15),
    (27, 'Product AA', 7.99, 15),
    (28, 'Product BB', 14.99, 15),
    
    -- Products for OrderId 16
    (29, 'Product CC', 11.50, 16),
    (30, 'Product DD', 9.99, 16);

Requisito: Escreva uma consulta que filtre a customerNamecoluna da Orderstabela ou ProductNamea coluna da Productstabela com base no filtro passado na consulta e a ordene OrderDatee busque o número de pedidos solicitados. Aqui está a consulta que escrevi, mas não realiza o que estou procurando:

declare @filter varchar(100) = 't'
declare @skiprows int = 5
declare @limitrows int = 5

select *
  from dbo.orders o
  left join dbo.products p
  on o.orderId = p.orderId
  where (customerName like '%'+@filter+'%'
    or productName like '%'+@filter+'%')
  order by orderdate desc
  offset @skiprows rows
  fetch next @limitrows rows only

Resultado: insira a descrição da imagem aqui

Os parâmetros @ limitrowse @ skiprowssão usados ​​para determinar o conjunto de resultados desejado. No entanto, na consulta fornecida, pular as primeiras 5 linhas e retornar as próximas 5 linhas não fornece o resultado esperado. O objetivo é pular os primeiros 5 IDs de pedidos distintos e, em seguida, selecionar os próximos 5 IDs de pedidos distintos. Posteriormente, o conjunto de resultados deve ser unido à tabela Produtos para recuperar todos os produtos correspondentes. É importante observar que o conjunto de resultados final pode conter mais de 5 linhas, pois cada pedido pode ter vários produtos. Se alguém puder fornecer orientações sobre como fazer isso, seria muito apreciado. Obrigado!

sql-server

2 respostas

  1. Best Answer

    Uma maneira seria usarDENSE_RANK

    declare @filter varchar(100) = 't'
declare @skiprows int = 5
declare @limitrows int = 5;

WITH T AS
(
select o.*, 
        p.ProductId,
        p.productName,
        p.Price,
        Rnk = DENSE_RANK() OVER (ORDER BY o.orderdate desc, o.orderId DESC)
  from dbo.orders o
  left join dbo.products p
  on o.orderId = p.orderId
  where (o.customerName like '%'+@filter+'%'
    or p.productName like '%'+@filter+'%')
)
SELECT *
FROM T
WHERE Rnk > @skiprows AND Rnk <= @skiprows + @limitrows
order by Rnk
    • 4 
  2. DECLARE @filter VARCHAR(100) = 't';
DECLARE @skiprows INT = 2;
DECLARE @limitrows INT = 8;

SELECT  *
FROM    (
        SELECT  *
        FROM    orders
        WHERE   (
                CustomerName LIKE '%' + @filter + '%'
                OR
                EXISTS
                (
                SELECT  *
                FROM    products
                WHERE   products.OrderId = orders.OrderId
                        AND ProductName LIKE '%' + @filter + '%'
                ))
        ORDER BY
                orderDate DESC, orderId DESC
        OFFSET  @skiprows ROWS
        FETCH NEXT
                @limitrows ROWS ONLY
        ) orders
OUTER APPLY
        (
        SELECT  *
        FROM    products
        WHERE   products.OrderId = orders.OrderId
        ) products
GO

    Não está totalmente claro o que exatamente "com base no filtro passado na consulta" significa em sua pergunta.

    A consulta acima encontra todos os pedidos que possuem a letra "t" no nome do cliente ou no nome de um dos produtos.

    Dessa lista, pega os pedidos do penúltimo ao décimo penúltimo, e devolve esses pedidos e todos os seus produtos (mesmo aqueles que não possuem a letra "t" no nome). No seu exemplo, porém, todos os produtos têm uma letra "t" como parte da palavra "produto".

    Se um pedido não tiver nenhum produto, a consulta retornará um único registro para esse pedido com nulos nos campos do produto (semelhante ao funcionamento da junção à esquerda).

    SQLFiddleName

    • 3

relate perguntas