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,dqty
from
(
    select row_number() over(partition by billcode,billdate,mqty,mmaterialid order by newid()) rn from view_man
) t
where t.rn=1

------解决方案--------------------

少了一个*
SQL code
--猜一个
select billcode,billdate,mqty,mmaterialid, dmaterialid,dqty
from
(
    select row_number() over(partition by billcode,billdate,mqty,mmaterialid order by newid()) rn,* from view_man
) t
where t.rn=1