34,590
社区成员
发帖
与我相关
我的任务
分享
--Temp table for test
CREATE TABLE #t1(b1 varchar(10), b2 varchar(10), b3 varchar(10),b4 varchar(10),b5 varchar(10));
INSERT INTO #t1 VALUES('false','false','true','true','false'),('false','false','true','false','false'),('false','false','false','false','false')
,('true','true','true','false','false'),('true','true','true','false','true'),('true','true','true','true','true');
--列的数目. demo中为5 (b1~b5). 如题的实例则设置为1000
DECLARE @iCol INT = 5
--需要为true的列数需大于此数, domo中为2。如题的实例则设置为300
DECLARE @iTrueCount INT = 2
--CTE to concat all cols
;WITH CTE_T1 AS (
SELECT CAST(
(
SELECT (
SELECT T2.N.value('./text()[1]', 'varchar(max)')
FROM (
SELECT T.*
for xml path(''), type
) as T1(N)
cross apply T1.N.nodes('/*') as T2(N)
for xml path(''), type
).value('substring(./text()[1], 1)', 'varchar(max)')
for xml path(''), type
)
AS VARCHAR(8000)) AS Val,*
FROM #t1 as T
)
--Len('true')=4, Len('False')=5, 若都为false, 则总长度为5*@iCol, 没多一个true,总长度减1,所以长度差值即为true列的数目
SELECT *
FROM CTE_T1
WHERE LEN(Val) < 5*@iCol - @iTrueCount
--Cleaning
DROP TABLE #t1
--这个不用动态
--等待更好的
CREATE TABLE #T
(
A BIT,
B BIT,
C BIT,
D BIT,
E BIT
)
INSERT INTO #T VALUES(1,0,1,0,1),(0,0,1,0,1),(1,0,1,0,0)
WITH ct
AS
(
SELECT *,ROW_NUMBER() OVER(ORDER BY GETDATE())+1 AS id FROM #T
)
SELECT * FROM
(
SELECT *, (SELECT * FROM ct WHERE id=A.id FOR XML PATH('')) AS cpath FROM ct A
) A WHERE LEN(cpath)-LEN(REPLACE(cpath,'>1<','><'))=2
--等待更好的
CREATE TABLE #T
(
A BIT,
B BIT,
C BIT,
D BIT,
E BIT
)
INSERT INTO #T VALUES(1,0,1,0,1),(0,0,1,0,1),(1,0,1,0,0)
DECLARE @sql VARCHAR(max)=''
DECLARE @where VARCHAR(max)=''
SET @sql='SELECT * FROM #T WHERE 2=(select sum(value) from (select 0 As value {where}) T )'
SET @where=(SELECT ' union all select '+name FROM tempdb.sys.columns WHERE OBJECT_ID=object_id('tempdb..#T')FOR XML PATH(''))
SET @sql=REPLACE(@sql,'{where}',@where)
EXEC (@sql)
select * from your_table as yt inner join ( select id as id, col1+col2+...+col1000 as s from your_table ) as t on [yt.id] = [t.id] and t.s = 300