各位大侠:问大家一个问题。
下面有一张表:
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
我想得到这样的数据:
就是把在相同的BidId下把BidPrice 这个字段中重复的所有都拿掉
需要的结果如下:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.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
我想得到这样的数据:
就是把在相同的BidId下把BidPrice 这个字段中重复的所有都拿掉
需要的结果如下:
ID BidId BiderName BidPrice BidTime
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.000
不知道能不能得到这样的结果SQL,谢谢
create table [TB]([ID] int,[BidId] int,[BiderName] varchar(8),[BidPrice] int,[BidTime] datetime)
insert [TB]
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 26,1,'sunjoin1',2,'2009-08-28 09:57:00.000' union all
select 27,1,'sunjoin',1,'2009-08-31 14:45:05.000' union all
select 28,1,'sunjoin1',8,'2009-08-31 14:49:09.000' union all
select 29,1,'sunjoin1',98,'2009-08-31 14:49:17.000'select ID=min(ID),BidId=min(BidId),BiderName,BidPrice=sum(BidPrice),BidTime=min(BidTime)
from TB group by BiderName
/*
ID BidId BiderName BidPrice BidTime
----------- ----------- --------- ----------- -----------------------
23 1 peter 25 2009-08-28 09:57:00.000
21 1 sunjoin 7 2009-08-28 09:57:00.000
26 1 sunjoin1 108 2009-08-28 09:57:00.000(3 行受影响)*/drop table TB早上问过了
INSERT @TB
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'SELECT *
FROM @TB AS T
WHERE NOT EXISTS(SELECT * FROM @TB WHERE BidId=T.BidId AND ID<>T.ID AND BidPrice=T.BidPrice)
/*
ID BidId BiderName BidPrice BidTime
----------- ----------- --------- ----------- ------------------------------------------------------
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.000
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-01 16:36:17
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[BidId] int,[BiderName] varchar(8),[BidPrice] int,[BidTime] datetime)
insert [tb]
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'
--------------开始查询--------------------------
select * from tb where BidPrice in( select BidPrice from
(select
BidPrice,BidId
from
tb
group by
BidPrice,BidId
having
count(1)=1)t)----------------结果----------------------------
/* ID BidId BiderName BidPrice BidTime
----------- ----------- --------- ----------- -----------------------
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.000(4 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-01 16:36:17
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[BidId] int,[BiderName] varchar(8),[BidPrice] int,[BidTime] datetime)
insert [tb]
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'
--------------开始查询--------------------------
select * from tb where BidPrice in( select BidPrice from
(select
BidPrice,BidId
from
tb
group by
BidPrice,BidId
having
count(1)=1)t)----------------结果----------------------------
/* ID BidId BiderName BidPrice BidTime
----------- ----------- --------- ----------- -----------------------
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.000(4 行受影响)
*/
declare @t1 table( ID int , BidId int , BiderName varchar(20), BidPrice int , BidTime datetime)insert into @t1 values(21,1,'sunjoin',1,'2009-08-28 09:57:00.000')
insert into @t1 values(22,1,'sunjoin',5,'2009-08-28 09:57:00.000')
insert into @t1 values(23,1,'peter',5,'2009-08-28 09:57:00.000')
insert into @t1 values(24,1,'peter',10,'2009-08-28 09:57:00.000')
insert into @t1 values(25,1,'peter',10,'2009-08-28 09:57:00.000')
insert into @t1 values(29,1,'sunjoin1',98,'2009-08-31 14:49:17.000')
insert into @t1 values(24,2,'peter',7,'2009-08-28 09:57:00.000')
insert into @t1 values(25,2,'peter',88,'2009-08-28 09:57:00.000')
insert into @t1 values(26,2,'sunjoin1',88,'2009-08-28 09:57:00.000')
insert into @t1 values(27,2,'sunjoin',11,'2009-08-31 14:45:05.000')
insert into @t1 values(28,2,'sunjoin1',11,'2009-08-31 14:49:09.000')
insert into @t1 values(29,2,'sunjoin1',98,'2009-08-31 14:49:17.000') select a.* from @t1 a
inner join (select BidId,BidPrice from @t1
group by BidId,BidPrice
having count(*)=1
)b on b.BidId = a.BidId and b.BidPrice =a.BidPrice
---------------------
21 1 sunjoin 1 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
29 2 sunjoin1 98 2009-08-31 14:49:17.000