在SQL中选择前第n行的结果是错误的
在SQL中选择前第n行的结果是错误的
提问于 2018-03-18 00:20:00
我有两张桌子，Product和SalesProduct。
Product表示例记录(这里ProductID是主键)：
ProductID | ProductCode | Name
----------+-------------+-----------
1 P001 Computer
2 p002 Laptop
3 p003 SpeakerSalesProduct表示例记录(这里ID是主键，ProductID是引用Products表的外键)：
ID| SalesNo | ProductID
--+---------+-----------
1 S0001 1
2 S0002 2
3 S0003 3
4 S0004 1
5 S0005 2
6 S0006 3
7 S0007 1
8 S0008 2
9 S0009 3当我写这个查询时：
SELECT
SalesNo, SalesProduct.ProductID, Name
FROM
SalesProduct
JOIN
Product ON SalesProduct.ProductID = Product.ProductID它将工作得很好，并返回以下结果：
SalesNo | ProductID | Name
--------+-----------+---------
S0001 1 Computer
S0002 2 Laptop
S0003 3 Speaker
S0004 1 Computer
S0005 2 Laptop
S0006 3 Speaker
S0007 1 Computer
S0008 2 Laptop
S0009 3 Speaker但是当我尝试像这样选择TOP(3)行时：
SELECT TOP(3)
SalesNo, SalesProduct.ProductID, Name
FROM
SalesProduct
JOIN
Product ON SalesProduct.ProductID = Product.ProductID此查询将返回相同的产品：
S0001 1 Computer
S0004 1 Computer
S0007 1 Computer但我想要这样的结果：
S0001 1 Computer
S0002 2 Laptop
S0003 3 Speaker上面的查询出了什么问题？怎样才能得到预期的输出呢？
回答 3
Stack Overflow用户
发布于 2018-03-18 03:50:54
您没有获得预期的输出可能是因为您没有指定Order By子句。您可以执行以下任一操作
SELECT TOP 3
SalesNo, SalesProduct.ProductID, Name
FROM
SalesProduct
JOIN
Product ON SalesProduct.ProductID = Product.ProductID
ORDER BY SalesNo ASC或者像这样
;WITH CTE
AS
(
SELECT
RN = ROW_NUMBER() OVER(ORDER BY SalesNo),
SalesNo, SalesProduct.ProductID, Name
FROM
SalesProduct
JOIN
Product ON SalesProduct.ProductID = Product.ProductID
)
SELECT
SalesNo, SalesProduct.ProductID, Name
WHERE RN<4 Stack Overflow用户
发布于 2018-03-18 01:09:59
SELECT TOP(3) sp.SalesNo, sp.ProductID, p.Name
FROM SalesProduct sp
INNER JOIN Product p ON SalesProduct.ProductID = Product.ProductID
ORDER BY sp.SalesNoStack Overflow用户
发布于 2018-03-18 01:48:12
请试试这个
DECLARE @Product TABLE (ProductId INT,ProductCode VARCHAR(10), Name VARCHAR(100))
INSERT INTO @Product(ProductId,ProductCode,Name)
(SELECT 1,'P001','Computer' UNION ALL SELECT 2,'P002','LAPTOP' UNION ALL SELECT 3,'P003','Speaker')
DECLARE @SalesProduct TABLE (Id INT,SalesNo VARCHAR(10),ProductId INT)
INSERT INTO @SalesProduct
SELECT 1,'S0001',1 UNION ALL SELECT 2,'S0002',2 UNION ALL SELECT 3,'S0003',3 UNION ALL SELECT 4,'S0004',1
UNION ALL SELECT 5,'S0005',2 UNION ALL SELECT 6,'S0006',3 UNION ALL SELECT 7,'S0007',1 UNION ALL SELECT 8,'S0008',2
UNION ALL SELECT 9,'S0009',3
SELECT TOP(3)
SP.SalesNo, SP.ProductID, P.Name
FROM
@SalesProduct SP
INNER JOIN
@Product P ON SP.ProductID = P.ProductID
ORDER BY SP.SalesNo页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49339118
复制相关文章
相似问题
腾讯云开发者
