如何从Except子句获取相关列 [英] How to get relating columns from an Except clause
问题描述
我有两个表比较一个定位的差异。我想知道哪些记录在tblExample2中而不是在tblExample1中基于ProjectNr列。如果存在差异，请向我显示与检测到的ProjectNr相关的剩余列。那可能吗？
tblExample1
Hi,
I have two table to compare a locate the differences. I want to know which records are in tblExample2 and not in tblExample1 based on the column ProjectNr. And if there are differences, show me the remaining columns relating to detected ProjectNr. Is that possible?
tblExample1
Year BookingNr ProjectNr ProjectName Area Location Finance Price A Price B Plast Pnow Plan 1 Plan 2 Plan 3 Plan 4
01.2011 16 TT.0161110 Acc-Gha E Ost B 2.516,00 2.516,00 2.516,00
01.2011 16 TT.0161110 Acc-Gha E Ost LU 1.199.337,78 931.337,78 78.337,80 55.000,00 150.000,00 134.000,00 206.000,00 150.000,00
01.2011 16 TT.0161110 Acc-Gha E Ost A 513.000,00 513.000,00 blank blank 50.000,00 70.000,00 100.000,00 120.000,00
01.2011 16 TT.0161110 Acc-Gha E Ost LU 5.135.000,00 4.976.000,00 blank blank 250.000,00 750.000,00 1.100.000,00 1.400.000,00
01.2011 16 T.016064 Nrg-Lag E Südost LU blank 735.000,00 blank blank blank blank blank
01.2011 16 T.016064 Nrg-Lag E Südost LU blank 4.161.000,00 blank blank blank blank blank
01.2011 31 PP.031001 Bln-Port E Süd A 2.967,00 2.967,00 2.967,00 blank blank blank blank
01.2011 31 PP.031001 Bln-Port E Süd LA 33.889,68 31.630,18 12.630,20 2.740,50 4.000,00 8.000,00 1.000,00
01.2011 31 PP.031001 Bln-Port E Süd LA 70.000,00 70.000,00 blank blank 35.000,00 35.000,00 blank
01.2011 11 G.011210 Alt-Nyc E Südost LS blank 4.000,00 blank blank blank
01.2011 11 G.011210 Alt-Nyc E Südost LS blank 80.000,00 blank blank blank
01.2011 16 X.016270181506 Fraport-Schil D Ost FE 46.702,71 50.335,23 50.335,20 3.632,52 0
01.2011 16 X.016270181506 Fraport-Schil D Ost V3 430.122,03 430.122,03 430.122,00
01.2011 16 X.016270181506 Fraport-Schil D Ost W 341.374,00 341.374,00 341.374,00
01.2011 16 X.016270181506 Fraport-Schil D Ost FE 173.415,55 173.415,55 169.415,40 0
01.2011 16 X.016270181506 Fraport-Schil D Ost V3 3.969.787,11 3.969.787,11 3.969.787,10
01.2011 16 XX.01681514600 Pot-Ess D Ost V3 175.649,82 175.649,82 175.649,80
01.2011 16 XX.01681514600 Pot-Ess D Ost V4 20.627,25 20.627,25 20.627,30
tblExample 2
tblExample 2
Year BookingNr ProjectNr ProjectName Area Location Finance Price A Price B Plast Pnow Plan 1 Plan 2 Plan 3 Plan 4
02.2011 16 TT.0161110 Acc-Gha D Ost B 2.516,00 2.516,00 2.516,00
02.2011 16 TT.0161110 Acc-Gha D Ost LU 1.199.337,78 960.337,78 78.337,80 55.000,00 150.000,00 134.000,00 206.000,00 150.000,00
02.2011 16 TT.0161110 Acc-Gha D Ost A 513.000,00 513.000,00 blank blank 50.000,00 70.000,00 100.000,00 120.000,00
02.2011 16 TT.0161110 Acc-Gha D Ost LU 5.135.000,00 4.976.000,00 blank blank 250.000,00 750.000,00 1.100.000,00 1.400.000,00
02.2011 16 T.016064 Nrg-Lag D Südost LU blank 735.000,00 blank blank blank blank blank
02.2011 16 T.016064 Nrg-Lag D Südost LU blank 4.161.000,00 blank blank blank blank blank
02.2011 31 PP.031001 Bln-Port D Süd A 2.967,00 2.967,00 2.967,00 blank blank blank blank
02.2011 31 PP.031001 Bln-Port D Süd LA 33.889,68 33.630,18 12.630,20 2.740,50 4.000,00 8.000,00 1.000,00
02.2011 31 PP.031001 Bln-Port D Süd LA 70.000,00 70.000,00 blank blank 35.000,00 35.000,00
02.2011 11 G.011210 Alt-Nyc D Südost LS blank 4.000,00 blank blank
02.2011 11 G.011210 Alt-Nyc D Südost LS blank 80.000,00 blank blank
02.2011 16 X.016270181506 Fraport-Schil D Ost AZ blank 57.000,00
02.2011 16 X.016270181506 Fraport-Schil D Ost FE 46.702,71 50.335,23 50.335,20 3.632,52 0
02.2011 16 X.016270181506 Fraport-Schil D Ost V3 430.122,03 430.122,03 430.122,00
02.2011 16 X.016270181506 Fraport-Schil D Ost W 341.374,00 341.374,00 341.374,00
02.2011 16 X.016270181506 Fraport-Schil D Ost AZ 590.000,00
02.2011 16 X.016270181506 Fraport-Schil D Ost FE 173.415,55 173.415,55 169.415,40 0
02.2011 16 XX.01681514600 Pot-Ess D Ost V blank blank blank 0 0
02.2011 16 XX.01681514600 Pot-Ess D Ost V3 175.649,82 175.649,82 175.649,80
02.2011 16 XX.01681514600 Pot-Ess D Ost V4 20.627,25 20.627,25 20.627,30
第一个可能的答案是这样的：
The first possible answer has the a sp like this:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
' ORDER BY [ProjectNr] DESC '
可能答案1
Possible Answer 1
ProjectNr ProjectName BookingNr
TT.0161110 Acc-Gha 16
T.016064 Nrg-Lag 16
PP.031001 Bln-Port 31
G.011210 Alt-Nyc 11
答案基于以下声明：
And the answer is based on this statement:
SET @Dynamictbl = N'SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM (
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM ' + @Table_Name + ' WHERE [Area] = ''D''' +
' EXCEPT
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM ' + @Table_Name2 + ' WHERE [Area] = ''D''' +
') AS T ' +
' GROUP BY [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
可能答案2
Possible Answer 2
BookingNr ProjectNr ProjectName Finance Price A
16 TT.0161110 Acc-Gha B 2.516,00
16 TT.0161110 Acc-Gha LU 1.199.337,78
16 TT.0161110 Acc-Gha A 513.000,00
16 TT.0161110 Acc-Gha LU 5.135.000,00
16 T.016064 Nrg-Lag LU blank
16 T.016064 Nrg-Lag LU blank
31 PP.031001 Bln-Port A 2.967,00
31 PP.031001 Bln-Port LA 33.889,68
31 PP.031001 Bln-Port LA 70.000,00
11 G.011210 Alt-Nyc LS blank
11 G.011210 Alt-Nyc LS blank
16 X.016270181506 Fraport-Schil AZ blank
16 XX.01681514600 Pot-Ess V blank
导出记录/解决方案ion应如下所示：
可能的答案3
Exported records / solution should look like this:
Possible Answer 3
BookingNr ProjectNr ProjectName Finance Price A
16 TT.0161110 Acc-Gha B 2.516,00
16 TT.0161110 Acc-Gha LU 1.199.337,78
16 TT.0161110 Acc-Gha A 513.000,00
16 TT.0161110 Acc-Gha LU 5.135.000,00
16 T.016064 Nrg-Lag LU blank
16 T.016064 Nrg-Lag LU blank
31 PP.031001 Bln-Port A 2.967,00
31 PP.031001 Bln-Port LA 33.889,68
31 PP.031001 Bln-Port LA 70.000,00
11 G.011210 Alt-Nyc LS blank
11 G.011210 Alt-Nyc LS blank
我的主要问题是或者真正感兴趣的记录应该是第一个可能的答案没有X的但是与其他相关的列（如可能的答案3）。这是可能的，虽然X的财务列是表示例2中的新内容。我已经从昨天更新了我的主要部分
My Main Question is or the records which am really interested in should look like the first possible answer without the X’s But with the other columns relating to it (like possible answer 3). Is that possible although the finance column to the X's are new in table Example2 .I have updated my main from yesterday
推荐答案
经过所有讨论我们认为这是你需要什么：
After all the discussions we had I think this is what you need:
SELECT [ProjectNr], [ProjectName], [BookingNr],[Finance],[Price A]
FROM tblExample2 t2 WHERE [Area] = 'D'
AND NOT EXISTS
(
SELECT * FROM tblExample1 t1 WHERE [Area] = 'D'
AND t2.[ProjectNr] = t1.[ProjectNr]
AND t2.[ProjectName] = t1.[ProjectName]
AND t2.[BookingNr] = t1.[BookingNr]
AND t2.[Finance] = t1.[Finance]
)
ORDER BY [ProjectNr] DESC
好的，你仍然没有回答我提出的所有问题，但我希望我能理解你的要求。您可以通过将行集加入详细信息表来获得所需的结果：
OK, you still didn't actually answer all the questions I asked, but I'm hoping that I have the sense of your requirement. You can achieve the result you want by joining your row set back to the detail table:
SELECT
DISTINCT table2.*
FROM (
SELECT ProjectNr, ProjectName, BookingNr
FROM table2
WHERE Area = 'D'
EXCEPT
SELECT ProjectNr, ProjectName, BookingNr
FROM table1
WHERE Area = 'D') AS MyRecs
INNER JOIN table2 ON MyRecs.ProjectNr = table2.ProjectNr
AND MyRecs.ProjectName = table2.ProjectName
AND MyRecs.BookingNr = table2.BookingNr
ORDER BY ProjectNr DESC
注意：
我仍然怀疑你的选择键;在您的示例数据中，ProjectNr始终生成相同的ProjectName和BookingNr。我已经使用DISTINCT删除了样本数据会产生的笛卡尔积。如果这不正确，您将需要调整选择键字段，如果您在内部联接中获得唯一匹配，则不需要不同。
数据看起来很差，但您可能有对此没有控制权。如果ProjectNr总是产生相同的ProjectName和BookingNr，那么这些应该被移动到相关的表（s？）。
没有候选键的数据很少。了解候选键是数据库设计的基础。密钥可以是多个字段（在现实世界中，通常是），并且是数据目的和描述内容的核心。粘贴身份字段不够好;确定它会为你提供一个有效的行标识符，但它不能解释数据的含义。
你可以使用table1和table2之间的内连接和适当的连接条件（注意这不是必须是等于）和/或WHERE条件才能完成相同的工作，但如果不能更好地理解您的数据/模式，我无法确定！
Notes:
I still have doubts about your selection key; in your sample data ProjectNr always yields the same ProjectName and BookingNr. I've used DISTINCT to remove the Cartesian product your sample data would yield. You will need to adjust the selection key fields if this is incorrect and if you get unique matches across the inner join you won't need "distinct".
The data looks poorly modelled, but you may have no control on this. If ProjectNr always yields the same ProjectName and BookingNr then these should be moved to related table(s?).
Data without a candidate key is rare. Understanding your candidate key(s) is fundamental to database design. A key can be multiple fields (and in the real world, often is) and is core to the purpose of the data and what it is describing. Sticking an Identity field in is not good enough; sure it will give you an efficient row identifier, but it doesn't explain what the data is about.
You could probably use an inner join between table1 and table2 with appropriate join conditions (note this does not have to be "equals") and/or WHERE conditions to do the same job, but without a better understanding of your data/schema I can't be certain!
这篇关于如何从Except子句获取相关列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
