max怎么去掉重复的行？解决马上给分！

Question

select * into tab from tb
delete from tb
insert into tb select * from tab

Answer 1

上面稿錯了select receiveno=max(receiveno),itemno,purprice  into tab from tb group by itemno,purprice  order by itemnodelete from tb
insert into tb select * from tab

Answer 2

我把max(receiveno)改成 receiveno=max(receiveno)还是重复啊  
delete from tb
insert into tb select * from tab
是什么意思？

Answer 3

select distinct receiveno=max(receiveno),itemno,purprice  into tab from tb group by itemno,purprice  order by itemnodelete from tb
insert into tb select * from tab加distinct,重復的記錄只選一條
上面的三條命令是先把不重復的記錄放到臨時表﹐刪除原表﹐再把臨時表的數據導回到原表中

Answer 4

你的意思是不是相同的itemno,purprice ﹐取出最大的日期﹖如果是這樣用
select receiveno=max(receiveno),itemno,purprice  into tab from tb group by itemno,purprice delete from tb
insert into tb select * from tab

Answer 5

我是求相同的itemno 他们的最大日期你的方法我试过了 不 行啊

Answer 6

create table tb(receiveno varchar(20),itemno varchar(10),purprice numeric(18,6))
Insert into tb 
select '200408170023','01000001',0.025000
union all select '200403080032','01000001',0.046300
union all select '200402190029','01000001',0.086600
union all select '200402210018','01000002',0.008500
union all select '200408170023','01000003',0.006000
union all select '200403080014','01000003',0.010000
union all select '200407150031','01000004',0.005400
union all select '200409020087','01000004',0.005500
union all select '200403080032','01000004',0.005940
union all select '200408190071','01000004',0.006000
union all select '200402190030','01000004',0.006400
union all select '200402190030','01000005',0.006000
union all select '200409250035','01000005',0.006500
union all select '200408170023','01000005',0.007000select * from tb a where receiveno in(select max(receiveno) from tb where itemno=a.itemno )--結果
receiveno      itemno      purprice
----------------------------------
200409250035 01000005 .006500
200409020087 01000004 .005500
200408170023 01000003 .006000
200402210018 01000002 .008500
200408170023 01000001 .025000你要的結果是不是這個﹖

Answer 7

也可以這樣select a.* from tb a,
(select receiveno=max(receiveno),itemno from tb group by itemno)b
where a.receiveno=b.receiveno and a.itemno=b.itemno
order by a.itemno--結果
receiveno        itemno     purprice
----------------------------------
200408170023 01000001 .025000
200402210018 01000002 .008500
200408170023 01000003 .006000
200409020087 01000004 .005500
200409250035 01000005 .006500

Answer 8

若receiveno是唯一的，您可以这样写：
select receiveno,itemno,purprice  
from tb a where receiveno in(select max(receiveno) receiveno from tb group by itemno)

Answer 9

用下面這種方法效率高點﹐前面一種方法句里有in,影響速度select a.* from tb a,
(select receiveno=max(receiveno),itemno from tb group by itemno)b
where a.receiveno=b.receiveno and a.itemno=b.itemno
order by a.itemno