English 中文(简体)
• 如何将同一表格中的数值进行比较
原标题:How to compare values within the same table in TSQL elegantly

我先提出一个环境表,其类型是确定不同产品,其名称是变称,其价值为环境。

例如,

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  

d 我只想简单列出产品A和产品B类数值之间的差别。 我可以想出一些办法,用临时表格或一些次选(例如,这一痛苦的表)来做到这一点:

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

但上述方法(至少)如果在一栏中点上出现var号,就失败了。 (任何解决办法都应解决这个问题,每一国名之间和(b)之间的任何差别也应得到代表。)

这是一个难以以干.方式解决的难题,但我很想知道,是否有一个正确的方式来做这项工作,没有马上的次选,或者没有上述告诫。

这更多是空洞的,但这一特殊表格属于中型散射设备服务器。

Thanks, Rk

最佳回答

如果你只关心var名和胎儿的价值观,你可以做以下事情:

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
                    )
问题回答

你们可以自谋生计:

select a.varname as varname,
a.info as  ProductA_Setting ,
b.info as  ProductB_Setting 
from @t a
inner join @t b
on a.varname = b.varname
where a.category =  ProductA 
and b.category =  ProductB 
and a.info <> b.info

这里使用的文字是:

declare @t table (category varchar(32), varname varchar(32), info varchar(32))

insert into @t select  ProductB ,  WEB_ACCESS_ALLOW ,  NO 
insert into @t select  ProductA ,  WEB_ACCESS_ALLOW ,  NO 
insert into @t select  ProductB ,  WEB_ACCESS_BLOCK ,  YES 
insert into @t select  ProductA ,  WEB_ACCESS_BLOCK ,  YES 
insert into @t select  ProductB ,  WEB_ACCOUNT_DETAIL ,  NO 
insert into @t select  ProductA ,  WEB_ACCOUNT_DETAIL ,  YES 

select * from @t

select a.varname as varname,
a.info as  ProductA_Setting ,
b.info as  ProductB_Setting 
from @t a
inner join @t b
on a.varname = b.varname
where a.category =  ProductA 
and b.category =  ProductB 
and a.info <> b.info

我想,你们想利用爱情会,并完全加入进来。

WITH SETTINGS (category, varname, info)
AS
(
    SELECT category, varname, info
    FROM settings_table
    WHERE NODE_NAME =  HTT-COMM-A 
        AND [section] =  Module Settings 
        AND category IN ( ProductA ,  ProductB )
)
SELECT
    COALESCE(A.varname, B.varname) AS varname,
    A.info AS info_a,
    B.info AS info_b
FROM
    SETTINGS A
    FULL OUTER JOIN SETTINGS B
        ON A.category =  ProductA 
            AND B.category =  ProductB 
            AND A.varname = B.varname
WHERE
    A.varname IS NULL
    OR B.varname IS NULL    
    OR A.info!= B.info
ORDER BY
    COALESCE(A.varname, B.varname)

SINET EXCEPT and SlectT... 国际电离层电离层电离层电离层总能成为我的书本,但该书必然使密码中子或更容易读,而我所写的版本仍然含有子序列。

根据的时间安排 Paul Kearney - pk ,我来:

DECLARE
  @Category1 varchar(32)
 ,@Category2 varchar(32)

SET @Category1 =  ProductA 
SET @Category2 =  ProductB 

SELECT isnull(set1.varname, set2.varname) varname, set1.Category, set1.Info, set2.Category, set2.Info
 from (--  Exists for "1" but not for "2"
       select @Category1 Category, varname, info
        from @t
        where category = @Category1
       except select @Category1, varname, info
        from @t
        where category = @Category2) set1
  full outer join (--  Exists for "2" but not for "1"
                   select @Category2 Category, varname, info
                     from @t
                     where category = @Category2
                    except select @Category2, varname, info
                     from @t
                     where category = @Category1) set2
   on set2.varname = set1.varname

整个外岛都赶走了缺失的囚室,你最后在“FunL”类和“Info”栏中看到一些UNL。

You ve发现了实体-Attribute-Value数据模型的许多问题之一。 对方案制定者来说,这一模式非常引人瞩目的......它使你们充满了方便和简单的承诺。 “Look,我可以增加一个没有DDL的新环境。 谁也是冷却。 但是,该表的记录显示,指定经营实体没有任何东西,你仍重新添加准则,以寻找这种环境,然后利用这一环境。 随着所有这些工作,正在增加一个真正的巨大痛苦的新一栏?

一种环境表是,只有你才能为欧洲志愿人员协会开脱,但为什么?

回答你的问题,教你捕鱼,而不是把鱼交给鱼。

页: 1

阅读这些文件,但侧重于外包和完全外包。





相关问题
How to write this T-SQL WHERE condition?

I ve got two tables: TableA Col1 Col2 TableB Col3 Col4 I want to join them together: SELECT * from TableA join TableB ON (...) Now, in place of ... I need to write an expression ...

Customer and Order Sql Statement

TSQL query to select all records from Customer that has an Order and also select all records from customer that does not have an Order. The table Customer contains a primary key of CustomerID. The ...

Recommended way of querying multiple Versioned tables

Have a win 2003 box with MSSQL 2005 running on it. There is a database which is populated every morning with new/modified SalesOrder made the previous day. The database has several tables: SalesOrder, ...

update duplicate record

I have a table with the following fields Id Name IsPublic i need to write a sql query that updates IsPublic to false where name has a duplicate. Only one of the duplicates should have IsPublic = ...

Define variable to use with IN operator (T-SQL)

I have a Transact-SQL query that uses the IN operator. Something like this: select * from myTable where myColumn in (1,2,3,4) Is there a way to define a variable to hold the entire list "(1,2,3,4)"? ...

Selecting records during recursive stored procedure

I ve got a content management system that contains a hierarchical structure of categories, with sub-categories subject to different ordering options at each level. Currently, that s retrieved by a (...

热门标签