内部连接如果零列

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在这里

您可以使用如下所示：

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)

Answer 4

尝试以下：

SELECT     x.Name,
           SUM(x.Balance)
FROM       x
INNER JOIN y
  ON       (y.idy IS NULL AND x.id = y.idx) OR (x.id = y.idy)
GROUP BY   x.Name;