熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

帶附加條件的NewID()用法(downmoon)

2013-11-13 10:23:57  來源: .NET編程 

  前天碰到一業務需求難倒了團隊成員

  表結構如下          CREATE TABLE [dbo][Product](
            [P_ID] [bigint] IDENTITY() NOT NULL
            [P_Name] [nvarchar]() NULL
            [CategoryID] [int] NULL
            [CategoryID] [int] NULL
            [CategoryID] [int] NULL
            [P_SingleIntro] [nvarchar]() NULL
            [LoginID] [nvarchar]() NULL
         CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
        (
            [P_ID] ASC
        )

  需要隨機列出表中每位用戶(loginid)的一個產品每次列出時隨機值不重復

  於是考慮用newid()          select max(P_ID)as P_IDloginID from product
        group by loginid order by NewID()

  結果每次取到的P_ID都是相同的! 不符合需求

  再修改如下          select P_IDLoginIDP_NameP_SingleIntro from product where P_ID in
        (
        select ( select top p_id from product as b where bloginid = cloginid order by newid() ) as p_id
        from ( select top aloginID from product as a group by aloginid order by NewID() ) as c
        )

  假定取前個用戶


From:http://tw.wingwit.com/Article/program/net/201311/13489.html
  • 上一篇文章:

  • 下一篇文章:
  • 推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.