----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-24 12:46:51
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[postlevelIDs] varchar(5))
insert [huang]
select 1,'[3]' union all
select 2,'[3,5]' union all
select 3,'[2,6]' union all
select 4,'[]'
--------------开始查询--------------------------select * from [huang]
WHERE CHARINDEX(','+'3'+',', ','+SUBSTRING([postlevelIDs],2,LEN([postlevelIDs])-2)+',')>0
----------------结果----------------------------
/*
ID postlevelIDs
----------- ------------
1 [3]
2 [3,5]*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-01-24 12:46:51
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([ID] int,[postlevelIDs] varchar(5))
insert [huang]
select 1,'[3]' union all
select 2,'[3,5]' union all
select 3,'[2,6]' union all
select 4,'[]'
--------------开始查询--------------------------select * from [huang]
WHERE CHARINDEX(','+'3'+',', ','+SUBSTRING([postlevelIDs],2,LEN([postlevelIDs])-2)+',')>0
----------------结果----------------------------
/*
ID postlevelIDs
----------- ------------
1 [3]
2 [3,5]*/
解决方案 »
- 子表和父表E-R图
- sql2005 设置默认值,怎么操作?
- MS SQL SERVER 2000的视图问题
- SQL Mail 发超链接的问题。
- 请问,关于SQL server 连接时 Provider=SQLOLEDB.1 的问题
- mssql一个很奇怪的问题,如何解决啊!“键列信息不足或不正确,更新影响到多行”
- 在线急求,SQL复制的问题
- 如何用存储过程实现插入乱字符
- 关于sql server2000数据库模糊查询的问题,pb6.5+sql server2000,高手们请进。
- vb.net连接mysql出现提示错误'MySqlConnection' is ambiguous in the namespace 'MySql.Data.M
- 求存储过程!!
- 聚合函数count里面加条件
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[postlevelIDs] varchar(5))
insert [tb]
select 1,'[3]' union all
select 2,'[3,5]' union all
select 3,'[2,6]' union all
select 4,'[]'
goselect *
from tb
where CHARINDEX(','+'3'+',' , ','+replace(REPLACE([postlevelIDs],'[',''),']','')+',') > 0
/*
ID postlevelIDs
1 [3]
2 [3,5]
*/
select * from tb where postlevelIDs like '%3%'
like足矣你不会活用?
like '%3%'
其中postlevelIDs由 postlevelID 通过','号隔开,前后用[]号包括起来
请问怎样查询出比如postlevelIDs包含 3的记录?
4存在几种可能?'[3,' 一种 ',3,' 一种 ',3]' 一种[3]一种
4种可能 写or 4次like 不足矣?