本文是小编为大家收集整理的关于用XOR语句连接子句的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我正在加入，我似乎无法使这个XOR正确地工作.
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]
如果里程相同，我需要加入子句，然后获取匹配的给定字段，否则我需要从第二个表中获得的默认数据是里程相当于9999.条款我得到了许多额外的行，其中里程等于说45，它从table2获取数据等于45，所有数据等于9999.我需要它来做一个或另一个，但不能同时做.这就是我的桌子的样子
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
推荐答案
我认为这会给您想要的东西:
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和MILES = 9999 TABLE2的特定记录.MILES = 9999.
.请注意，TOP 1在最后一个子查询中使用，以防万一多个记录 MILES = 9999中存在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
其他推荐答案
我会根据双重左上加入.一次进行比赛，如果没有，请默认为9999实例
SELECT
T1.*,
coalesce( T2.miles, T3.miles ) as Miles2,
coalesce( T2.rate, T3.rate ) as MileageRate
FROM
TABLE1 T1
LEFT JOIN TABLE2 T2
on T1.Company = T2.CompanyCode
AND T1.Miles = T2.Miles
LEFT JOIN TABLE2 T3
on T1.Company = T3.CompanyCode
AND T3.Miles = 9999
确认工作在此SQL小提琴链接
问题描述
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]
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]
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.
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.
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 would do based on a double left-join.. once for a match, and if not, then default to the 9999 instance
SELECT
T1.*,
coalesce( T2.miles, T3.miles ) as Miles2,
coalesce( T2.rate, T3.rate ) as MileageRate
FROM
TABLE1 T1
LEFT JOIN TABLE2 T2
on T1.Company = T2.CompanyCode
AND T1.Miles = T2.Miles
LEFT JOIN TABLE2 T3
on T1.Company = T3.CompanyCode
AND T3.Miles = 9999
Confirmed working at this SQL Fiddle link
更多相关问答
联系站长