我用多个CTE编写一个查询,从第一个CTE中选择所有行,从第二个CTE中选择4行 . 我选择了部分,但它返回CTE2中的整个项目 .
this is what it returns and selected part that i want.
这是我的疑问:
;WITH CTE1(UserName, ItemId, ItemName, ImageId, ImageName) AS
(
SELECT [Users].UserName, [Items].ItemId, [Items].Name, [Images].ImageId, [Images].Name
FROM Items INNER JOIN Images
ON [Items].ItemId = [Images].ItemId INNER JOIN Users
ON [Items].UserId = TestUser.UserId
WHERE ([Items].ItemId = 1) AND ([Items].UserId = 1) AND ([Items].Name = N'Pen')
),
CTE2(ItemId, ItemName, ImageId, ImageName) AS
(
SELECT [Items].ItemId, [Items].Name, [Images].ImageId, [Images].Name
FROM Items INNER JOIN [Images]
ON [Items].ItemId = [Images].ItemId
WHERE [Items].UserId = 1
GROUP BY [Items].ItemId, [Items].Name, [Images].ImageId, [Images].Name
)
SELECT CTE1.UserName, CTE1.ItemId AS ItemId1, CTE1.ItemName AS ItemName1,
CTE1.ImageId AS ImageId1, CTE1.ImageName AS ImageName1, COUNT(CTE1.ItemId) OVER() AS ItemCounter1,
CTE2.ItemId AS ItemId2, CTE2.ItemName AS ItemName2, CTE2.ImageId AS ImageId2, CTE2.ImageName AS ImageName2, COUNT(CTE2.ItemId) OVER() AS TotalCount
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY ItemId ORDER BY ItemId) AS RN FROM CTE2) CTE2 LEFT JOIN CTE1
ON CTE2.ItemId = CTE1.ItemId WHERE RN = 1 ORDER BY CTE1ItemId DESC
所有帮助都将得到赞赏 .
2 回答
我不确切地知道你想要什么或者哪四行,但是想到了
TOP 4
:通常,会有一个
ORDER BY
来选择最近的四个,最旧的四个,最红的四个或其他什么 .谢谢你的帮助 .
我再次写了我的查询,得到了我的错 . 我知道这不是一件平常的事情,但我希望与其他可能也有这个问题的人分享我的问题 .
这就是我写的:
非常感谢你 .