22,209
社区成员
发帖
与我相关
我的任务
分享
where ','+@str+',' like '%,'+APPLICATION_ID+',%'
where ','+@str+'," like '%,'+APPLICATION_ID+',%'
select distinct USE_LOG_CODE
from table_app_use_info t
where charindex(','+ltrim(APPLICATION_ID)+',',','+/*这个地方写你程序中获取的字符串*/+',')>0
group by USE_LOG_COD
having count(1) = (select count(1) from table_app_use_info where USE_LOG_CODE = t.USE_LOG_CODE);
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
这个不太懂.
--试试这个:
declare @str varchar(8000);
set @str = '26,27';
select distinct USE_LOG_CODE
from table_app_use_info t
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
group by USE_LOG_COD
having count(1) = (select count(1) from table_app_use_info where USE_LOG_CODE = t.USE_LOG_CODE);
--这个可以!
CREATE TABLE table_app_use_info
(
ID INT NOT NULL,
USE_LOG_CODE VARCHAR(100),--这个相当于外键
APPLICATION_ID INT
)
insert into table_app_use_info
select 45,'a1',27 union all
select 44,'a1',26 union all
select 37,'a2',27 union all
select 36,'a2',26 union all
select 10,'a3',20 union all
select 11,'a3',21 union all
select 12,'a3',24 union all
select 15,'a3',27 union all
select 41,'a4',27 union all
select 40,'a4',26 union all
select 39,'a4',25 union all
select 38,'a4',24
go
declare @str varchar(8000)
set @str = '26,27'
;with ach as
(
select USE_LOG_CODE,count(1) cnt,
(select count(1) from table_app_use_info where USE_LOG_CODE = t.USE_LOG_CODE) num
from table_app_use_info t
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
group by USE_LOG_CODE
)
select USE_LOG_CODE
from ach
where cnt = num and cnt = len(@str)-len(replace(@str,',',''))+1
drop table table_app_use_info
/************
USE_LOG_CODE
----------------------------------------------------------------------------------------------------
a1
a2
(2 行受影响)
CREATE TABLE table_app_use_info
(
ID INT NOT NULL,
USE_LOG_CODE VARCHAR(100),--这个相当于外键
APPLICATION_ID INT
)
insert into table_app_use_info
select 45,'a1',27 union all
select 44,'a1',26 union all
select 37,'a2',27 union all
select 36,'a2',26 union all
select 10,'a3',20 union all
select 11,'a3',21 union all
select 12,'a3',24 union all
select 15,'a3',27 union all
select 41,'a4',27 union all
select 40,'a4',26 union all
select 39,'a4',25 union all
select 38,'a4',24
go
declare @str varchar(8000)
set @str = '24,25,26,27'
;with ach as
(
select USE_LOG_CODE,count(1) cnt
from table_app_use_info
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
group by USE_LOG_CODE
)
select USE_LOG_CODE
from ach
where cnt = len(@str)-len(replace(@str,',',''))+1
drop table table_app_use_info
/*************
USE_LOG_CODE
----------------------------------------------------------------------------------------------------
a4
(1 行受影响)
declare @str varchar(8000)
set @str = '26,27'
select distinct USE_LOG_CODE
from table_app_use_info t
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
and (select count(1) from table_app_use_info where USE_LOG_CODE = t.USE_LOG_CODE) = len(@str)-len(replace(@str,',',''))+1
CREATE
TABLE table_app_use_info
(
ID INT NOT NULL,
USE_LOG_CODE VARCHAR(100),--这个相当于外键
APPLICATION_ID INT,
PRIMARY KEY (ID)
)
ENGINE=MyISAM DEFAULT CHARSET=utf8;
insert into table_app_use_info select 45,'a1',27
insert into table_app_use_info select 44 ,'a1', 26
insert into table_app_use_info select 37 ,'a2', 27
insert into table_app_use_info select 36 ,'a2', 26
insert into table_app_use_info select 10 ,'a3', 20
insert into table_app_use_info select 11 ,'a3', 21
insert into table_app_use_info select 12 ,'a3', 24
insert into table_app_use_info select 15 ,'a3', 27
insert into table_app_use_info select 41 ,'a4', 27
insert into table_app_use_info select 40 ,'a4', 26
insert into table_app_use_info select 39 ,'a4', 25
insert into table_app_use_info select 38 ,'a4', 24
;with t as
(
select USE_LOG_CODE,APPLICATION_ID=stuff((select ','+convert(varchar,APPLICATION_ID)
from table_app_use_info t2
where t2.USE_LOG_CODE=t1.USE_LOG_CODE
for xml path('')),1,1,'')
from table_app_use_info t1
group by USE_LOG_CODE
)
select * from t where APPLICATION_ID='24,25,26,27'
/*
USE_LOG_CODE APPLICATION_ID
-------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
a4 24,25,26,27
*/
--ID USE_LOG_CODE APPLICATION_ID table_app_use_info
declare @str varchar(8000)
set @str = '26,27'
select USE_LOG_CODE
from table_app_use_info
where charindex(','+ltrim(APPLICATION_ID)+',',','+@str+',')>0
group by USE_LOG_CODE
having count(distinct APPLICATION_ID) = len(@str)-len(replace(@str,',',''))+1