MSSQL2000 视图数据转换为视图解决思路

MSSQL2000 视图数据转换为视图MSSQL2000 视图数据转换为视图视图原结果如下：view_manbillcode,billdate,mq

MSSQL2000 视图数据转换为视图
MSSQL2000 视图数据转换为视图
视图原结果如下：
view_man
billcode,billdate,mqty,mmaterialid, dmaterialid,dqty
1001 ,2012-08-19, 100, 10001 , 10003 , 300
1001 ,2012-08-19, 100, 10001 , 10007 , 300
1001 ,2012-08-19, 100, 10001 , 10009 , 600
1001 ,2012-08-19, 100, 10001 , 10006 , 300
1001 ,2012-08-19, 100, 10001 , 10008 , 700
1001 ,2012-08-19, 100, 10001 , 10003 , 300

1003 ,2012-08-19, 300, 10001 , 10003 , 300
1003 ,2012-08-19, 300, 10001 , 10007 , 900
1003 ,2012-08-19, 300, 10001 , 10009 , 600

求：当行中的列billcode,billdate,mqty,mmaterialid 完全相同时只取期中一行内容

转换为视图结果
billcode,billdate,mqty,mmaterialid, dmaterialid,dqty
1001 ,2012-08-19, 100, 10001 , 10003 , 300
1001 ,2012-08-19, 0, 10001 , 10007 , 300
1001 ,2012-08-19, 0, 10001 , 10009 , 600
1001 ,2012-08-19, 0, 10001 , 10006 , 300
1001 ,2012-08-19, 0, 10001 , 10008 , 700
1001 ,2012-08-19, 0, 10001 , 10003 , 300

1003 ,2012-08-19, 300, 10001 , 10003 , 300
1003 ,2012-08-19, 0, 10001 , 10007 , 900
1003 ,2012-08-19, 0, 10001 , 10009 , 600

[解决办法]

SQL code

--猜一个select billcode,billdate,mqty,mmaterialid, dmaterialid,dqtyfrom(    select row_number() over(partition by billcode,billdate,mqty,mmaterialid order by newid()) rn from view_man) twhere t.rn=1
[解决办法]
少了一个*
SQL code--猜一个select billcode,billdate,mqty,mmaterialid, dmaterialid,dqtyfrom(    select row_number() over(partition by billcode,billdate,mqty,mmaterialid order by newid()) rn,* from view_man) twhere t.rn=1