查询视图重复行赋值
查询视图重复行赋值
select * from vip_scysbzclmxqty (视图)

如下: (mqty重复)
billid ,billcode ,mgoodsid,mqty,dgoodsid,dqty
1001 ,pc001 , 123 ,300, 2 , 300
1001 ,pc001 , 123 ,300, 3 , 300
1001 ,pc001 , 123 ,300, 4 , 300
1001 ,pc001 , 123 ,300, 5 , 300

要求生成查询视图结果如下:
billid ,billcode ,mgoodsid,mqty,dgoodsid,dqty
1001 ,pc001 , 123 ,300, 2 , 300
1001 ,pc001 , 123 ,0, 3 , 300
1001 ,pc001 , 123 ,0, 4 , 300
1001 ,pc001 , 123 ,0, 5 , 300



------解决方案--------------------
billid ,billcode ,mgoodsid,mqty,dgoodsid,dqty
1001 ,pc001 , 123 ,300, 2 , 300
1001 ,pc001 , 123 ,0, 3 , 300
1001 ,pc001 , 123 ,0, 4 , 300
1001 ,pc001 , 123 ,0, 5 , 300

--这个结果通过什么算法的来的？？
------解决方案--------------------

SQL code

select billid ,billcode ,mgoodsid,case when num=1 then mqty else 0 end as mqty ,dgoodsid,dqty
from
(selectrow_number()over(order by dgoodsid asc) as num,* from vip_scysbzclmxqty) a

------解决方案--------------------
如果需要分组的话
SQL code
select billid ,billcode ,mgoodsid,case when num=1 then mqty else 0 end as mqty ,dgoodsid,dqty
from
(select row_number()over(partition by billid order by dgoodsid asc) as num,* from vip_scysbzclmxqty) a