select * from table aa where (select count(*) from table bb where aa.invoice=bb.invoice and aa.inv_qty=bb.inv_qty and aa.amount=bb.amount and aa.date=bb.date)<=1
看这个是不是你需要的结果,如果不是追加上下面的条件试试.or not exists (select 1 from table bb where aa.invoice=bb.invoice and aa.inv_qty=bb.inv_qty and aa.amount=bb.amount and aa.date=bb.date and aa.id>bb.id)
看这个是不是你需要的结果,如果不是追加上下面的条件试试.or not exists (select 1 from table bb where aa.invoice=bb.invoice and aa.inv_qty=bb.inv_qty and aa.amount=bb.amount and aa.date=bb.date and aa.id>bb.id)
select * from 表 bb where exists( select 1 from 表 aa
where aa.invoice=bb.invoice and aa.inv_qty=bb.inv_qty and aa.amount=bb.amount and aa.date=bb.date
group by invoice,inv_qty,amount,date
having count(*)<=1)
insert #a values('001',22, 100,'03/05/06', 1)
insert #a values('001',22, 100 ,'03/05/06', 2)
insert #a values('001',30, 100 ,'03/05/06', 3)
insert #a values('001',22, 200 ,'03/05/06', 4)
insert #a values('002',200, 300 ,'03/05/06', 5)
insert #a values('002',200, 300 ,'03/05/06', 6)
insert #a values('003',20, 30 ,'03/05/06', 7)
insert #a values('003',20, 80 ,'03/05/06', 8)select invoice from (
select invoice,inv_qty,amount,[date] from #a group by invoice,inv_qty,amount,[date]
) tem group by invoice having sum(1)>1go
drop table #a
把其他的相同發標的 inv_qty,amount,date 改為與 ID 值最小的那筆相同
非常謝謝 !
Update a Set a.inv_qty = b.inv_qty,a.amout = b.amout,a.date = b.date
From Table a Join #tmp1 b on a.invoice = b.invoice
Drop Table #tmp1
Update a Set a.inv_qty = b.inv_qty,a.amout = b.amout,a.date = b.date
From Table a Join #tmp1 b on a.invoice = b.invoice
where a.inv_qty <> b.inv_qty or a.inv_amout <> b.inv_amout or a.date <> b.date
Drop Table #tmp1
Update a Set a.inv_qty = b.inv_qty,a.amout = b.amout,a.date = b.date
From Table a Join #tmp1 b on a.invoice = b.invoice
where a.inv_qty <> b.inv_qty or a.inv_amout <> b.inv_amout or a.date <> b.date
Drop Table #tmp1
insert #a values('001',22, 100,'03/05/06', 1)
insert #a values('001',22, 100 ,'03/05/06', 2)
insert #a values('001',30, 100 ,'03/05/06', 3)
insert #a values('001',22, 200 ,'03/05/06', 4)
insert #a values('002',200, 300 ,'03/05/06', 5)
insert #a values('002',200, 300 ,'03/05/06', 6)
insert #a values('003',20, 30 ,'03/05/06', 7)
insert #a values('003',20, 80 ,'03/05/06', 8)update #a set inv_qty=tem2.inv_qty,amount=tem2.amount,[date]=tem2.[date] from (
select * from #a tem where ID=(select min(ID) from #a where invoice=tem.invoice)
) tem2 where #a.invoice=tem2.invoiceselect * from #a
go
drop table #a