数据库：
我有2张桌子。 Table1有 50 个字段，其中 25 个都命名为 SomeValue1 、SomeValue2 ... SomeValue25 . Table2是相似的，只是范围从 SomeOtherValue1 到 SomeOtherValue30 .
1(对于这两个表，这些"编号"列允许null。
2(对于这两个表，这些"编号"列应仅在未null之前的所有列时才允许一个值。因此，只有在SomeValue1和SomeValue2都未null的情况下，您才能在SomeValue3中输入值。
问题：
我正在尝试从两个表中查找所有记录，其中给定记录具有 n 个以上未null字段。
不幸的是，我不能相信数据的完整性。我根本无法SELECT * FROM Table WHERE SomeValueN IS NOT NULL因为我无法保证执行上面的规则#2。
问题：
有没有办法在不手动输入每个列名的情况下执行类似 SELECT MyPrimaryKey FROM Table WHERE COUNT([all_columns with null values]) > n 的操作？
使用 cross apply(values ()) 和聚合并包含having count()...进行动态反透视
测试设置是一个包含 25 个以 SomeValue 开头的可为 null 列的表：
create table t (id int not null identity(1,1) , SomeValue1 int null , SomeValue2 int null , SomeValue3 int null , SomeValue4 int null , SomeValue5 int null , SomeValue6 int null , SomeValue7 int null , SomeValue8 int null , SomeValue9 int null , SomeValue10 int null , SomeValue11 int null , SomeValue12 int null , SomeValue13 int null , SomeValue14 int null , SomeValue15 int null , SomeValue16 int null , SomeValue17 int null , SomeValue18 int null , SomeValue19 int null , SomeValue20 int null , SomeValue21 int null , SomeValue22 int null , SomeValue23 int null , SomeValue24 int null , SomeValue25 int null )
insert into t values
(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
,(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, null)
,(3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, null, null)
,(4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4, null, null, null)
,(5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, null, null, null, null)
,(6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, 6, null, null, null, null, null)
,(7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, 7, null, null, null, null, null, null)
,(8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, 8, null, null, null, null, null, null, null)
,(9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, null, null, null, null, null, null, null, null)
,(10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, null, null, null, null, null, null, null, null, null)
,(11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, 11, null, null, null, null, null, null, null, null, null, null)
,(12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, 12, null, null, null, null, null, null, null, null, null, null, null)
,(13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, 13, null, null, null, null, null, null, null, null, null, null, null, null)
,(14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, 14, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(16, 16, 16, 16, 16, 16, 16, 16, 16, 16, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(17, 17, 17, 17, 17, 17, 17, 17, 17, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(18, 18, 18, 18, 18, 18, 18, 18, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(19, 19, 19, 19, 19, 19, 19, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(20, 20, 20, 20, 20, 20, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(21, 21, 21, 21, 21, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(22, 22, 22, 22, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(23, 23, 23, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(24, 24, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
,(25, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
查询：
declare @cols as nvarchar(max);
declare @sql as nvarchar(max);
set @cols = stuff((
select ',(''' + C.name + ''','+ quotename(C.name) +')'
from sys.columns c
where c.object_id = object_id('dbo.t') /* <-- table name */
and c.name like 'SomeValue%' /* <-- column name starts with */
order by c.column_id
for xml path (''), type).value('.','nvarchar(max)')
,1,1,'');
set @sql = '
select t.id --, u.column_name, u.column_value
from t
cross apply (
values '+@cols+') u (column_name,column_value)
group by t.id
having count(u.column_value) > 10;' /* <-- how many not null */
select @sql as CodeGenerated;
exec sp_executesql @sql;
Rextester 演示：http://rextester.com/UKFM26061
生成的代码：
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| CodeGenerated | |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
| | |
| select t.id --, u.column_name, u.column_value | |
| from t | |
| cross apply ( | |
| values ('SomeValue1',[SomeValue1]),('SomeValue2',[SomeValue2]),('SomeValue3',[SomeValue3]),('SomeValue4',[SomeValue4]),('SomeValue5',[SomeValue5]),('SomeValue6',[SomeValue6]),('SomeValue7',[SomeValue7]),('SomeValue8',[SomeValue8]),('SomeValue9',[SomeValue9]),('SomeValue10',[SomeValue10]),('SomeValue11',[SomeValue11]),('SomeValue12',[SomeValue12]),('SomeValue13',[SomeValue13]),('SomeValue14',[SomeValue14]),('SomeValue15',[SomeValue15]),('SomeValue16',[SomeValue16]),('SomeValue17',[SomeValue17]),('SomeValue18',[SomeValue18]),('SomeValue19',[SomeValue19]),('SomeValue20',[SomeValue20]),('SomeValue21',[SomeValue21]),('SomeValue22',[SomeValue22]),('SomeValue23',[SomeValue23]),('SomeValue24',[SomeValue24]),('SomeValue25',[SomeValue25])) u (column_name,column_value) | |
| group by t.id | |
| having count(u.column_value) > 10; | |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--+
查询执行返回：
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+----+