Class 表
TITLE RootID arrChildID
aaaa 1 1,4,5,6,
bbb 2 2,126,127,128,129现在知道栏目ID,要求根据栏目ID在arrChildID的情况获得父栏目 RootID 的值,如何办呢?
比如知道当前栏目classID=4 那么就能获得父栏目ROOTID=1Select RootID from Class where arrChildID in(4) 不正确
Select RootID from Class where 4 in (arrChildID) 不正确
TITLE RootID arrChildID
aaaa 1 1,4,5,6,
bbb 2 2,126,127,128,129现在知道栏目ID,要求根据栏目ID在arrChildID的情况获得父栏目 RootID 的值,如何办呢?
比如知道当前栏目classID=4 那么就能获得父栏目ROOTID=1Select RootID from Class where arrChildID in(4) 不正确
Select RootID from Class where 4 in (arrChildID) 不正确
Select RootID from Class where arrChildID in replac(arrChildId,',',''',''')
[/Quote]
Select RootID from Class where arrChildID in ''''+replac(arrChildId,',',''',''')+''''不好意思
-- Author : htl258(Tony)
-- Date : 2010-05-03 13:53:41
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [Class]
IF OBJECT_ID('[Class]') IS NOT NULL
DROP TABLE [Class]
GO
CREATE TABLE [Class] ([TITLE] [nvarchar](10),[RootID] [int],[arrChildID] [nvarchar](20))
INSERT INTO [Class]
SELECT 'aaaa','1','1,4,5,6' UNION ALL
SELECT 'bbb','2','2,126,127,128,129'--SELECT * FROM [Class]-->SQL查询如下:
SELECT RootID FROM Class WHERE ','+[arrChildID]+',' LIKE '%,4,%'
--或者:
SELECT RootID FROM Class WHERE CHARINDEX(',4,',','+[arrChildID]+',')>0
/*
RootID
-----------
1(1 行受影响)
*/
SELECT RootID FROM Class WHERE ","&[arrChildID]&"," LIKE "*,4,*"ACCESS语法不是很熟,你用这样试试先