英文:
Join two tables on multiple columns with case that return not fully matched raws with replacement of not matched value
问题
Here's the translation of the provided text:
我有表A
| raw_number | ars | bill | codt | c4 |
|---|---|---|---|---|
| 1 | 900 | 603121 | 123 | |
| 2 | 900 | 603121 | YPR-003 | 234 |
| 3 | 900 | 603121 | Psp-123 | 345 |
| 4 | 900 | 603121 | 456 | |
| 5 | 900 | 603121 | 567 |
和表B
| raw_number | ars | bill | codt | s4 |
|---|---|---|---|---|
| 1 | 900 | 603121 | Psp-123 | asd |
| 2 | 900 | 603121 | zxc | |
| 3 | 900 | 455000 | F |
我正在将表A与表B连接，连接条件是“ars”，“bill”，“codt”列。
我的目标是获得以下结果：
| ars | bill | codt | c4 | s4 |
|---|---|---|---|---|
| 900 | 603121 | 123 | zxc | |
| 900 | 603121 | 234 | zxc | |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | 456 | zxc | |
| 900 | 603121 | 567 | zxc |
“raw_number”列用于解释：对于来自表A的raw number 3，匹配必须是来自表B的raw 1，因为它完全匹配，但来自A的raw number 2必须与来自B的raw 2匹配。
原始查询更复杂，因为表A和B是来自其他连接的结果。
更新后的查询如下：
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars
AND a.bill = b.bill
AND a.codt IS DISTINCT FROM b.codt
UNION
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill
AND a.codt = b.codt
看起来这是我想要的，但我不确定，因为我的原始查询周围还有其他联合操作，看起来很庞大，有1200多行。也许有人有其他想法？
英文:
I have table A
| raw_number | ars | bill | codt | c4 |
|---|---|---|---|---|
| 1 | 900 | 603121 | 123 | |
| 2 | 900 | 603121 | YPR-003 | 234 |
| 3 | 900 | 603121 | Psp-123 | 345 |
| 4 | 900 | 603121 | 456 | |
| 5 | 900 | 603121 | 567 |
and table B
| raw_number | ars | bill | codt | s4 |
|---|---|---|---|---|
| 1 | 900 | 603121 | Psp-123 | asd |
| 2 | 900 | 603121 | zxc | |
| 3 | 900 | 455000 | F |
Im joining table A with table B on "ars", "bill", "codt" columns.
My point is getting this result:
| ars | bill | codt | c4 | s4 |
|---|---|---|---|---|
| 900 | 603121 | 123 | zxc | |
| 900 | 603121 | 234 | zxc | |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | 456 | zxc | |
| 900 | 603121 | 567 | zxc |
columns "raw_number" added for explanations : for raw number 3 from table A match must be raw 1 from table B, cos its fully matched, but raw number 2 from A must match with raw 2 from B
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
is not working for me cos it returns null values from table B for raw number 3 A table
INNER JOIN is not a point too cos remove this raw
original query is more complicated because tables A and B is a results from another joins
UPDATE:
After an NBK answer i try this
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars
AND a.bill = b.bill
AND a.codt IS DISTINCT FROM b.codt
UNION
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill
AND a.codt = b.codt
it looks like i want but im not sure because my original query with this have another unions around and looks monstrous.. 1200+ raws
Maybe someone have another idea?
答案1
得分: 1
下面是您要翻译的内容：
if codt codt is an emtypoyt string = works, you neeed only take care of the NULL if no match is found
CREATE TABLE A (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"c4" INTEGER
);
INSERT INTO A
("raw_number", "ars", "bill", "codt", "c4")
VALUES
('1', '900', '603121','', '123'),
('2', '900', '603121', 'YPR-003', '234'),
('3', '900', '603121', 'Psp-123', '345'),
('4', '900', '603121', '', '456'),
('5', '900', '603121', '', '567');
CREATE TABLE B (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"s4" VARCHAR(3)
);
INSERT INTO B
("raw_number", "ars", "bill", "codt", "s4")
VALUES
('1', '900', '603121', 'Psp-123', 'asd'),
('2', '900', '603121', '', 'zxc'),
('3', '900', '455000', '', 'F');
SELECT a.ars, a.bill, a.codt, a.c4, COALESCE(b.s4,(SELECT s4 FROM B b1 WHERE a.ars = b1.ars AND a.bill = b1.bill and codt = ''))
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
这是您提供的SQL代码的翻译部分。
<details>
<summary>英文:</summary>
if codt codt is an emtypoyt string = works, you neeed only take care of the NULL if no match is found
CREATE TABLE A (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"c4" INTEGER
);
INSERT INTO A
("raw_number", "ars", "bill", "codt", "c4")
VALUES
('1', '900', '603121','', '123'),
('2', '900', '603121', 'YPR-003', '234'),
('3', '900', '603121', 'Psp-123', '345'),
('4', '900', '603121', '', '456'),
('5', '900', '603121', '', '567');
> ``` status
> CREATE TABLE
> ```
> ``` status
> INSERT 0 5
> ```
CREATE TABLE B (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"s4" VARCHAR(3)
);
INSERT INTO B
("raw_number", "ars", "bill", "codt", "s4")
VALUES
('1', '900', '603121', 'Psp-123', 'asd'),
('2', '900', '603121', '', 'zxc'),
('3', '900', '455000', '', 'F');
> ``` status
> CREATE TABLE
> ```
> ``` status
> INSERT 0 3
> ```
SELECT a.ars, a.bill, a.codt, a.c4, COALESCE(b.s4,(SELECT s4 FROM B b1 WHERE a.ars = b1.ars AND a.bill = b1.bill and codt = ''))
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
| ars | bill | codt | c4 | coalesce |
|----:|-----:|:-----|---:|:---------|
| 900 | 603121 | | 123 | zxc |
| 900 | 603121 | | 456 | zxc |
| 900 | 603121 | | 567 | zxc |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | YPR-003 | 234 | zxc |
> ``` status
> SELECT 5
> ```
[fiddle](https://dbfiddle.uk/P4nSx_yC)
t
</details>
# 答案2
**得分**: 1
This query will return the data you requested but *s4* is missing.
这个查询将返回您请求的数据，但缺少 *s4*。
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
order by ars, bill, c4
这将返回：
ars | bill | codt | c4 | s4
---|------|-----|-------|----
900 | 603121 | null | 123 | null
900 | 603121 | null | 234 | null
900 | 603121 | Psp-123 | 345 | asd
900 | 603121 | null | 456 | null
900 | 603121 | null | 567 | null
Then will get *s4* from *table B* when s4 is null using `inner join` :
然后，当 s4 为 null 时，将从 *表 B* 获取 *s4* 使用 `inner join`：
select DISTINCT s.ars, s.bill, s.codt, s.c4, case when s.s4 is not null then s.s4 else b.s4 end as s4
from B b
inner join (
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
) as s on s.ars = b.ars and s.bill = b.bill
where b.codt = ''
order by ars, bill, c4
Result :
ars | bill | codt | c4 | s4
---|------|-----|------|---
900 | 603121 | null | 123 | zxc
900 | 603121 | null | 234 | zxc
900 | 603121 | Psp-123 | 345 | asd
900 | 603121 | null | 456 | zxc
900 | 603121 | null | 567 | zxc
[Demo here][1]
[1]: https://dbfiddle.uk/fz7Ox1cN
<details>
<summary>英文:</summary>
This query will return the data you requested but *s4* is missing.
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
order by ars, bill, c4
This will return :
ars |bill |codt |c4 |s4
---|------|-----|-------|----
900 |603121| null |123| null
900 |603121| null |234| null
900 |603121| Psp-123 |345| asd
900 |603121| null |456| null
900 |603121| null |567| null
Then will get *s4* from *table B* when s4 is null using `inner join` :
select DISTINCT s.ars, s.bill, s.codt, s.c4, case when s.s4 is not null then s.s4 else b.s4 end as s4
from B b
inner join (
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM A a
LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt <> ''
) as s on s.ars = b.ars and s.bill = b.bill
where b.codt = ''
order by ars, bill, c4
Result :
ars |bill| codt| c4| s4
---|------|-----|------|---
900 |603121| null| 123| zxc
900 |603121| null| 234| zxc
900 |603121| Psp-123| 345| asd
900 |603121| null| 456| zxc
900 |603121| null| 567| zxc
[Demo here][1]
[1]: https://dbfiddle.uk/fz7Ox1cN
</details>
# 答案3
**得分**: 1
以下是翻译好的部分：
"根据我理解，您想将存在于 A 中但不存在于 B 中的 `codt` 值与 B 中的空字符串 `codt` 匹配，您可以在连接之前检查 `B` 中 `codt` 的存在性，如下所示：
```sql
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM
(
SELECT ars, bill,
CASE
WHEN
EXISTS(SELECT 1 FROM B d WHERE d.ars=t.ars AND d.bill=t.bill AND d.codt= t.codt) THEN codt
ELSE ''
END AS codt, c4
FROM A t
) a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
ORDER BY c4
英文:
As I understood, you want to match the codt value that exists in A but not in B with the empty string codt from B, you could check the existence of codt in B before joining as the following:
SELECT a.ars, a.bill, b.codt, a.c4, b.s4
FROM
(
select ars, bill,
case
when
exists(select 1 from B d where d.ars=t.ars and d.bill=t.bill and d.codt= t.codt) then codt
else ''
end as codt, c4
from A t
) a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
ORDER BY c4
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论