带附加条件的NewID()用法(downmoon)

前天碰到一业务需求，难倒了团队成员，

表结构如下：

<!-- {cps..0}--> CREATE TABLE [ dbo ] . [ Product ] (
[ P_ID ] [ bigint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ P_Name ] [ nvarchar ] ( 255 ) NULL ,
[ CategoryID1 ] [ int ] NULL ,
[ CategoryID2 ] [ int ] NULL ,

[ CategoryID3 ] [ int ] NULL ,

[ P_SingleIntro ] [ nvarchar ] ( 200 ) NULL ,

[ LoginID ] [ nvarchar ] ( 50 ) NULL ,

CONSTRAINT [ PK_Product ] PRIMARY KEY CLUSTERED
(
[ P_ID ] ASC
)

需要随机列出表中每位用户(loginid)的一个产品，每次列出时随机值不重复。

于是考虑用newid()

<!-- {cps..1}--> select max (P_ID) as P_ID,loginID from product
group by loginid order by NewID ()

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

再修改如下：

<!-- {cps..2}--> select P_ID,LoginID,P_Name,P_SingleIntro from product where P_ID in
(
select ( select top 1 p_id from product as b where b.loginid = c.loginid order by newid ()) as p_id
from ( select top 10000 a.loginID from product as a group by a.loginid order by NewID ()) as c
)
-- 假定取前10000个用户

OK!!!!(就是性能寒碜了点！^_^ )

感谢尘尘 。
在SQL 2005/2008下可以这么做。

select P_ID,LoginID,P_Name,P_SingleIntro from (select P_ID,LoginID,P_Name,P_SingleIntro, ROW_NUMBER() over(partition by loginID order by new) rn from (select *,NEWID() new from Product) as temp) te where rn=1 and LoginID is not null and Len(loginID)>0 order by loginid asc

助人等于自助! 3w@live.cn