62,074
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE a(x INT IDENTITY(1,1),a1 INT, a2 INT, a3 INT, a4 INT, a5 INT)
INSERT dbo.a
SELECT 1,2,3,4,5
INSERT dbo.a
SELECT 5,4,3,2,1
--插入两条记录
SELECT *
FROM dbo.a
WHERE STR(a1) + STR(a2) + STR(a3) + STR(a4) + STR(a5) LIKE '%1%3%5%'
--如果a1到a5从小到大排列这样就行
/*
x a1 a2 a3 a4 a5
----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 4 5
(1 行受影响)
*/
SELECT m1.*
FROM dbo.a m1
WHERE CHARINDEX('1', STR(a1) + STR(a2) + STR(a3) + STR(a4) + STR(a5)) > 0
AND EXISTS ( SELECT 1
FROM dbo.a m2
WHERE m2.x = m1.x
AND CHARINDEX('3',
STR(a1) + STR(a2) + STR(a3) + STR(a4)
+ STR(a5)) > 0
AND EXISTS ( SELECT 1
FROM dbo.a m3
WHERE m3.x = m2.x
AND CHARINDEX('5',
STR(a1) + STR(a2)
+ STR(a3)
+ STR(a4)
+ STR(a5)) > 0 ) )
--a1-a5无序排列
/*
x a1 a2 a3 a4 a5
----------- ----------- ----------- ----------- ----------- -----------
1 1 2 3 4 5
2 5 4 3 2 1
(2 行受影响)
*/
DROP TABLE dbo.a
你懂的