質問編集履歴
1
CRATE TABLE、TABLE DATA、期待される結果を追加
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -6,16 +6,58 @@
|
|
|
6
6
|
|
|
7
7
|
```SQL
|
|
8
8
|
SELECT
|
|
9
|
-
TABLE_A.A
|
|
9
|
+
TABLE_A.A AS AA
|
|
10
|
-
, TABLE_A.B
|
|
10
|
+
, TABLE_A.B AS AB
|
|
11
|
-
, TABLE_A.C
|
|
11
|
+
, TABLE_A.C AS AC
|
|
12
|
-
, (SELECT TABLE_B.A FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1)
|
|
12
|
+
, (SELECT TABLE_B.A FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1) AS BA
|
|
13
|
-
, (SELECT TABLE_B.B FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1)
|
|
13
|
+
, (SELECT TABLE_B.B FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1) AS BB
|
|
14
|
-
, (SELECT TABLE_B.C FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1)
|
|
14
|
+
, (SELECT TABLE_B.C FROM TABLE_B WHERE TABLE_B.D = TABLE_A.C ORDER BY TABLE_B.E ASC LIMIT 1) AS BC
|
|
15
15
|
FROM
|
|
16
16
|
TABLE_A
|
|
17
|
-
|
|
17
|
+
ORDER BY
|
|
18
|
+
AA, AB, AC
|
|
18
19
|
```
|
|
20
|
+
|
|
21
|
+
``` CRATE TABLE
|
|
22
|
+
CREATE TABLE public.table_a (
|
|
23
|
+
a varchar(10) NOT NULL,
|
|
24
|
+
b varchar(10) NULL,
|
|
25
|
+
c varchar(10) NOT NULL,
|
|
26
|
+
d varchar(10) NULL,
|
|
27
|
+
e varchar(10) NULL,
|
|
28
|
+
CONSTRAINT table_a_pkey PRIMARY KEY (a, c)
|
|
29
|
+
);
|
|
30
|
+
CREATE TABLE public.table_b (
|
|
31
|
+
a varchar(10) NULL,
|
|
32
|
+
b varchar(10) NULL,
|
|
33
|
+
c varchar(10) NULL,
|
|
34
|
+
d varchar(10) NOT NULL,
|
|
35
|
+
e varchar(10) NOT NULL,
|
|
36
|
+
CONSTRAINT table_b_pkey PRIMARY KEY (d, e)
|
|
37
|
+
);
|
|
38
|
+
```
|
|
39
|
+
|
|
40
|
+
``` TABLE DATA
|
|
41
|
+
INSERT INTO table_a(a, b, c, d, e)VALUES('A1', 'B1', 'C1', '', '');
|
|
42
|
+
INSERT INTO table_a(a, b, c, d, e)VALUES('A2', 'B2', 'C2', '', '');
|
|
43
|
+
INSERT INTO table_a(a, b, c, d, e)VALUES('A3', 'B3', 'C3', '', '');
|
|
44
|
+
|
|
45
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C101', 'b_C101', 'c_C101', 'C1', '01');
|
|
46
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C102', 'b_C102', 'c_C102', 'C1', '02');
|
|
47
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C103', 'b_C103', 'c_C103', 'C1', '03');
|
|
48
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C202', 'b_C202', 'c_C202', 'C2', '02');
|
|
49
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C203', 'b_C203', 'c_C203', 'C2', '03');
|
|
50
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C204', 'b_C204', 'c_C204', 'C2', '04');
|
|
51
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C304', 'b_C304', 'c_C304', 'C3', '04');
|
|
52
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C305', 'b_C305', 'c_C305', 'C3', '05');
|
|
53
|
+
INSERT INTO table_b(a, b, c, d, e)VALUES('C306', 'b_C306', 'c_C306', 'C3', '06');
|
|
54
|
+
```
|
|
55
|
+
|
|
56
|
+
### 期待される結果
|
|
57
|
+
A1 B1 C1 C101 b_C101 c_C101
|
|
58
|
+
A2 B2 C2 C202 b_C202 c_C202
|
|
59
|
+
A3 B3 C3 C304 b_C304 c_C304
|
|
60
|
+
|
|
19
61
|
### 補足情報（FW/ツールのバージョンなど）
|
|
20
62
|
実行環境はPostgres11です。
|
|
21
63
|
|