INNER if null列

Question

x table 
id - name   - balance
1 - test    - 500
2 - test 2  - 400
3 - test 3  - 300

y table
idx - idy - name
1   - null
2   - 5
3   - null
4   - 6

SELECT x.Name, SUM(x.Balance)
FROM x
INNER JOIN y ON ???

if IS NULL y.idy ON (x.id = y.idx)
ELSE ON (x.id = y.idy)

我的英语不足以说出这个问题。但是我的问题是可以理解的

Answer 1

您还可以尝试使用ANSI SQL标准coalesce()函数来检查null值

SELECT x.Name, SUM(x.Balance) [Balance] FROM x
join y ON coalesce(y.idy, y.idx) = x.id
group by x.Name

结果：

Name    Balance
test    500
test 3  300

Answer 2

SQL HERE

您可以像下面这样使用ISNULL：

SELECT x.Name, SUM(x.Balance)
FROM x
INNER JOIN y ON x.id = isnull(y.idy, y.idx)
group by x.Name

Answer 3

布尔逻辑如下所示：

ON (y.idy IS NULL AND x.id = y.idx) OR (x.id = y.idy)