--建立测试表 create table tb1(ID int,campaign varchar(10)) insert into tb1 select 1,'1,3,5' union all select 2,'4,5,6' union all select 3,'5' union all select 4,'1,6' union all select 5,'4,6' union all select 6,'5,4' union all select 7,'1,3,4,2,6'--查询数据--参加4\5\6活动两次以下(不包括未参加)的用户 select tb1.* From (select ID,','+campaign+',' campaign From tb1) a inner join tb1 on a.ID=tb1.ID where (a.campaign like '%,4,%' and a.campaign not like '%,5,%' and a.campaign not like '%,6,%') or (a.campaign like '%,5,%' and a.campaign not like '%,6,%' and a.campaign not like '%,4,%') or (a.campaign like '%,6,%' and a.campaign not like '%,4,%' and a.campaign not like '%,5,%')select ID From (select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,4,%' union all select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,5,%' union all select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,6,%' ) a group by ID having count(*)=1--参加4\5\6活动两次以上(包括两次)的用户 select tb1.* From (select ID,','+campaign+',' campaign From tb1) a inner join tb1 on a.ID=tb1.ID where (a.campaign like '%,4,%' and a.campaign like '%,5,%') or (a.campaign like '%,4,%' and a.campaign like '%,6,%') or (a.campaign like '%,5,%' and a.campaign like '%,6,%')select ID From (select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,4,%' union all select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,5,%' union all select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,6,%' ) a group by ID having count(*)>1--删除测试表 drop table tb1--查询结果 ID campaign ----------- ---------- 1 1,3,5 3 5 4 1,6(所影响的行数为 3 行)ID ----------- 1 3 4(所影响的行数为 3 行)ID campaign ----------- ---------- 2 4,5,6 5 4,6 6 5,4 7 1,3,4,2,6(所影响的行数为 4 行)ID ----------- 2 5 6 7(所影响的行数为 4 行)
谢谢Cityfire(青团子) 可是感觉语句太长了,有没有更简单的语句
Id campaign 1 1,3,5 2 4,5,6 3 5 4 1,6 其中ID是用户序号,campaign代表用户参加活动的标号 代表序号为1的用户参加了第一次、第三次、第五次活动. 现在要求找出在第四次、第五次、第六次活动中,参加两次以下的用户(SQL语句1); 参加两次以上的用户,其中包括两次(SQL语句2) ceate talbe t (Id int, num int) Insert t (id, num) select id, 0 From tb1 declare @min int, @max int select @min = min(id), @Max = Max(id) From t while @Min <= @Max begin Select * into #t1 From tb1 Where id = @Min And campaign like '%4%' if EXISTS (Select * From #t1) begin Updqte t set num = Num + 1 where id = @min drop table #t1 end Select * into #t1 From tb1 Where id = @Min And campaign like '%5%' if EXISTS (Select * From #t1) begin Updqte t set num = Num + 1 where id = @min drop table #t1 end Select * into #t1 From tb1 Where id = @Min And campaign like '%6%' if EXISTS (Select * From #t1) begin Updqte t set num = Num + 1 where id = @min drop table #t1 end Set @Min = @Min + 1 endSelect * From t where num < 2 Select * From t where num >= 2
to Cityfire(青团子) 我想我的楼主也一定说长了, 差不多。
自己写了一个 select * from tb1 where ((patindex('%4,5%')>0 or patindex('%4%6%')>0 or patindex('%5,6%')>0 ) 这是当参加活动有顺序的时候,既最新的活动在最后面当参加活动没顺序的时候,就麻烦了
谢 sankis() 这个只是一个大查询的一个条件,所以我要写的简单一点,还有没有更好一点的
to: Cityfire(青团子) 说的对,我马虎了
select * from tb1 a where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%') or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%') or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a where (a.campaign like '%4%' and a.campaign like '%5%') or (a.campaign like '%4%' and a.campaign like '%6%') or (a.campaign like '%5%' and a.campaign like '%6%')
如果要查出第4,5,6次活动都没参与的也包括在内,如下:select * from tb1 a where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%') or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%') or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%') or (a.campaign not like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a where (a.campaign like '%4%' and a.campaign like '%5%') or (a.campaign like '%4%' and a.campaign like '%6%') or (a.campaign like '%5%' and a.campaign like '%6%')
to lcq9732(明空) 41,6 4,61 41,5 4,51 51,6 5,61这些也满足你写的第二条语句了,但这些不是参加两次的数据
以下ID=8的数据查不到,如要查到ID = 8的那条记录可如上语句 --构造数据 create table tb1(ID int,campaign varchar(10)) insert into tb1 select 1,'1,3,5' union all select 2,'4,5,6' union all select 3,'5' union all select 4,'1,6' union all select 5,'4,6' union all select 6,'5,4' union all select 7,'1,3,4,2,6' union all select 8,'1,'select * from tb1 a where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%') or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%') or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a where (a.campaign like '%4%' and a.campaign like '%5%') or (a.campaign like '%4%' and a.campaign like '%6%') or (a.campaign like '%5%' and a.campaign like '%6%')--删除测试表 drop table tb1
lcq9732(明空) 你再改改就比我写的还长了
研究出来一个,不过要改库中的数据
查询之前:update set campaign='''+replace(campaign,',','|,|')+''' where campaign is not null select * from tb1 where (patindex('%|4|%|5|%')>0 or patindex('%|4|%|6|%')>0 or patindex('%|5|%|6|%')>0 ) or patindex('%|5|%|4|%')>0 or patindex('%|6|%|4|%')>0 or patindex('%|6|%|5|%')>0 )
查询之后: update set campaign=replace(campaign,'|','') where campaign is not null 不过活动范围是三次活动,如果是多次活动中选两次,就又麻烦了
错了: 查询之前:update set campaign='|'+replace(campaign,',','|,|')+'|' where campaign is not null
看来只能如此了 --构造数据 IF EXISTS(SELECT name FROM sysobjects WHERE name = N'tb1' ) DROP TABLE tb1 create table tb1(ID int,campaign varchar(10)) insert into tb1 select 1,'1,3,5' union all select 2,'4,5,6' union all select 3,'5' union all select 4,'1,6' union all select 5,'4,6' union all select 6,'5,4' union all select 7,'1,3,4,2,6' union all select 8,'41,52'select * from (select [ID] ,campaign = ',' + campaign + ',' from tb1 ) a where (a.campaign like '%,4,%' and a.campaign not like '%,5,%' and a.campaign not like '%,6,%') or (a.campaign like '%,5,%' and a.campaign not like '%,6,%' and a.campaign not like '%,4,%') or (a.campaign like '%,6,%' and a.campaign not like '%,4,%' and a.campaign not like '%,5,%')select * from (select [ID] ,campaign = ',' + campaign + ',' from tb1 ) a where (a.campaign like '%,4,%' and a.campaign like '%,5,%') or (a.campaign like '%,4,%' and a.campaign like '%,6,%') or (a.campaign like '%,5,%' and a.campaign like '%,6,%') --删除测试表 drop table tb1
--示例--示例数据 declare @t table(Id int,campaign varchar(10)) insert @t select 1,'1,3,5' union all select 2,'4,5,6' union all select 3,'5' union all select 4,'1,6'--查询(一条就够了) select * from @t where len(replace(campaign,',',''))- len(replace(replace(replace(replace(','+campaign+',',',4,',','),',5,',','),',6,',','),',','')) <2/*--测试结果Id campaign ----------- ---------- 1 1,3,5 3 5 4 1,6(所影响的行数为 3 行) --*/
--或者--示例数据 declare @t table(Id int,campaign varchar(10)) insert @t select 1,'1,3,5' union all select 2,'4,5,6' union all select 3,'5' union all select 4,'1,6'--查询(一条就够了) select * from @t where case charindex(',4,',','+campaign+',') when 0 then 0 else 1 end + case charindex(',5,',','+campaign+',') when 0 then 0 else 1 end + case charindex(',6,',','+campaign+',') when 0 then 0 else 1 end <2/*--测试结果Id campaign ----------- ---------- 1 1,3,5 3 5 4 1,6(所影响的行数为 3 行)--*/
--建立测试表
create table tb1(ID int,campaign varchar(10))
insert into tb1 select 1,'1,3,5'
union all select 2,'4,5,6'
union all select 3,'5'
union all select 4,'1,6'
union all select 5,'4,6'
union all select 6,'5,4'
union all select 7,'1,3,4,2,6'--查询数据--参加4\5\6活动两次以下(不包括未参加)的用户
select tb1.* From (select ID,','+campaign+',' campaign From tb1) a
inner join tb1 on a.ID=tb1.ID
where (a.campaign like '%,4,%' and a.campaign not like '%,5,%' and a.campaign not like '%,6,%')
or (a.campaign like '%,5,%' and a.campaign not like '%,6,%' and a.campaign not like '%,4,%')
or (a.campaign like '%,6,%' and a.campaign not like '%,4,%' and a.campaign not like '%,5,%')select ID From
(select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,4,%'
union all
select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,5,%'
union all
select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,6,%'
) a
group by ID
having count(*)=1--参加4\5\6活动两次以上(包括两次)的用户
select tb1.* From (select ID,','+campaign+',' campaign From tb1) a
inner join tb1 on a.ID=tb1.ID
where (a.campaign like '%,4,%' and a.campaign like '%,5,%')
or (a.campaign like '%,4,%' and a.campaign like '%,6,%')
or (a.campaign like '%,5,%' and a.campaign like '%,6,%')select ID From
(select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,4,%'
union all
select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,5,%'
union all
select * From (select ID,','+campaign+',' campaign From tb1) a where campaign like '%,6,%'
) a
group by ID
having count(*)>1--删除测试表
drop table tb1--查询结果
ID campaign
----------- ----------
1 1,3,5
3 5
4 1,6(所影响的行数为 3 行)ID
-----------
1
3
4(所影响的行数为 3 行)ID campaign
----------- ----------
2 4,5,6
5 4,6
6 5,4
7 1,3,4,2,6(所影响的行数为 4 行)ID
-----------
2
5
6
7(所影响的行数为 4 行)
1 1,3,5
2 4,5,6
3 5
4 1,6
其中ID是用户序号,campaign代表用户参加活动的标号
代表序号为1的用户参加了第一次、第三次、第五次活动.
现在要求找出在第四次、第五次、第六次活动中,参加两次以下的用户(SQL语句1); 参加两次以上的用户,其中包括两次(SQL语句2)
ceate talbe t (Id int, num int)
Insert t (id, num) select id, 0 From tb1
declare @min int, @max int
select @min = min(id), @Max = Max(id) From t
while @Min <= @Max
begin
Select * into #t1 From tb1 Where id = @Min And campaign like '%4%'
if EXISTS (Select * From #t1)
begin
Updqte t set num = Num + 1 where id = @min
drop table #t1
end
Select * into #t1 From tb1 Where id = @Min And campaign like '%5%'
if EXISTS (Select * From #t1)
begin
Updqte t set num = Num + 1 where id = @min
drop table #t1
end
Select * into #t1 From tb1 Where id = @Min And campaign like '%6%'
if EXISTS (Select * From #t1)
begin
Updqte t set num = Num + 1 where id = @min
drop table #t1
end
Set @Min = @Min + 1
endSelect * From t where num < 2
Select * From t where num >= 2
我想我的楼主也一定说长了,
差不多。
where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%')
or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%')
or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a
where (a.campaign like '%4%' and a.campaign like '%5%')
or (a.campaign like '%4%' and a.campaign like '%6%')
or (a.campaign like '%5%' and a.campaign like '%6%')
那
5, 4
6, 4
6, 5
这些呢?
要是只是一个
4,
5,
6,
怎么办呢?
如果要是还有
7,
8,
9呢?
where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%')
or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%')
or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')
or (a.campaign not like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a
where (a.campaign like '%4%' and a.campaign like '%5%')
or (a.campaign like '%4%' and a.campaign like '%6%')
or (a.campaign like '%5%' and a.campaign like '%6%')
4,61
41,5
4,51
51,6
5,61这些也满足你写的第二条语句了,但这些不是参加两次的数据
--构造数据
create table tb1(ID int,campaign varchar(10))
insert into tb1 select 1,'1,3,5'
union all select 2,'4,5,6'
union all select 3,'5'
union all select 4,'1,6'
union all select 5,'4,6'
union all select 6,'5,4'
union all select 7,'1,3,4,2,6'
union all select 8,'1,'select * from tb1 a
where (a.campaign like '%4%' and a.campaign not like '%5%' and a.campaign not like '%6%')
or (a.campaign like '%5%' and a.campaign not like '%6%' and a.campaign not like '%4%')
or (a.campaign like '%6%' and a.campaign not like '%4%' and a.campaign not like '%5%')select * from tb1 a
where (a.campaign like '%4%' and a.campaign like '%5%')
or (a.campaign like '%4%' and a.campaign like '%6%')
or (a.campaign like '%5%' and a.campaign like '%6%')--删除测试表
drop table tb1
你再改改就比我写的还长了
查询之前:update set campaign='''+replace(campaign,',','|,|')+''' where campaign is not null
select * from tb1 where (patindex('%|4|%|5|%')>0 or patindex('%|4|%|6|%')>0 or patindex('%|5|%|6|%')>0 ) or patindex('%|5|%|4|%')>0 or patindex('%|6|%|4|%')>0 or patindex('%|6|%|5|%')>0 )
查询之后:
update set campaign=replace(campaign,'|','') where campaign is not null 不过活动范围是三次活动,如果是多次活动中选两次,就又麻烦了
--构造数据
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'tb1'
)
DROP TABLE tb1
create table tb1(ID int,campaign varchar(10))
insert into tb1 select 1,'1,3,5'
union all select 2,'4,5,6'
union all select 3,'5'
union all select 4,'1,6'
union all select 5,'4,6'
union all select 6,'5,4'
union all select 7,'1,3,4,2,6'
union all select 8,'41,52'select * from (select [ID] ,campaign = ',' + campaign + ',' from tb1 ) a
where (a.campaign like '%,4,%' and a.campaign not like '%,5,%' and a.campaign not like '%,6,%')
or (a.campaign like '%,5,%' and a.campaign not like '%,6,%' and a.campaign not like '%,4,%')
or (a.campaign like '%,6,%' and a.campaign not like '%,4,%' and a.campaign not like '%,5,%')select * from (select [ID] ,campaign = ',' + campaign + ',' from tb1 ) a
where (a.campaign like '%,4,%' and a.campaign like '%,5,%')
or (a.campaign like '%,4,%' and a.campaign like '%,6,%')
or (a.campaign like '%,5,%' and a.campaign like '%,6,%')
--删除测试表
drop table tb1
declare @t table(Id int,campaign varchar(10))
insert @t select 1,'1,3,5'
union all select 2,'4,5,6'
union all select 3,'5'
union all select 4,'1,6'--查询(一条就够了)
select *
from @t
where len(replace(campaign,',',''))-
len(replace(replace(replace(replace(','+campaign+',',',4,',','),',5,',','),',6,',','),',',''))
<2/*--测试结果Id campaign
----------- ----------
1 1,3,5
3 5
4 1,6(所影响的行数为 3 行)
--*/
但是我说了这是一个大查询里的一个条件,如果用你的语句,相当于又多一个表,本来就有几个表联接查询,再多一个速度就更慢了
declare @t table(Id int,campaign varchar(10))
insert @t select 1,'1,3,5'
union all select 2,'4,5,6'
union all select 3,'5'
union all select 4,'1,6'--查询(一条就够了)
select *
from @t
where case charindex(',4,',','+campaign+',') when 0 then 0 else 1 end
+ case charindex(',5,',','+campaign+',') when 0 then 0 else 1 end
+ case charindex(',6,',','+campaign+',') when 0 then 0 else 1 end
<2/*--测试结果Id campaign
----------- ----------
1 1,3,5
3 5
4 1,6(所影响的行数为 3 行)--*/