英文:
how to phrase a conditional condition?
问题
我想查询一个自连接，只会在第一个条件上连接，只有在找不到匹配项时才会连接第二个条件。
我尝试了以下语法，但对我不起作用，因为它理解上总是连接两个条件。
SELECT a.id, a.id2, a.col1, b.id2 from test a
LEFT JOIN test b ON a.id = b.id AND
(SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1 OR b.col1 = '00')
我需要的是像XOR一样的操作。
示例：
表格
| id | id2 | col1 |
|---|---|---|
| 4080 | 2147 | 00 |
| 4080 | 2148 | 0001 |
| 4080 | 2149 | 0002 |
| 4080 | 2150 | 000201 |
| 3820 | 2069 | 00 |
| 3820 | 2070 | 000101 |
期望的结果
| a.id | a.id2 | a.col1 | b.id2 |
|---|---|---|---|
| 4080 | 2147 | 00 | NULL |
| 4080 | 2148 | 0001 | 2147 |
| 4080 | 2149 | 0002 | 2147 |
| 4080 | 2150 | 000201 | 2149 |
| 3820 | 2069 | 00 | NULL |
| 3820 | 2070 | 000101 | 2069 |
数据
CREATE TABLE [test](
[id] [int] NOT NULL,
[id2] [int] NOT NULL,
[col1] [varchar](6) NOT NULL
)
INSERT INTO [test] VALUES
(4080,2147,'00'),
(4080,2148,'0001'),
(4080,2149,'0002'),
(4080,2150,'000201'),
(3820,2069,'00'),
(3820,2070,'000101')
英文:
I want to query a self join that will only join on the first condition and only if it cant find a match it will join the second condition
I tried this syntax which doesnt work for me, because it will understandbly always join both conditions
SELECT a.id, a.id2, a.col1, b.id2 from test a
LEFT JOIN test b ON a.id = b.id AND
(SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1 OR b.col1 = '00')
What I need is like an XOR instead.
Example:
table
| id | id2 | col1 |
|---|---|---|
| 4080 | 2147 | 00 |
| 4080 | 2148 | 0001 |
| 4080 | 2149 | 0002 |
| 4080 | 2150 | 000201 |
| 3820 | 2069 | 00 |
| 3820 | 2070 | 000101 |
desired result
| a.id | a.id2 | a.col1 | b.id2 |
|---|---|---|---|
| 4080 | 2147 | 00 | NULL |
| 4080 | 2148 | 0001 | 2147 |
| 4080 | 2149 | 0002 | 2147 |
| 4080 | 2150 | 000201 | 2149 |
| 3820 | 2069 | 00 | NULL |
| 3820 | 2070 | 000101 | 2069 |
Data
CREATE TABLE [test](
[id] [int] NOT NULL,
[id2] [int] NOT NULL,
[col1] [varchar](6) NOT NULL
)
INSERT INTO [test] VALUES
(4080,2147,'00'),
(4080,2148,'0001'),
(4080,2149,'0002'),
(4080,2150,'000201'),
(3820,2069,'00'),
(3820,2070,'000101')
答案1
得分: 0
以下是翻译好的部分：
你可以执行两个独立的左连接，然后根据需要选择“最佳匹配”。
例如：
select a.*,
case when a.col1 = '00' then null else coalesce(b.id2, c.id2) end as bid2
from test a
left join test b on b.id = a.id
and SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1
left join test c on c.id = a.id and c.col1 = '00';
结果：
id id2 col1 bid2
----- ----- ------- ----
4080 2147 00 null
4080 2148 0001 2147
4080 2149 0002 2147
4080 2150 000201 2149
3820 2069 00 null
3820 2070 000101 2069
在db<>fiddle上查看运行示例。
英文:
You can do two separate left joins and then pick the "best match" as needed.
For example:
select a.*,
case when a.col1 = '00' then null else coalesce(b.id2, c.id2) end as bid2
from test a
left join test b on b.id = a.id
and SUBSTRING(a.col1, 1, LEN(a.col1) - 2) = b.col1
left join test c on c.id = a.id and c.col1 = '00'
Result:
id id2 col1 bid2
----- ----- ------- ----
4080 2147 00 null
4080 2148 0001 2147
4080 2149 0002 2147
4080 2150 000201 2149
3820 2069 00 null
3820 2070 000101 2069
See running example at db<>fiddle.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论