代码之家 › 专栏 › 技术社区 › Aboud Zakaria

删除重复的数据复杂sql查询

sql-server sql

Aboud Zakaria · 技术社区 · 11 年前

我有以下复杂的查询

SELECT TOP 20
    [image].Image_id, Source_Link
FROM 
    Item
INNER JOIN 
    [Image] ON Item.Image_id = [Image].Image_id
INNER JOIN 
    Age_Item ON Item.Item_id = Age_Item.Item_id
WHERE 
    Gender = 'male'
    AND Age_Item.Age_id = 4
    AND [Image].Image_id NOT IN
        (SELECT [Image].Image_id
         FROM [Image]
         INNER JOIN Item on [Image].Image_id = Item.Image_id
         INNER JOIN Session_Item on Item.Item_id = Session_Item.Item_id
         WHERE Session_Item.Session_id = 3)
ORDER BY 
    NEWID() -- random rows

然而,我意识到有时它会显示一些重复的结果,我想删除重复的行

我试过了 SELECT DISTINCT 但它显示了这个错误:

如果指定了select DISTINCT,则ORDER BY项目必须出现在选择列表中。

我不能用 GROUP BY([Image].Image_id) 在这种情况下,因为列 [Image].Source_Link 未包含在聚合函数中。

我应该做什么来解决这个问题?

//编辑: 我已经解决了,谢谢你们的回答

SELECT TOP 20 * FROM
(
SELECT DISTINCT [image].Image_id, Source_Link
FROM Item
INNER JOIN [Image] ON Item.Image_id = [Image].Image_id
INNER JOIN Age_Item ON Item.Item_id = Age_Item.Item_id
WHERE Gender='male'
AND Age_Item.Age_id = 4
AND [Image].Image_id NOT IN
(
    SELECT [Image].Image_id
    FROM [Image]
    INNER JOIN Item on [Image].Image_id = Item.Image_id
    INNER JOIN Session_Item on Item.Item_id = Session_Item.Item_id
    WHERE Session_Item.Session_id = 3
)
)query ORDER BY NEWID();

2 回复 | 直到 11 年前

Conrad Frix 11 年前

因为你想要一个随机的20个不同的和随机的顺序,你需要得到一个不同的集合,然后应用你的 TOP 20 ORDER BY NEW_ID 之后

你可以用CTE做到这一点

with cte as (

SELECT DISTINCT [image].Image_id, Source_Link
FROM Item
INNER JOIN [Image] ON Item.Image_id = [Image].Image_id
INNER JOIN Age_Item ON Item.Item_id = Age_Item.Item_id
WHERE Gender='male'
AND Age_Item.Age_id = 4
AND [Image].Image_id NOT IN
(
    SELECT [Image].Image_id
    FROM [Image]
    INNER JOIN Item on [Image].Image_id = Item.Image_id
    INNER JOIN Session_Item on Item.Item_id = Session_Item.Item_id
    WHERE Session_Item.Session_id = 3
)
)
Select TOP 20 DISTINCT * FROM CTE ORDER BY NEWID()

Richard Hansell 11 年前

SELECT DISTINCT * FROM ( <your original query> ) query;

我刚刚尝试了以下方法:

SELECT DISTINCT * FROM (
SELECT TOP 20 [image].Image_id, Source_Link
FROM Item
INNER JOIN [Image] ON Item.Image_id = [Image].Image_id
INNER JOIN Age_Item ON Item.Item_id = Age_Item.Item_id
WHERE Gender='male'
AND Age_Item.Age_id = 4
AND [Image].Image_id NOT IN
(
    SELECT [Image].Image_id
    FROM [Image]
    INNER JOIN Item on [Image].Image_id = Item.Image_id
    INNER JOIN Session_Item on Item.Item_id = Session_Item.Item_id
    WHERE Session_Item.Session_id = 3
)
ORDER BY NEWID()) query;

…对我来说一切都很好。