declare @s varchar(100) set @s='9,13,20,22' select * from book where charindex(','+bookid+',','+@s+',')>0
修正笔误 declare @s varchar(100) set @s='9,13,20,22' select * from book where charindex(','+bookid+',',','+@s+',')>0
use ProduceManageSystem; go if OBJECT_ID(N'K3_Produce_File_Manage',N'U') is not null drop table K3_Produce_File_Manage go if OBJECT_ID(N'Pro_Power',N'U') is not null drop table Pro_Power go create table Pro_Power ( id int primary key not null, fkey nvarchar(20) , person_seefkey nvarchar(500) , person_seename nvarchar(500) ) go create table K3_Produce_File_Manage ( id int primary key not null, name nvarchar(10), isfile int, pro_power_add nvarchar(20) ) --插入K3_Produce_File_Manage测试数据 insert into K3_Produce_File_Manage select 1,'text',0,'16' union all select 2,'text1',0,'16,18' union all select 3,'text11',0,'16,18' go --插入Pro_Power测试数据 insert into Pro_Power select 16,'dfd','1666','袁惠奖' union all select 18,'dfd','188','陈晓娟' select * from K3_Produce_File_Manage select * from Pro_Power ; with cte1 as ( select ATable.id,BTable.BID from ( select id, name,isfile,cast('<V>'+replace(pro_power_add,',','</V><V>')+'</V>' as xml)as ROW from K3_Produce_File_Manage )as ATable outer apply ( select C.value('.','int') As BID from ATable.ROW.nodes('/V') T(C) )as BTable ), cte2 as ( select cte1.id as FirstID,Pro_Power.* from Pro_Power inner join cte1 on cte1.BID=Pro_Power.id ) select distinct FirstID ,stuff((select ','+Pro_Power.person_seefkey from Pro_Power where Pro_Power.id in (select id from cte2 where cte2.FirstID=Acte2.FirstID)for XML path('')),1,1,'')as seefkey, stuff((select ','+Pro_Power.person_seename from Pro_Power where Pro_Power.id in (select id from cte2 where cte2.FirstID=Acte2.FirstID)for XML path('')),1,1,'')as seename from cte2 as Acte2 drop table K3_Produce_File_Manage drop table Pro_Power A表示数据:id name isfile pro_power_add ----------- ---------- ----------- -------------------- 1 text 0 16 2 text1 0 16,18 3 text11 0 16,18B表示数据:id fkey person_seefkey person_seename ----------- -------------------- -------------- -------------------- 16 dfd 1666 袁惠奖 18 dfd 188 陈晓娟结果为:FirstID seefkey seename ----------- ------------- ------------------------------------------ 1 1666 袁惠奖 2 1666,188 袁惠奖,陈晓娟 3 1666,188 袁惠奖,陈晓娟
如果bookid的类型是intdeclare @s varchar(100) set @s='9,13,20,22' select * from book where charindex(','+ltrim(bookid)+',',','+@s+',')>0--或者 declare @s varchar(100) set @s='9,13,20,22' select * from book where ','+@s+',' like '%,'+ltrim(bookid)+',%'
set @s='9,13,20,22'
select * from book where charindex(','+bookid+',','+@s+',')>0
declare @s varchar(100)
set @s='9,13,20,22'
select * from book where charindex(','+bookid+',',','+@s+',')>0
use ProduceManageSystem;
go
if OBJECT_ID(N'K3_Produce_File_Manage',N'U') is not null drop table K3_Produce_File_Manage
go
if OBJECT_ID(N'Pro_Power',N'U') is not null drop table Pro_Power
go
create table Pro_Power
(
id int primary key not null,
fkey nvarchar(20) ,
person_seefkey nvarchar(500) ,
person_seename nvarchar(500)
)
go
create table K3_Produce_File_Manage
(
id int primary key not null,
name nvarchar(10),
isfile int,
pro_power_add nvarchar(20)
)
--插入K3_Produce_File_Manage测试数据
insert into K3_Produce_File_Manage
select 1,'text',0,'16' union all
select 2,'text1',0,'16,18' union all
select 3,'text11',0,'16,18'
go
--插入Pro_Power测试数据
insert into Pro_Power
select 16,'dfd','1666','袁惠奖' union all
select 18,'dfd','188','陈晓娟'
select * from K3_Produce_File_Manage
select * from Pro_Power
;
with cte1 as
(
select ATable.id,BTable.BID from
(
select id, name,isfile,cast('<V>'+replace(pro_power_add,',','</V><V>')+'</V>' as xml)as ROW
from K3_Produce_File_Manage
)as ATable
outer apply
(
select
C.value('.','int') As BID
from ATable.ROW.nodes('/V') T(C)
)as BTable
),
cte2 as
(
select cte1.id as FirstID,Pro_Power.* from Pro_Power inner join cte1
on cte1.BID=Pro_Power.id
)
select distinct FirstID
,stuff((select ','+Pro_Power.person_seefkey from Pro_Power where Pro_Power.id in (select id from cte2 where cte2.FirstID=Acte2.FirstID)for XML path('')),1,1,'')as seefkey,
stuff((select ','+Pro_Power.person_seename from Pro_Power where Pro_Power.id in (select id from cte2 where cte2.FirstID=Acte2.FirstID)for XML path('')),1,1,'')as seename
from cte2 as Acte2
drop table K3_Produce_File_Manage
drop table Pro_Power
A表示数据:id name isfile pro_power_add
----------- ---------- ----------- --------------------
1 text 0 16
2 text1 0 16,18
3 text11 0 16,18B表示数据:id fkey person_seefkey person_seename
----------- -------------------- -------------- --------------------
16 dfd 1666 袁惠奖
18 dfd 188 陈晓娟结果为:FirstID seefkey seename
----------- ------------- ------------------------------------------
1 1666 袁惠奖
2 1666,188 袁惠奖,陈晓娟
3 1666,188 袁惠奖,陈晓娟
set @s='9,13,20,22'
select * from book where charindex(','+ltrim(bookid)+',',','+@s+',')>0--或者
declare @s varchar(100)
set @s='9,13,20,22'
select * from book where ','+@s+',' like '%,'+ltrim(bookid)+',%'
CHARINDEX(',' + RTRIM(NodeID) +',',',' + CAST(@ChildIDArray AS NVARCHAR(100)) +',') > 0 这句话的意思是把childidarray数组里的栏目数据全搜索出来吗?
select * from book where bookid “9,13,20,22” like '%'+bookid+'%'
CHARINDEX(',' + RTRIM(NodeID) +',',',' + CAST(@ChildIDArray AS NVARCHAR(100)) +',') > 0
这个可以写在一个SQL查询语句里,成功了,但不了解,能教我吗?了解后结贴!
'9,13,20,22' --> ',9,13,20,22,'
bookid = 9 --> ',9,'2.
CHARINDEX
返回字符串中指定表达式的起始位置。 语法
CHARINDEX ( expression1 , expression2 [ , start_location ] ) 参数
expression1一个表达式,其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。expression2一个表达式,通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。start_location在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location,而是一个负数或零,则将从 expression2 的起始位置开始搜索。返回类型
int注释
如果 expression1 或 expression2 之一属于 Unicode 数据类型(nvarchar 或 nchar)而另一个不属于,则将另一个转换为 Unicode 数据类型。如果 expression1 或 expression2 之一为 NULL 值,则当数据库兼容级别为 70 或更大时,CHARINDEX 返回 NULL 值。当数据库兼容级别为 65 或更小时,CHARINDEX 仅在 expression1 和 expression2 都为 NULL 时返回 NULL 值。 如果在 expression2 内没有找到 expression1,则 CHARINDEX 返回 0。示例
第一个代码示例返回序列"wonderful"在 titles 表的 notes 列中开始的位置。第二个示例使用可选的 start_location 参数从 notes 列的第五个字符开始寻找"wonderful"。第三个示例显示了当 expression2 内找不到 expression1 时的结果集。USE pubs
GO
SELECT CHARINDEX('wonderful', notes)
FROM titles
WHERE title_id = 'TC3218'
GO-- Use the optional start_location parameter to start searching
-- for wonderful starting with the fifth character in the notes
-- column.
USE pubs
GO
SELECT CHARINDEX('wonderful', notes, 5)
FROM titles
WHERE title_id = 'TC3218'
GO下面是第一个查询和第二个查询的结果集:-----------
46 (1 row(s) affected)USE pubs
GO
SELECT CHARINDEX('wondrous', notes)
FROM titles
WHERE title_id='TC3218'
GO下面是结果集。
-----------
0 (1 row(s) affected)
关键是CHARINDEX 函数,它能判断“加工过"的每个字段值中是否含有相关字符,并返回起始位置
求教CAST(@ChildIDArray AS NVARCHAR(100)) 是定义一个数组吗?上面那个charindex我都不知道参数是怎么分的,','+等符号真是晕了,太笨了我:(