主页 / dba / 问题 / 328702
Accepted
lifeisajourney
Asked: 2023-06-28 02:22:56 +0800 CST

编写查询以获取 SQL Server 中的“x”行数

  • 772

我有两个表 - 订单和产品。每个订单可以有 0 个或多个产品。

--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);

要求：编写一个查询，根据查询中传递的过滤器 过滤表customerName中的列Orders或表ProductName中的列，并按其排序并获取请求的订单数。这是我写的查询，但它没有完成我正在寻找的内容：ProductsOrderDate

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

结果： 在此输入图像描述

参数@limitrows和@skiprows用于确定所需的结果集。但是，在给定的查询中，跳过前 5 行并返回接下来的 5 行并不能提供预期的结果。目标是跳过前 5 个不同的订单 ID，然后选择接下来的 5 个不同的订单 ID。随后，结果集应与 Products 表连接以检索所有相应的产品。需要注意的是，由于每个订单可以有多个产品，因此最终结果集可能包含超过 5 行。如果有人可以提供如何实现这一目标的指导，我们将不胜感激。谢谢你！

sql-server

2 个回答

  1. Best Answer

    一种方法是使用DENSE_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

    目前尚不完全清楚“基于查询中传递的过滤器”在您的问题中到底意味着什么。

    上面的查询查找客户姓名或产品名称之一中包含字母“t”的所有订单。

    在此列表中，它获取从倒数第三个到倒数第十个的订单，并返回这些订单及其所有产品（甚至是那些名称中没有字母“t”的订单）。但在您的示例中，所有产品中都有一个字母“t”作为单词“product”的一部分。

    如果订单没有任何产品，查询将返回该订单的单个记录，产品字段中为空（类似于左连接的工作原理）。

    SQLFiddle

    • 3

相关问题