22,210
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE [dbo].[Places] (
[ID] int NULL,
[placeName] nvarchar(50) NULL
)
ON [PRIMARY]
GO
BEGIN TRANSACTION
GO
INSERT INTO [dbo].[Places] VALUES ('1', N'马街');
INSERT INTO [dbo].[Places] VALUES ('2', N'鸡街');
INSERT INTO [dbo].[Places] VALUES ('3', N'狗街');
GO
COMMIT
GO
CREATE TABLE [dbo].[Lines] (
[ID] int NULL,
[lineName] nvarchar(40) NULL,
[placeIDs] nvarchar(100) NULL
)
ON [PRIMARY]
GO
BEGIN TRANSACTION
GO
INSERT INTO [dbo].[Lines] VALUES ('1', N'马街=鸡街=狗街', '1,2,3');
INSERT INTO [dbo].[Lines] VALUES ('2', N'鸡街=狗街', '2,3');
INSERT INTO [dbo].[Lines] VALUES ('3', N'狗街', '3');
GO
COMMIT
GO
SELECT *
FROM Lines
WHERE ','+placeIDs+',' LIKE '%,2,%'
AND ','+placeIDs+',' LIKE '%,3,%'
SELECT *
FROM dbo.Lines
WHERE placeIDs LIKE '%' + ( SELECT STUFF(( SELECT ',' + RTRIM(ID)
FROM dbo.Places
WHERE ID IN ( 2, 3 )
FOR
XML PATH('')
), 1, 1, '')
) + '%'