用XOR语句加入子句 [英] Join Clause With a XOR Statement
问题描述
我正在加入一个连接,但似乎无法使此XOR正常工作.
I am doing a join and I can't seem to make this XOR to properly work.
SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES = t1.MILES AND t2.MILES != 9999 THEN t2.FLATRATE
ELSE t3.RATEBASIS
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 TRUCKERCODE, MILES, RATEBASIS, FLATRATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
如果里程数相同,我需要加入ON子句,然后获取匹配的给定字段,否则我需要从第二个表中获取的默认数据是里程数等于9999的位置.子句我得到了很多额外的行,其中MILES等于45,它从TABLE2读取了英里等于45的数据,并从所有数据得到了Miles等于9999.我需要它做一个或另一个,但不能同时做.这就是我的桌子的样子
I need the ON clause to join if the miles are the same then get the given fields that match otherwise the default data I need to get out of the second table is where the miles is equal to 9999. Right now with that ON clause I get many extra rows where the MILES equals lets say 45, it gets the data from TABLE2 where miles equals 45 and all the data where miles equals 9999. I need it to do one or the other but not both. This is what my tables would look like
TABLE1 TABLE2
ID COMPANY MILES ETC ID COMPANYCODE MILES RATE
1 ILLINI 50 1 ILLINI 50 3.2
2 ILLINI 110 2 ILLINI 110 5.2
3 ILLINI 150 3 ILLINI 150 2.4
4 ILLINI 200 4 ILLINI 200 1.9
5 ILLINI 250 5 ILLINI 9999 1.5
6 ILLINI 300
7 ILLINI 350
8 ILLINI 400
9 ILLINI 450
10 ILLINI 500
Desired Output
COMPANY MILES MILES2 RATE
ILLINI 50 50 3.2
ILLINI 110 110 5.2
ILLINI 150 150 2.4
ILLINI 200 200 1.9
ILLINI 250 9999 1.5
ILLINI 300 9999 1.5
ILLINI 350 9999 1.5
ILLINI 400 9999 1.5
ILLINI 450 9999 1.5
ILLINI 500 9999 1.5
推荐答案
我认为这会为您提供想要的东西:
I think this will give you what you want:
SELECT t1.COMPANY, t1.MILES,
CASE WHEN t2.MILES IS NULL THEN t3.MILES
ELSE t2.MILES
END AS MILES2,
CASE WHEN t2.MILES IS NULL THEN t3.RATE
ELSE t2.RATE
END AS RATE
FROM TABLE1 AS t1
LEFT JOIN TABLE2 AS t2
ON t1.[COMPANY] = t2.[COMPANYCODE] AND (t1.[MILES] = t2.[MILES])
INNER JOIN (
SELECT TOP 1 COMPANYCODE, MILES, RATE FROM TABLE2 WHERE MILES = 9999
) AS t3
ON t1.[COMPANY] = t3.[COMPANYCODE]
如果MILES
中存在匹配项,则输出包含来自TABLE1
,TABLE2
的两条记录.否则,输出将包含左表中的记录,即TABLE1
和TABLE2
中带有MILES = 9999
的 specific 记录.
If there is a match in MILES
, then output contains both records from TABLE1
, TABLE2
. Otherwise, output contains record from left table, i.e. TABLE1
and the specific record from TABLE2
with MILES = 9999
.
请注意,以防万一TABLE2
中存在多个具有MILES = 9999
的记录的情况,在最后一个子查询中使用TOP 1
.
Please note that TOP 1
is used in the last sub-query just in case more than one records with MILES = 9999
exist in TABLE2
.
输出:
COMPANY MILES MILES2 RATE
----------------------------
ILLINI 50 50 3,2
ILLINI 110 110 5,2
ILLINI 150 150 2,4
ILLINI 200 200 1,9
ILLINI 250 9999 1,5
ILLINI 300 9999 1,5
ILLINI 350 9999 1,5
ILLINI 400 9999 1,5
ILLINI 450 9999 1,5
ILLINI 500 9999 1,5
这篇关于用XOR语句加入子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!