怎么根据采购次数改变标志位？

如何根据采购次数改变标志位？？？？？？？？？？？？？？？？
首先根据采购表和采购明细表统计出当月所采购的物品及其次数

SQL code

select  distinct productID as PID,count(*) as times  -- into #tempfrom pordersub inner join porder  on  pordersub.porderID = porder.porderID where datediff(month,porder.updatetime,getdate()) =0 group by productID

如下图


然后在product表中有isporder标志位，该如何根据productID，在product表将采购次数大于1的product的isporder设为true，采购次数小于1的设为false呢？

[解决办法]

SQL code

select  distinct productID as PID,case when count(1) > 1 then 1 else 0 end as isporder  -- into #tempfrom pordersub inner join porder  on  pordersub.porderID = porder.porderID where datediff(month,porder.updatetime,getdate()) =0 group by productID
[解决办法]
SQL code
update p set isporder=case when times>1 then 'true' else 'false' endfrom product p,(select  distinct productID as PID,count(*) as times  -- into #tempfrom pordersub inner join porder  on  pordersub.porderID = porder.porderID where datediff(month,porder.updatetime,getdate()) =0 group by productID) twhere t.productID =p.productID 

 

查看更多 下一篇