Join two tables on multiple columns with case that return not fully matched raws with replacement of not matched value

huangapple
go评论95阅读模式
英文:

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是来自其他连接的结果。

更新后的查询如下：

  1. SELECT a.ars, a.bill, b.codt, a.c4, b.s4 
  2. FROM A a LEFT JOIN B b ON a.ars = b.ars 
  3. AND a.bill = b.bill 
  4. AND a.codt IS DISTINCT FROM b.codt 
  5. UNION  
  6. SELECT a.ars, a.bill, b.codt, a.c4, b.s4 
  7. FROM A a LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill 
  8. 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

  1. SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  2. FROM A a
  3. LEFT JOIN B b ON
  4. 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

  1. SELECT a.ars, a.bill, b.codt, a.c4, b.s4 
  2. FROM A a LEFT JOIN B b ON a.ars = b.ars 
  3. AND a.bill = b.bill 
  4. AND a.codt IS DISTINCT FROM b.codt 
  5. UNION  
  6. SELECT a.ars, a.bill, b.codt, a.c4, b.s4 
  7. FROM A a LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill 
  8. 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

下面是您要翻译的内容：

  1. if codt codt is an emtypoyt string = works, you neeed only take care of the NULL if no match is found
  3. CREATE TABLE A (
  4.   "raw_number" INTEGER,
  5.   "ars" INTEGER,
  6.   "bill" INTEGER,
  7.   "codt" VARCHAR(7),
  8.   "c4" INTEGER
  9. );
  11. INSERT INTO A
  12.   ("raw_number", "ars", "bill", "codt", "c4")
  13. VALUES
  14.   ('1', '900', '603121','', '123'),
  15.   ('2', '900', '603121', 'YPR-003', '234'),
  16.   ('3', '900', '603121', 'Psp-123', '345'),
  17.   ('4', '900', '603121', '', '456'),
  18.   ('5', '900', '603121', '', '567');
  1. CREATE TABLE B (
  2.   "raw_number" INTEGER,
  3.   "ars" INTEGER,
  4.   "bill" INTEGER,
  5.   "codt" VARCHAR(7),
  6.   "s4" VARCHAR(3)
  7. );
  9. INSERT INTO B
  10.   ("raw_number", "ars", "bill", "codt", "s4")
  11. VALUES
  12.   ('1', '900', '603121', 'Psp-123', 'asd'),
  13.   ('2', '900', '603121', '', 'zxc'),
  14.   ('3', '900', '455000', '', 'F');
  1. 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 = ''))
  2. FROM A a
  3. LEFT JOIN B b ON
  4. a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt

