表tlogs(消费记录表)有如下记录: 结果:
dttime operator cardid 222
2006-4-1 001 111 333
2006-4-2 007 222
2006-4-1 008 333
2006-4-5 002 111
2006-4-1 007 333
2006-4-4 008 111
2006-5-1 008 222要求把4月份operator全部是007或008那消费的卡号找出来
如上面的表应该把 222, 333找出来我用的
select scardid
from tlogs
where month(dttime1)=4 and soperator in('007','008')group by scardid
语句,没有成功,它把只要4月份一次在007或008那消费就找出来了,可我要的不是这样的结果,我要的是4月份全部都是在007或008那消费的才找出来
请大家帮帮忙。
dttime operator cardid 222
2006-4-1 001 111 333
2006-4-2 007 222
2006-4-1 008 333
2006-4-5 002 111
2006-4-1 007 333
2006-4-4 008 111
2006-5-1 008 222要求把4月份operator全部是007或008那消费的卡号找出来
如上面的表应该把 222, 333找出来我用的
select scardid
from tlogs
where month(dttime1)=4 and soperator in('007','008')group by scardid
语句,没有成功,它把只要4月份一次在007或008那消费就找出来了,可我要的不是这样的结果,我要的是4月份全部都是在007或008那消费的才找出来
请大家帮帮忙。
Select scardid
from tlogs A
where month(dttime1)=4 and Not Exists(Select * From tlogs Where cardid=A.cardid And month(dttime1)=4 And operator<>'007' And operator<>'008' )
insert into @t values('2006-4-1','001',111)
insert into @t values('2006-4-2','007',222)
insert into @t values('2006-4-1','008',333)
insert into @t values('2006-4-5','002',111)
insert into @t values('2006-4-1','007',333)
insert into @t values('2006-4-4','008',111)
insert into @t values('2006-5-1','008',222)
select
a.scardid
from
@t a
where
month(a.dttime1)=4
and
not exists(select 1 from @t where month(a.dttime1)=4 and scardid=a.scardid and soperator not in('007','008'))
group by
a.scardid
/*
scardid
-----------
222
333
*/
create table tlogs
(
dttime varchar(12),
operator varchar(5),
cardid varchar(5)
)insert into tlogs select '2006-4-1', '001', '111'
insert into tlogs select '2006-4-2', '007', '222'
insert into tlogs select '2006-4-1', '008', '333'
insert into tlogs select '2006-4-5', '002', '111'
insert into tlogs select '2006-4-1', '007', '333'
insert into tlogs select '2006-4-4', '008', '111'
insert into tlogs select '2006-5-1', '008', '222'
--语句
select cardid
from tlogs
where left(dttime,6) = '2006-4'
and cardid not in (select distinct cardid from tlogs where operator <> '007' and operator <> '008' and left(dttime,6) = '2006-4')
group by cardid
having count(1) <= 2--结果
222
333
select *
from tlogs
where month(dttime1)=4 and soperator in('007','008')
你语句运行成功了,但是有个小问题就就出来的卡号有重复的,我做了一个小修改就可以了
Select distinct scardid
from tlogs A
where month(dttime1)=4 and Not Exists(Select * From tlogs
Where scardid=A.scardid And month(dttime1)=4 And soperator<>'007' And soperator<>'008' )
在此感谢你的帮助