我试图将作为列值的一部分存在的tblFactorDefinition
列名与具有实际值的匹配列名进行比较,tblConstituent
以找出这两个表之间的差异。
即使确实存在差异,我也没有得到预期的输出。我在下面发布了带有预期输出的数据库架构和示例数据:
下面的三个表是tblFactorDefinition
,tblConstituent
和tblFamily
:
FamilyID | FieldName | FactorDefinition | PropertyTypeID
---------+------------+--------------------+----------------
10216 | Factor1 | 901 | 300
10216 | Factor2 | 901 | 305
10216 | Factor3 | 901 | 310
ConstituentID | FamilyID | ListingID | Factor1 | Factor2 | Factor3 | Factor9
--------------+----------+------------+---------+---------+---------+---------
1101 | 10216 | 1 | 0.1 | NULL | 0.5 | 1.0
1105 | 10216 | 2 | 0.1 | 0.3 | 0.5 | 1.0
1108 | 10216 | 5 | 0.45 | 0.42 | NULL | 1.0
FamilyID | OpenDate
---------+------------
10216 | 2016-05-16
预期输出如下所示:
FamilyID | FieldName | ConstituentID
----------+--------------+---------------
10216 | Factor2 | 1101
10216 | Factor3 | 1108
这是查询,我没有得到正确的逻辑,因此它什么都不返回。
SELECT
T.FamilyID,
C.COLUMN_NAME,
T.ConstituentID
FROM
SolaDBServer..tblConstituent T
INNER JOIN
INFORMATION_SCHEMA.COLUMNS C ON T.FamilyID = C.COLUMN_NAME
AND C.TABLE_NAME = 'tblFactorDefinition'
AND T.FamilyID = 10216
LEFT OUTER JOIN
SolaDBServer..tblConstituent tc ON tc.FamilyID = T.FamilyID
INNER JOIN
SolaDBServer..tblFamily tf ON tf.FamilyID = tc.FamilyID
AND tf.OpenDate = CAST(GETDATE() AS DATE)
WHERE
C.COLUMN_NAME = 'FieldName'
对此有任何帮助吗?
谢谢。
您可以使用UNPIVOT
请注意我在查询中使用了IIF函数。(需要SQL Server 2012或以上版本)
如果您使用旧版本,请使用 case 语句替换它们。
试试这个:
select a.FamilyID,a.FieldName,a.ConstituentID from
(
select FamilyID,FieldName, ConstituentID, indicator
from
(select c.ConstituentID,c.FamilyID
,iif(factor1 is null,1,0) as Factor1 --indicator for null
,iif(factor2 is null,1,0) as Factor2
,iif(factor3 is null,1,0) as Factor3
,iif(factor9 is null,1,0) as Factor9
from tblConstituent c
join tblFamily f
on f.FamilyID = c.FamilyID
where f.OpenDate = cast (getdate() as date)
)p
unpivot
(Indicator for FieldName
in ([Factor1],[Factor2],[Factor3],[Factor9])
) as unpvt
) a
join tblFactorDefinition b --check if their factor(s) exist for specific ID
on a.FamilyID = b.FamilyID
and a.FieldName = b.FieldName
where a.Indicator = 1
测试结果(我用不同的 FamilyID 又添加了一行):
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句