表结构:
id mid_id pro_id
1 1 1
2 2 1
3 3 1
4 1 2
5 3 2
6 1 3
7 4 3
条件mid_id 1 要查出 pro_id 1 2 3
条件mid_id 1 2 要查出 pro_id 1
条件mid_id 1 3 要查出 pro_id 1 2
条件mid_id 4 要查询 pro_id 3
id mid_id pro_id
1 1 1
2 2 1
3 3 1
4 1 2
5 3 2
6 1 3
7 4 3
条件mid_id 1 要查出 pro_id 1 2 3
条件mid_id 1 2 要查出 pro_id 1
条件mid_id 1 3 要查出 pro_id 1 2
条件mid_id 4 要查询 pro_id 3
set @s='1 2 3'
select pro_id from tb where charindex(cast(pro_id as varchar(10)),@s)>0
set @s='1 2 3'
select pro_id from tb where charindex(cast(mid_id as varchar(10)),@s)>0
xiugai
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,mid_id int,pro_id int)
go
insert into tb
select
1 , 1 , 1 union all select
2 , 2 , 1 union all select
3 , 3 , 1 union all select
4 , 1 , 2 union all select
5 , 3 , 2 union all select
6 , 1 , 3 union all select
7 , 4 , 3
go
declare @s varchar
set @s='1 2 3'
select pro_id from tb where charindex(cast(mid_id as varchar(10)),@s)>0
/*------------
pro_id
-----------
1
2
3-------*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,mid_id int,pro_id int)
go
insert into tb
select
1 , 1 , 1 union all select
2 , 2 , 1 union all select
3 , 3 , 1 union all select
4 , 1 , 2 union all select
5 , 3 , 2 union all select
6 , 1 , 3 union all select
7 , 4 , 3
go
CREATE FUNCTION dbo.f_tb(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str =''
SELECT @str = @str + ',' + rtrim(mid_id) FROM tb WHERE pro_id=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
declare @s varchar(10)
set @s='1,2,3'
select pro_id
from tb
where CHARINDEX(@s,dbo.f_tb(pro_id ))<>0
group by pro_id
/*
pro_id
-----------
1
*/
/*------------
pro_id
-----------
1
2
3-------*/
条件mid_id 1 2 要查出 pro_id 1
条件mid_id 4 要查询 pro_id 3
都通过了但是
条件mid_id 1 3 要查出 pro_id 1 2
只查到了2
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,mid_id int,pro_id int)
go
insert into tb
select
1 , 1 , 1 union all select
2 , 2 , 1 union all select
3 , 3 , 1 union all select
4 , 1 , 2 union all select
5 , 3 , 2 union all select
6 , 1 , 3 union all select
7 , 4 , 3 go
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
declare @s varchar(10)
set @s='1 3'--每个数字间一个空格
select pro_id
from(
select * from dbo.f_splitSTR(@s,' ')) t join tb on t.col=tb.mid_id
group by pro_id
having COUNT(*)=LEN(@s)/2+1/*
pro_id
-----------
1
2*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,mid_id int,pro_id int)
go
insert into tb
select
1 , 1 , 1 union all select
2 , 2 , 1 union all select
3 , 3 , 1 union all select
4 , 1 , 2 union all select
5 , 3 , 2 union all select
6 , 1 , 3 union all select
7 , 4 , 3 go
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
GO
declare @s varchar(10)
set @s='1 2 3'--每个数字间一个空格 每2个数字之间隔一个空格
select pro_id
from(
select * from dbo.f_splitSTR(@s,' ')) t join tb on t.col=tb.mid_id
group by pro_id
having COUNT(*)=LEN(@s)/2+1/*
pro_id
-----------
1
*/
DROP TABLE tb
GO
CREATE TABLE tb(id int,mid_id int,pro_id int)
go
insert into tb
select
1 , 1 , 1 union all select
2 , 2 , 1 union all select
3 , 3 , 1 union all select
4 , 1 , 2 union all select
5 , 3 , 2 union all select
6 , 1 , 3 union all select
7 , 4 , 3
GODECLARE @s VARCHAR(20)
SET @s='1 3'
SELECT pro_id
FROM tb
WHERE CHARINDEX(' ' + RTRIM(mid_id) + ' ' , ' ' + @s + ' ')>0
GROUP BY pro_id
HAVING COUNT(DISTINCT mid_id) = LEN(@s)+1-LEN(REPLACE(@s,' ',''))
/*
1
2
*/SELECT DISTINCT pro_id
FROM tb a
WHERE (
SELECT COUNT(DISTINCT
CASE
WHEN REPLACE(' ' + @s + ' ',' ' + RTRIM(mid_id) + ' ','') = ' '+@s+' ' THEN
NULL
ELSE
REPLACE(' ' + @s + ' ',' ' + RTRIM(mid_id) + ' ','')
END
)
FROM tb
WHERE pro_id = a.pro_id
)
=
LEN(@s)+1-LEN(REPLACE(@s,' ',''))
/*
1
2
*/