我如何获得列名称和/或值？

select 
t.slotmachinebk,
t.gamingdate, 
t.freeplaydownloaded, 
t.freeplayadjusted, 
t.freeplayplayed, 
t.freeplayabandoned, 
t.freeplaybalance 
from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t where not exists (select * from  testtable where

slotmachinebk = t.slotmachinebk and
auditdate = t.gamingdate and
freeplaydownloaded = t.freeplaydownloaded and
freeplayadjusted =  t.freeplayadjusted and
freeplayplayed = t.freeplayplayed and
freeplayabandoned = t.freeplayabandoned and 
freeplaybalance = t.freeplaybalance)

Well I have this tsql query that gives me the records that dont match between testtable and freeplay.egmfreeplay....but how do I modify this query to get the column name/value that dont match, column names being the columns used in subquery ANDs...

好吧,我有这个tsql查询,它给我在testtable和freeplay.egmfreeplay之间不匹配的记录....但是如何修改此查询以获取不匹配的列名/值,列名是子查询中使用的列与运算...

4 个解决方案

#1

You could put a bunch of 'CASE WHEN' statements in your SELECT query

您可以在SELECT查询中放入一堆'CASE WHEN'语句

CASE WHEN a.column1 <> b.column1 THEN 1 ELSE 0 END AS column1_diff

Then you would see in your result set a '1' if that column was different for that record or a '0' if it wasn't different

然后你会在结果集中看到一个'1',如果该列对于该记录是不同的,或者如果它没有不同则为'0'

EDIT

I attempted to refactor your query to something that will work.

我试图将你的查询重构为可行的东西。

SELECT      t.*,
            CASE WHEN t.slotmachinebk       <> z.slotmachinebk      THEN 1 ELSE 0 END AS slotmachinebk_diff,
            CASE WHEN t.gamingdate          <> z.gamingdate         THEN 1 ELSE 0 END AS gamingdate_diff,
            CASE WHEN t.freeplaydownloaded  <> z.freeplaydownloaded THEN 1 ELSE 0 END AS freeplaydownloaded_diff,
            CASE WHEN t.freeplayadjusted    <> z.freeplayadjusted   THEN 1 ELSE 0 END AS freeplayadjusted_diff,
            CASE WHEN t.freeplayplayed      <> z.freeplayplayed     THEN 1 ELSE 0 END AS freeplayplayed_diff,
            CASE WHEN t.freeplayabandoned   <> z.freeplayabandoned  THEN 1 ELSE 0 END AS freeplayabandoned_diff,
            CASE WHEN t.freeplaybalance     <> z.freeplaybalance    THEN 1 ELSE 0 END AS freeplaybalance_diff,
FROM        testtable z
LEFT JOIN   (
            SELECT      * 
            FROM        freeplay.egmfreeplay 
            UNION ALL 
            SELECT      * 
            FROM        Change.EgmFreePlay
            ) t
        ON  z.slotmachinebk         = t.slotmachinebk
        AND z.auditdate             = t.gamingdate
        AND z.freeplaydownloaded    = t.freeplaydownloaded
        AND z.freeplayadjusted      = t.freeplayadjusted
        AND z.freeplayplayed        = t.freeplayplayed
        AND z.freeplayabandoned     = t.freeplayabandoned
        AND z.freeplaybalance       = t.freeplaybalance
WHERE       t.id IS NOT NULL -- this will only select those in 'freeplay.egmfreeplay' and 'Change.EgmFreePlay' that are not in 'testtable', I am not sure if 'id' actually exists, but you want to use something that will never be NULL in those two tables

#2

If I'm understanding your question properly, you want rows from each table where the records are different... if you have an identity column to match them on, you could join on that and then add your where clause to only pull records where those columns are different.

如果我正确理解你的问题,你需要每个表中的记录不同的行...如果你有一个标识列匹配它们,你可以加入它然后添加你的where子句只拉记录在哪里那些列是不同的。

#3

select t.slotmachinebk
       ,t.gamingdate
       , t.freeplaydownloaded
       , t.freeplayadjusted
       , t.freeplayplayed
       , t.freeplayabandoned
       , t.freeplaybalance 
from (select *  freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t
left join testtable tt on 
      tt.slotmachinebk = t.slotmachinebk 
      and tt.auditdate = t.gamingdate 
      and tt.freeplaydownloaded = t.freeplaydownloaded 
      and freeplayadjusted =  t.freeplayadjusted 
      and tt.freeplayplayed = t.freeplayplayed 
      and tt.freeplayabandoned = t.freeplayabandoned 
      and tt.freeplaybalance = t.freeplaybalance
where tt.whateverTheHeckMyIdColumnIs is null

This should get you whatever is in your union that does not have an exact match in testtable. Of course you shouldn't not use select * either, especially not in union (this query would break the first time someone changed a table structure in one but not both of the tables in the union). Learn to specify your columns. If you have no id on testtable, create one first or the change the where clause to a field that cannot be ever be null.

这应该可以让你在你的联盟中找到与testtable中没有完全匹配的东西。当然你不应该使用select *,尤其是不能在union中使用(这个查询会在第一次有人更改一个表结构而不是联合中的两个表时中断)。学习指定列。如果testtable上没有id,请先创建一个id,或将where子句更改为一个不能为null的字段。

#4

LEFT OUTER JOIN

LEFT OUTER JOIN

I'd don't know the key column names, but try this...

我不知道关键的列名,但试试这个......

select 
    CASE WHEN ISNULL(t.slotmachinebk           ,'')!=ISNULL(z.XXX                ,'') THEN 'slotmachinebk'       ELSE '' END AS slotmachinebk        --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.gamingdate         ,'')!=ISNULL(z.gamingdate         ,'') THEN 'gamingdate'          ELSE '' END AS gamingdate           --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplaydownloaded ,'')!=ISNULL(z.freeplaydownloaded ,'') THEN 'freeplaydownloaded'  ELSE '' END AS freeplaydownloaded   --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayadjusted   ,'')!=ISNULL(z.freeplayadjusted   ,'') THEN 'freeplayadjusted'    ELSE '' END AS freeplayadjusted     --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayplayed     ,'')!=ISNULL(z.freeplayplayed     ,'') THEN 'freeplayplayed'      ELSE '' END AS freeplayplayed       --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayabandoned  ,'')!=ISNULL(z.freeplayabandoned  ,'') THEN 'freeplayabandoned'   ELSE '' END AS freeplayabandoned    --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplaybalance    ,'')!=ISNULL(z.freeplaybalance    ,'') THEN 'freeplaybalance'     ELSE '' END AS freeplaybalance      --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
    from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t 
    LEFT OUTER JOIN testtable z ON t.KEY=z.KEY  --<<<<<<<<key names???
    where not exists (select * from  testtable where
                             slotmachinebk = t.slotmachinebk and
                             auditdate = t.gamingdate and
                             freeplaydownloaded = t.freeplaydownloaded and
                             freeplayadjusted =  t.freeplayadjusted and
                             freeplayplayed = t.freeplayplayed and
                             freeplayabandoned = t.freeplayabandoned and 
                             freeplaybalance = t.freeplaybalance
                      )