Sql Server选择不同的记录和OrderBy NEWID（）

this is the stored procdure, I need to select Distinct Records and display them in a random order but I am facing an error that selecting Distinct can not be used with newid(), so how can I walk around this ?

这是存储过程,我需要选择不同的记录并以随机顺序显示它们但我面临一个错误,选择Distinct不能与newid()一起使用,那么我该如何走动呢?

USE [OtlobODR]
GO
/****** Object:  StoredProcedure [OtlobFood].[ListOffersItems]    Script Date: 11/18/2012 13:01:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [OtlobFood].[ListOffersItems] 
@Fk_CampaignID int
as
select distinct
    CampaignID, CampaignOffers.OldPrice 
    ,   dbo.ItemSizes.IS_Id,
    , dbo.Items.[Item_Description_L2] as Item_Description 
    , dbo.Items.[Item_Image]
    , dbo.Items.[Item_Details]
    , dbo.ItemSizes.[IS_Price] as Price
    -- if null then it is not a featured meal
    , dbo.ProviderItems.[PI_Id] as ProviderItems_PI_ID
    , dbo.ItemCategories.[ItemCat_Id]
    , dbo.Providers.Provider_Name_L2 as Provider_Name
    , dbo.Providers.Provider_Menu_Logo
    , dbo.Providers.Provider_Id
FROM Items  
INNER JOIN ProviderItems ON Items.Item_Id = ProviderItems.Item_Id 
INNER JOIN dbo.ItemSizes ON dbo.Items.Item_Id = dbo.ItemSizes.Item_Id
INNER JOIN CampaignOffers ON CampaignOffers.ItemID = ItemSizes.IS_Id
INNER JOIN dbo.ItemCategories ON dbo.Items.ItemCat_Id = dbo.ItemCategories.ItemCat_Id
INNER JOIN dbo.Providers ON dbo.ProviderItems.Provider_Id = dbo.Providers.Provider_Id 
INNER JOIN dbo.Branches  ON dbo.Providers.Provider_Id = dbo.Branches.Provider_Id 
where Fk_CampaignID=@Fk_CampaignID 
group by  NEWID(),      
    CampaignID, CampaignOffers.OldPrice ,
    dbo.ItemSizes.IS_Id,
    dbo.Items.[Item_Description_L2],
    dbo.Items.[Item_Image], 
    dbo.Items.[Item_Details],
    dbo.ItemSizes.IS_Id,
    dbo.ItemSizes.[IS_Price] ,
    -- if null then it is not a featured meal
    dbo.ProviderItems.[PI_Id] , 
    dbo.ItemCategories.[ItemCat_Id],
      dbo.Providers.Provider_Name_L2,
    dbo.Providers.Provider_Menu_Logo
    ,dbo.Branches.Branch_Id,
    dbo.Providers.Provider_Id,CampaignID,CampaignOffers.OldPrice 
order by NEWID()

3 个解决方案

#1

You need to push the SELECT DISTINCT into an inner query (at which point you can also lose the GROUP BY) then do a NEWID() in the outer query. The general form is

您需要将SELECT DISTINCT推送到内部查询(此时您也可以丢失GROUP BY),然后在外部查询中执行NEWID()。一般形式是

select
    newid(), X.*
from
    (
    select distinct <cols>
    from <tables>
    where <whatever>
    ) X
order by 1

in your case I think this is what you want:

在你的情况下,我认为这是你想要的:

select
    newid(), X.*
from
    (
    select distinct
        CampaignID, CampaignOffers.OldPrice ,
        dbo.ItemSizes.IS_Id,
        dbo.Items.[Item_Description_L2] as Item_Description ,
        dbo.Items.[Item_Image], 
        dbo.Items.[Item_Details],
        dbo.ItemSizes.[IS_Price] as Price,
        -- if null then it is not a featured meal
        dbo.ProviderItems.[PI_Id] as ProviderItems_PI_ID, 
        dbo.ItemCategories.[ItemCat_Id],
        dbo.Providers.Provider_Name_L2 as Provider_Name,
        dbo.Providers.Provider_Menu_Logo,
        dbo.Providers.Provider_Id
    FROM
        Items
        INNER JOIN ProviderItems ON Items.Item_Id = ProviderItems.Item_Id 
        INNER JOIN dbo.ItemSizes ON dbo.Items.Item_Id = dbo.ItemSizes.Item_Id
        inner join CampaignOffers ON CampaignOffers.ItemID = ItemSizes.IS_Id
        INNER JOIN dbo.ItemCategories ON dbo.Items.ItemCat_Id = dbo.ItemCategories.ItemCat_Id
        INNER JOIN dbo.Providers ON dbo.ProviderItems.Provider_Id = dbo.Providers.Provider_Id 
        INNER JOIN dbo.Branches ON dbo.Providers.Provider_Id = dbo.Branches.Provider_Id 
    where
        Fk_CampaignID = @Fk_CampaignID 
    ) X
order by 1

#2

Remove the NEWID()'s from your query and surround it with

从查询中删除NEWID()并将其包围

SELECT * FROM (
   <your query>
) as t
ORDER BY NEWID()

#3

This query doesn't make much sense, there is no aggregation, so what is the purpose of the group by statement? and grouping by a NEWID() is not going to get you any grouping's anyway.

这个查询没有多大意义,没有聚合,那么group by语句的目的是什么?并且通过NEWID()进行分组无论如何都不会让你获得任何分组。

Combine the superfluous group by with the distinct clause and it feels more like you have a join condition incorrect that is causing a cartesian, and you are desperately trying to use distinct and group by's to eliminate that cartesian, it certainly makes little sense to have them both in the statement, and the newid in the group by should be removed.

将多余的组合与distinct子句相结合,感觉更像是你的连接条件不正确而导致笛卡儿,并且你拼命地尝试使用distinct和group by来消除那个笛卡尔,那么拥有它们当然没有意义在语句中,以及组中的newid都应该被删除。

#1