表tb
ID varchar 主键
dt varchar(yyyy-mm-dd)
palce varchar
person varchar
money1 varchar 同一天、同一地点、同一人、交易2笔或2笔以上,交易金额在100-200之间的记录
注:2笔或2笔以上,交易金额在100-200之间 指每笔都在100-200之间
查询结果不要给金额求和,只要每笔的记录。
数据库是sql server 2005
ID varchar 主键
dt varchar(yyyy-mm-dd)
palce varchar
person varchar
money1 varchar 同一天、同一地点、同一人、交易2笔或2笔以上,交易金额在100-200之间的记录
注:2笔或2笔以上,交易金额在100-200之间 指每笔都在100-200之间
查询结果不要给金额求和,只要每笔的记录。
数据库是sql server 2005
select convert(varchar(10),dt,120),palce,person from tb
where money1>=100 and money1<=200
group by convert(varchar(10),dt,120),palce,person
having count(1)>1
--金额字段忘记加了。。补上。。select convert(varchar(10),dt,120),palce,person,money1 from tb
where money1>=100 and money1<=200
group by convert(varchar(10),dt,120),palce,person,money1
having count(1)>1
insert into @tb select 1,'2008-01-01','北京','张','110'
insert into @tb select 2,'2008-01-01','北京','张','120'
insert into @tb select 3,'2008-01-01','北京','李','130'
insert into @tb select 4,'2008-01-01','北京','李','140'
insert into @tb select 5,'2008-01-01','北京','王','150'
insert into @tb select 6,'2008-01-01','北京','王','210'select * from @tb tp
where (
select count(1) from @tb where datediff(dd,dt,tp.dt)=0 and palce=tp.palce and person=tp.person and [money] between 100 and 200
)>=2id dt palce person money
1 2008-01-01 北京 张 110
2 2008-01-01 北京 张 120
3 2008-01-01 北京 李 130
4 2008-01-01 北京 李 140
select * from tb a,(
select dt,palce,person from tb
where money2 between 100 and 200
group by dt,palce,person
having count(1)>=2) b
where a.dt=b.dt and a.palce=b.palce and a.person=b.person and a.money2 between 100 and 200
insert into @tb select 1,'2008-01-01','北京','张','110'
insert into @tb select 2,'2008-01-01','北京','张','120'
insert into @tb select 3,'2008-01-01','北京','李','130'
insert into @tb select 4,'2008-01-01','北京','李','140'
insert into @tb select 4,'2008-01-01','北京','李','3000'
insert into @tb select 5,'2008-01-01','北京','王','150'
insert into @tb select 6,'2008-01-01','北京','王','210'select * from @tb tp
where [money] between 100 and 200
and exists (select 1 from @tb where dt=tp.dt and palce=tp.palce and person=tp.person and id<>tp.id and [money] between 100 and 200)
and not exists (select 1 from @tb where dt=tp.dt and palce=tp.palce and person=tp.person and not ([money] between 100 and 200))--结果
id dt palce person money
---------- ---------- ---------- ---------- ----------
1 2008-01-01 北京 张 110
2 2008-01-01 北京 张 120(所影响的行数为 2 行)
3楼的有bug,应该为:select * from @tb tp
where (
select count(1) from @tb where datediff(dd,dt,tp.dt)=0 and palce=tp.palce and person=tp.person and [money] between 100 and 200
)>=2 and [money] between 100 and 200
declare @tb table (id varchar(10),dt varchar(10),palce varchar(10),person varchar(10),[money] varchar(10))
insert into @tb select 1,'2008-01-01','北京','张','110'
insert into @tb select 2,'2008-01-01','北京','张','120'
insert into @tb select 3,'2008-01-01','北京','李','130'
insert into @tb select 4,'2008-01-01','北京','李','140'
insert into @tb select 5,'2008-01-01','北京','王','150'
insert into @tb select 6,'2008-01-01','北京','王','210'
insert into @tb select 7,'2008-01-01','北京','张','210'
select * from @tb tp
where (
select count(1) from @tb where datediff(dd,dt,tp.dt)=0 and palce=tp.palce and person=tp.person and [money] between 100 and 200
)>=2
5楼说的对,忘记看了。呵呵`select a.* from tb a,
(select convert(varchar(10),dt,120)dt,palce,person from tb
where money1>=100 and money1<=200
group by convert(varchar(10),dt,120),palce,person
having count(1)>1)b
where convert(varchar(10),a.dt,120)=b.dt and a.palce=b.palce and a.person=b.person