各位大侠:问大家一个问题。
下面有一张表:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
23 1 peter 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
25 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
26 2 sunjoin1 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000
28 2 sunjoin1 11 2009-08-31 14:49:09.000
29 2 sunjoin1 98 2009-08-31 14:49:17.000
我想得到这样的数据:
就是把下把BidPrice 这个字段中重复的都拿掉一个
需要的结果如下:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
26 2 sunjoin1 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000
不知道能不能得到这样的结果SQL,谢谢
下面有一张表:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
23 1 peter 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
25 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
26 2 sunjoin1 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000
28 2 sunjoin1 11 2009-08-31 14:49:09.000
29 2 sunjoin1 98 2009-08-31 14:49:17.000
我想得到这样的数据:
就是把下把BidPrice 这个字段中重复的都拿掉一个
需要的结果如下:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
26 2 sunjoin1 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000
不知道能不能得到这样的结果SQL,谢谢
需要的结果如下:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000
不知道能不能得到这样的结果SQL,谢谢 以上才是我要的数据!!1
select *from ta a
where not exists( select 1 from ta where BidPrice = a.BidPrice and id < a.id)
-- Author: flystone
-- Version:V1.001
-- Date:2009-09-24 18:09:03
-------------------------------------- Test Data: ta
If object_id('ta') is not null
Drop table ta
Go
Create table ta(ID int,BidId int,BiderName nvarchar(8),BidPrice int,BidTime datetime)
Go
Insert into ta
select 21,1,'sunjoin',1,'2009-08-28 09:57:00.000' union all
select 22,1,'sunjoin',5,'2009-08-28 09:57:00.000' union all
select 23,1,'peter',5,'2009-08-28 09:57:00.000' union all
select 24,1,'peter',10,'2009-08-28 09:57:00.000' union all
select 25,1,'peter',10,'2009-08-28 09:57:00.000' union all
select 29,1,'sunjoin1',98,'2009-08-31 14:49:17.000' union all
select 24,2,'peter',7,'2009-08-28 09:57:00.000' union all
select 25,2,'peter',88,'2009-08-28 09:57:00.000' union all
select 26,2,'sunjoin1',88,'2009-08-28 09:57:00.000' union all
select 27,2,'sunjoin',11,'2009-08-31 14:45:05.000' union all
select 28,2,'sunjoin1',11,'2009-08-31 14:49:09.000' union all
select 29,2,'sunjoin1',98,'2009-08-31 14:49:17.000'
Go
--Start
select *from ta a
where not exists( select 1 from ta where BidPrice = a.BidPrice and (id < a.id or BidId < a.BidId))--Result:
/*
ID BidId BiderName BidPrice BidTime
----------- ----------- --------- ----------- ------------------------------------------------------
21 1 sunjoin 1 2009-08-28 09:57:00.000
22 1 sunjoin 5 2009-08-28 09:57:00.000
24 1 peter 10 2009-08-28 09:57:00.000
29 1 sunjoin1 98 2009-08-31 14:49:17.000
24 2 peter 7 2009-08-28 09:57:00.000
25 2 peter 88 2009-08-28 09:57:00.000
27 2 sunjoin 11 2009-08-31 14:45:05.000(所影响的行数为 7 行)
*/
--End