teratail
回答率
85.37%
teratail 10th-anniversary
teratail 10th-anniversary
質問するログイン新規登録
トップSQL Anywhereに関する質問SQLでGroupByして3テーブル結合したい

編集履歴

質問編集履歴

2

誤字

2022/04/06 00:34

投稿

ryoucyan2000
ryoucyan2000

スコア18

test CHANGED
File without changes
test CHANGED
@@ -116,8 +116,3 @@
116
116
  |:--|:--:|:--:|:--:|--:|
117
117
  |3|123|machine1|1|B001
118
118
  |5|223|machine2|2|A003
119
-
120
-
121
- |列1|列2|列3|1|
122
- |:--|:--:|:--:|--:|
123
-
SQL AnywhereMySQL

1

質問の仕方でアドバイスいただきましたので内容を修正しました。データをcreate tableで追加　SQLの修正　得たい結果の追加

2022/04/05 16:22

投稿

ryoucyan2000
ryoucyan2000

スコア18

test CHANGED
File without changes
test CHANGED
@@ -1,3 +1,4 @@
1
+ 質問の仕方でアドバイスいただきましたので内容を修正しました。
1
2
  3個のテーブルを結合したいのですがうまくいきません
2
3
  Mysql8.0です。
3
4
 
@@ -12,55 +13,111 @@
12
13
  Error: ER_NO_SUCH_TABLE: Table 'pa.latest2' doesn't exist
13
14
  となってしまします。なぜそうなってしまうのかわからず教えて下さい
14
15
 
16
+ 質問途中で②に対して①と③をjoinすればよかったと思いましたが
17
+ テーブルをまたいだ結合方法として知りたいのでご教授いただきたく
18
+
19
+
20
+
21
+
15
22
  ### 2テーブル成功
16
23
  ```SQL
17
24
  SELECT
18
- pa.qsi.id AS id,
25
+ psi.id AS id,
19
- pa.qsi.ieee_address AS ieee_address,
26
+ psi.ieee_address AS ieee_address,
20
- pa.machine_m.name AS name,
27
+ machine_m.machine_name AS name,
21
-
22
- pa.qsi.red_information AS red,
28
+ psi.red_information AS red
23
- pa.qsi.wdt_monitoring_information AS wdt_monitoring_information,
24
- pa.qsi.time_counter AS time_counter
25
29
  FROM
26
- ((pa.qsi
30
+ (psi
27
31
  JOIN (SELECT
28
- pa.qsi.ieee_address AS ieee_address,
32
+ psi.ieee_address AS ieee_address,
29
- MAX(pa.qsi.id) AS ID
33
+ MAX(psi.id) AS ID
30
34
  FROM
31
- pa.qsi
35
+ psi
32
- GROUP BY pa.qsi.ieee_address) latest ON (((pa.qsi.ieee_address = latest.ieee_address)
36
+ GROUP BY psi.ieee_address) latest ON ((psi.ieee_address = latest.ieee_address)
33
- AND (pa.qsi.id = latest.ID))))
37
+ AND (psi.id = latest.ID))
34
- JOIN pa.machine_m ON ((latest.ieee_address = pa.machine_m.ieee)));
38
+ JOIN machine_m ON (latest.ieee_address = machine_m.ieee));
35
39
  ```
36
40
 
37
41
  ### 3テーブルを結合失敗
38
42
  ```SQL
39
43
  SELECT
40
- pa.wsi.id AS id,
44
+ psi.id AS id,
41
- pa.wsi.ieee_address AS ieee_address,
45
+ psi.ieee_address AS ieee_address,
42
- pa.machine_m.name AS name,
46
+ machine_m.machine_name AS name,
43
- pa.wsi.red_information AS red,
47
+ psi.red_information AS red,
44
- pa.wsi.wdt_monitoring_information AS wdt_monitoring_information,
45
- pa.wsi.time_counter AS time_counter,
46
- pa.send_program.toolno as toolno
48
+ send_program.toolno as toolno
47
49
  FROM
48
- ((pa.wsi
50
+ ((psi
49
51
  JOIN (SELECT
50
- pa.wsi.ieee_address AS ieee_address,
52
+ psi.ieee_address AS ieee_address,
51
- MAX(pa.wsi.id) AS ID
53
+ MAX(psi.id) AS ID
52
54
  FROM
53
- pa.wsi
55
+ psi
54
- GROUP BY pa.wsi.ieee_address) latest ON ((pa.wsi.ieee_address = latest.ieee_address)
56
+ GROUP BY psi.ieee_address) latest ON ((psi.ieee_address = latest.ieee_address)
55
- AND (pa.wsi.id = latest.ID))
57
+ AND (psi.id = latest.ID))
56
58
  JOIN (select
57
- pa.send_program.machine AS machine,
59
+ send_program.machine AS machine,
58
- MAX(pa.send_program.ID) AS ID2
60
+ MAX(send_program.ID) AS ID2
59
61
  FROM
60
- pa.send_program
62
+ send_program
61
- GROUP BY pa.send_program.machine) latest2 ON ((pa.send_program.machine = latest2.machine)
63
+ GROUP BY send_program.machine) latest2 ON ((send_program.machine = latest2.machine)
62
- AND (pa.send_program.id = latest2.ID2)))
64
+ AND (send_program.id = latest2.ID2)))
63
65
 
64
- join latest2 ON latest.ieee_address = latest2.machine)
66
+ join latest2 ON machine_m.machine_name = latest2.machine)
65
- join pa.machine_m ON ((latest.ieee_address = pa.machine_m.ieee));
67
+ join machine_m ON (latest.ieee_address = machine_m.ieee);
66
68
  ```
69
+ データ例
70
+ ```SQL
71
+ create table psi(
72
+ id INT NOT NULL PRIMARY KEY,
73
+ ieee_address varchar(50) null,
74
+ red_information smallint(5) null
75
+ );
76
+
77
+ INSERT INTO psi
78
+ (id,ieee_address,red_information)
79
+ VALUES
80
+ (1,123,1),
81
+ (2,123,0),
82
+ (3,123,1),
83
+ (4,223,0),
84
+ (5,223,2);
85
+
86
+ create table machine_m(
87
+ id INT NOT NULL PRIMARY KEY,
88
+ ieee varchar(50) null,
89
+ machine_name tinytext null
90
+ );
91
+
92
+ INSERT INTO machine_m
93
+ (id,ieee,machine_name)
94
+ VALUES
95
+ (1,123,"machine1"),
96
+ (2,223,"machine2");
97
+
98
+ create table send_program(
99
+ id INT NOT NULL PRIMARY KEY,
100
+ toolno tinytext null,
101
+ machine tinytext null
102
+ );
103
+
104
+ INSERT INTO send_program
105
+ (id,toolno,machine)
106
+ VALUES
107
+ (1,"A001","machine1"),
108
+ (2,"A002","machine2"),
109
+ (3,"B001","machine1"),
110
+ (4,"A003","machine2")
111
+ ;
112
+ ```
113
+ ### 得たい結果
114
+
115
+ |ID|ieee_address|name|red|toolno|
116
+ |:--|:--:|:--:|:--:|--:|
117
+ |3|123|machine1|1|B001
118
+ |5|223|machine2|2|A003
119
+
120
+
121
+ |列1|列2|列3|1|
122
+ |:--|:--:|:--:|--:|
123
+
SQL AnywhereMySQL