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

3 个解决方案

#1

更多相关文章

随机推荐