質問編集履歴

3

書式の改善

2021/11/26 04:51

投稿

seastar
seastar

スコア62

test CHANGED
File without changes
test CHANGED
@@ -104,7 +104,7 @@
104
104
 
105
105
  1 2021/11/27 title-2
106
106
 
107
- 2 2021/11/25 title-4
107
+ 2 2021/11/25 title-4,title-5
108
108
 
109
109
 
110
110
 

2

書式の改善

2021/11/26 04:51

投稿

seastar
seastar

スコア62

test CHANGED
File without changes
test CHANGED
@@ -31,10 +31,6 @@
31
31
 
32
32
 
33
33
  ```SQL
34
-
35
- ※, LISTAGG(b.name, ',') WITHIN GROUP (order by b.name)※
36
-
37
-
38
34
 
39
35
  CREATE TABLE mysql.test1 (id int, book_id int, lend_date Date)
40
36
 
@@ -88,6 +84,28 @@
88
84
 
89
85
  ```
90
86
 
87
+ 現状の結果
88
+
89
+ id lend_date name
90
+
91
+ 1 2021/11/26 title-1
92
+
93
+ 1 2021/11/27 title-2
94
+
95
+ 2 2021/11/25 title-4
96
+
97
+
98
+
99
+ 欲しい結果
100
+
101
+ id lend_date name
102
+
103
+ 1 2021/11/26 title-1,title-3
104
+
105
+ 1 2021/11/27 title-2
106
+
107
+ 2 2021/11/25 title-4
108
+
91
109
 
92
110
 
93
111
  お忙しいところ見て頂きありがとうございます。

1

書式の改善

2021/11/26 04:50

投稿

seastar
seastar

スコア62

test CHANGED
File without changes
test CHANGED
@@ -32,43 +32,59 @@
32
32
 
33
33
  ```SQL
34
34
 
35
+ ※, LISTAGG(b.name, ',') WITHIN GROUP (order by b.name)※
36
+
37
+
38
+
39
+ CREATE TABLE mysql.test1 (id int, book_id int, lend_date Date)
40
+
41
+ INSERT INTO mysql.test1 VALUES(1, 1, '2021/11/26')
42
+
43
+ INSERT INTO mysql.test1 VALUES(1, 2, '2021/11/27')
44
+
45
+ INSERT INTO mysql.test1 VALUES(1, 3, '2021/11/26')
46
+
47
+ INSERT INTO mysql.test1 VALUES(2, 4, '2021/11/25')
48
+
49
+ INSERT INTO mysql.test1 VALUES(2, 5, '2021/11/25')
50
+
51
+
52
+
53
+ CREATE TABLE mysql.test2 (book_id int, name varchar(10))
54
+
55
+ INSERT INTO mysql.test2 VALUES(1, 'title-1')
56
+
57
+ INSERT INTO mysql.test2 VALUES(2, 'title-2')
58
+
59
+ INSERT INTO mysql.test2 VALUES(3, 'title-3')
60
+
61
+ INSERT INTO mysql.test2 VALUES(4, 'title-4')
62
+
63
+ INSERT INTO mysql.test2 VALUES(5, 'title-5')
64
+
65
+
66
+
35
67
  SELECT
36
68
 
37
- pay.member_id
38
-
39
- , LISTAGG(b.name, ',') WITHIN GROUP (order by b.name)※
69
+ a.id, a.lend_date, b.name ←※LISTAGG(b.name, ',') WITHIN GROUP (order by b.name)※
40
-
41
- , DATE_FORMAT(a.date, '%Y年%c月%e日') as date
42
70
 
43
71
  FROM
44
72
 
45
- TEST1 a
73
+ mysql.test1 a
46
74
 
47
75
  INNER JOIN
48
76
 
49
-    TEST2 b
77
+ mysql.test2 b
50
78
 
51
79
  ON
52
80
 
53
- a.id = b.id
81
+ a.book_id = b.book_id
54
-
55
- WHERE
56
-
57
- a.date BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59'
58
82
 
59
83
  GROUP BY
60
84
 
61
- a.id
85
+ a.id
62
86
 
63
- , DATE_FORMAT(date, '%Y年%c月%e日')
87
+ , DATE_FORMAT(a.lend_date, '%Y年%c月%e日')
64
-
65
- ORDER BY
66
-
67
- a.id
68
-
69
- , a.date
70
-
71
- , b.name
72
88
 
73
89
  ```
74
90