sql - SQL中如何优雅地比较同一个表中的值

Question

我有一个设置表，其中“category”确定不同的产品，“varname”是变量名称，“info”是设置的值。

例如，

select top 6 category, varname, info 
from settings_table 
where NODE_NAME='HTT-COMM-A' 
  and section='Module Settings' 
  and category in  ('ProductA', 'ProductB') 
order by varname

结果:

 category   varname             info   ProductB   WEB_ACCESS_ALLOW    NO   ProductA   WEB_ACCESS_ALLOW    NO   ProductB   WEB_ACCESS_BLOCK    YES   ProductA   WEB_ACCESS_BLOCK    YES   ProductB   WEB_ACCOUNT_DETAIL  NO   ProductA   WEB_ACCOUNT_DETAIL  YES  

I'd like to generate a simple list of differences between the values when category='ProductA' and 'ProductB'. I can think of a number of ways to do this with a temporary table, or by a number of subselects (for instance, this painful one) :

select a.category, a.varname, a.info , b.category, b.info 
from (select category, varname, info, description
      from settings_table 
      where category = 'ProductA') as a,
     (select category, varname,info, description 
      from settings_table 
      where category = 'ProductB') as b 
where a.varname=b.varname and a.info != b.info

但是当 b 中有一个不在 a 中的变量名时，上述方法(至少)会失败。 (任何解决方案都应该解决这个问题，a 和 b 之间的变量名的任何差异也应该被表示出来。)

这不是一个很难以笨拙的方式解决的问题，但我想知道是否有一种“正确的方法”可以优雅地做到这一点，没有可怕的子选择或没有上面的警告。

这与 SQL 无关，但此特定表位于 MSSQL 服务器中。

谢谢，Rk

Answer 1

如果您只关心 varname 和 info 值，您可以这样做:

Select varname, info
From @Data As T
Except  (
        Select varname, info
        From @Data As T1
        Where category = 'ProductA'
        Intersect
        Select varname, info
        From @Data As T2
        Where category = 'ProductB'
        )

如果你想要源表中的其他列，那么你可以这样做:

Select T.*
From settings_table As T
    Left Join   (
                Select T1.varname, T1.info
                From settings_table As T1
                Where T1.category = 'ProductA'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                Intersect
                Select T2.varname, T2.info
                From settings_table As T2
                Where T1.category = 'ProductB'
                    And T1.NODE_NAME='HTT-COMM-A' 
                    And T1.section='Module Settings'
                ) As Z
        On Z.varname = T.varname
            And Z.info = T.info
Where Z.varname Is Null
    And T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'

还有第三种方法是简单地使用 EXISTS 谓词:

Select T.*
From settings_table As T
Where T.NODE_NAME='HTT-COMM-A' 
    And T.section='Module Settings'
    And Not Exists  (
                    Select 1
                    From settings_table As T2
                    Where T2.category In('ProductA','ProductB')
                        And T2.varname = T.varname
                        And T2.info = T.info
                    Group By T2.varname, T2.info
                    Having Count(*) = 2
                    )