fiddle

  2. 这是您提供的SQL代码的翻译部分。
  4. <details>
  5. <summary>英文:</summary>
  7. 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');

  1. &gt; ``` status
  2. &gt; CREATE TABLE
  3. &gt; ```
  5. &gt; ``` status
  6. &gt; INSERT 0 5
  7. &gt; ```

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');

  1. &gt; ``` status
  2. &gt; CREATE TABLE
  3. &gt; ```
  5. &gt; ``` status
  6. &gt; INSERT 0 3
  7. &gt; ```

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

  1. | ars | bill | codt | c4 | coalesce |
  2. |----:|-----:|:-----|---:|:---------|
  3. | 900 | 603121 |  | 123 | zxc |
  4. | 900 | 603121 |  | 456 | zxc |
  5. | 900 | 603121 |  | 567 | zxc |
  6. | 900 | 603121 | Psp-123 | 345 | asd |
  7. | 900 | 603121 | YPR-003 | 234 | zxc |
  8. &gt; ``` status
  9. &gt; SELECT 5
  10. &gt; ```
  12. [fiddle](https://dbfiddle.uk/P4nSx_yC)
  13. t
  17. </details>
  21. # 答案2
  22. **得分**: 1
  24. This query will return the data you requested but *s4* is missing. 
  26. 这个查询将返回您请求的数据，但缺少 *s4*。
  28. SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  29. FROM A a
  30. LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt &lt;&gt; &#39;&#39;
  31. order by ars, bill, c4
  33. 这将返回：
  35. ars | bill | codt | c4 | s4
  36. ---|------|-----|-------|----
  37. 900 | 603121 | null | 123 | null
  38. 900 | 603121 | null | 234 | null
  39. 900 | 603121 | Psp-123 | 345 | asd
  40. 900 | 603121 | null | 456 | null
  41. 900 | 603121 | null | 567 | null
  43. Then will get *s4* from *table B* when s4 is null using `inner join` :
  45. 然后，当 s4 为 null 时，将从 *表 B* 获取 *s4* 使用 `inner join`：
  47. 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
  48. from B b
  49. inner join (
  50. SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  51. FROM A a
  52. LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt &lt;&gt; &#39;&#39;
  53. ) as s on s.ars = b.ars and s.bill = b.bill
  54. where b.codt = &#39;&#39;
  55. order by ars, bill, c4
  57. Result :
  59. ars | bill | codt | c4 | s4
  60. ---|------|-----|------|---
  61. 900 | 603121 | null | 123 | zxc
  62. 900 | 603121 | null | 234 | zxc
  63. 900 | 603121 | Psp-123 | 345 | asd
  64. 900 | 603121 | null | 456 | zxc
  65. 900 | 603121 | null | 567 | zxc
  67. [Demo here][1]
  69. [1]: https://dbfiddle.uk/fz7Ox1cN
  71. <details>
  72. <summary>英文:</summary>
  74. This query will return the data you requested but *s4* is missing. 
  76.     SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  77.       FROM A a
  78.       LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt &lt;&gt; &#39;&#39;
  79.       order by ars, bill, c4
  81. This will return :
  83. ars	|bill  |codt	|c4	|s4
  84.  ---|------|-----|-------|----
  85. 900	|603121|	null	|123|	null
  86. 900	|603121|	null	|234|	null
  87. 900	|603121|	Psp-123	|345|	asd
  88. 900	|603121|	null	|456|	null
  89. 900	|603121|	null	|567|	null
  91. Then will get *s4* from *table B* when s4 is null using `inner join` : 
  93.     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 
  94.     from B b
  95.     inner join (
  96.       SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  97.       FROM A a
  98.       LEFT JOIN B b ON a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt and a.codt &lt;&gt; &#39;&#39;
  99.     ) as s on s.ars = b.ars and s.bill = b.bill
  100.     where b.codt = &#39;&#39;
  101.     order by ars, bill, c4
  103. Result :
  105. ars	|bill|	codt|	c4|	s4
  106. ---|------|-----|------|---
  107. 900	|603121|	null|	123|	zxc
  108. 900	|603121|	null|	234|	zxc
  109. 900	|603121|	Psp-123|	345|	asd
  110. 900	|603121|	null|	456|	zxc
  111. 900	|603121|	null|	567|	zxc
  113. [Demo here][1] 
  116.   [1]: https://dbfiddle.uk/fz7Ox1cN
  118. </details>
  122. # 答案3
  123. **得分**: 1
  125. 以下是翻译好的部分：
  127. "根据我理解，您想将存在于 A 中但不存在于 B 中的 `codt` 值与 B 中的空字符串 `codt` 匹配，您可以在连接之前检查 `B` 中 `codt` 的存在性，如下所示：
  129. ```sql
  130. SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  131. FROM 
  132.   (
  133.     SELECT ars, bill, 
  134.       CASE 
  135.         WHEN 
  136.          EXISTS(SELECT 1 FROM B d WHERE d.ars=t.ars AND d.bill=t.bill AND d.codt= t.codt) THEN codt 
  137.         ELSE '' 
  138.       END AS codt, c4
  139.    FROM A t
  140.   ) a
  141. LEFT JOIN B b ON
  142. a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
  143. 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:

  1. SELECT a.ars, a.bill, b.codt, a.c4, b.s4
  2. FROM 
  3.   (
  4.     select ars, bill, 
  5.       case 
  6.         when 
  7.          exists(select 1 from B d where d.ars=t.ars and d.bill=t.bill and d.codt= t.codt) then codt 
  8.         else &#39;&#39; 
  9.       end as codt, c4
  10.    from A t
  11.   ) a
  12. LEFT JOIN B b ON
  13. a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
  14. ORDER BY c4

See demo

huangapple
  • 本文由 发表于 2023年5月6日 18:38:53
  • 转载请务必保留本文链接：https://go.coder-hub.com/76188427.html
  • postgresql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定