回答編集履歴

3

procedure

2017/05/10 00:47

投稿

yambejp
yambejp

スコア114572

test CHANGED
@@ -61,3 +61,83 @@
61
61
  ```
62
62
 
63
63
  プロシージャとピボットとか使うともうちょい自動化してそれっぽく見えるかも
64
+
65
+
66
+
67
+ # procudure
68
+
69
+ - 以下、実行しプロシージャをつくっておきます。
70
+
71
+ ```SQL
72
+
73
+ drop procedure if exists my_proc;
74
+
75
+ delimiter //
76
+
77
+ create procedure my_proc()
78
+
79
+ begin
80
+
81
+ declare a int;
82
+
83
+ declare b varchar(100);
84
+
85
+ declare done int default 0;
86
+
87
+ declare cur cursor for
88
+
89
+ select eid,ename from event order by eid;
90
+
91
+ declare continue handler for sqlstate '02000' set done=1;
92
+
93
+
94
+
95
+ set @sql='select t1.uid,t1.uname\n';
96
+
97
+ open cur;
98
+
99
+ repeat
100
+
101
+ fetch cur into a,b;
102
+
103
+ if not done then
104
+
105
+ set @sql=concat (@sql,',sum(t2.eid=',a,' * t2.shukketu=1) as `',b,' shukketu`\n');
106
+
107
+ set @sql=concat (@sql,',sum(t2.eid=',a,' * t2.nyukin=1) as `',b,' nyukin`\n');
108
+
109
+ end if;
110
+
111
+ until done end repeat;
112
+
113
+ close cur;
114
+
115
+ set @sql=concat (@sql,' from user as t1\n');
116
+
117
+ set @sql=concat (@sql,' left join event_user as t2 on t1.uid=t2.uid\n');
118
+
119
+ set @sql=concat (@sql,' group by t1.uid\n');
120
+
121
+ prepare stmt from @sql;
122
+
123
+ execute stmt;
124
+
125
+ end
126
+
127
+ //
128
+
129
+ delimiter ;
130
+
131
+
132
+
133
+ ```
134
+
135
+ - 実行
136
+
137
+ ```SQL
138
+
139
+ call my_proc;
140
+
141
+
142
+
143
+ ```

2

調整

2017/05/10 00:47

投稿

yambejp
yambejp

スコア114572

test CHANGED
@@ -60,6 +60,4 @@
60
60
 
61
61
  ```
62
62
 
63
-
64
-
65
- ピボットとか使うともうちょいそれっぽく見えるかも
63
+ プロシージャとピボットとか使うともうちょい自動化してそれっぽく見えるかも

1

追記

2017/05/09 14:43

投稿

yambejp
yambejp

スコア114572

test CHANGED
@@ -9,3 +9,57 @@
9
9
  create tableとinsert into形式で例示いただければ
10
10
 
11
11
  SQL文は知恵を絞れると思います。
12
+
13
+
14
+
15
+ # sample
16
+
17
+ - 元データ
18
+
19
+ ```SQL
20
+
21
+ create table event (eid int unique,ename varchar(30));
22
+
23
+ insert into event values(1,'child event 1'),(2,'child event 2'),(3,'child event 3');
24
+
25
+ create table user (uid int unique,uname varchar(30));
26
+
27
+ insert into user values(1,'tanaka'),(2,'yoshida'),(3,'suzuki'),(4,'sato'),(5,'honda');
28
+
29
+ create table event_user(eid int,uid int ,shukketu tinyint not null,nyukin tinyint not null,unique(eid,uid));
30
+
31
+ insert into event_user values(1,1,1,1),(1,2,0,0),(1,3,1,0),(2,1,0,1),(3,4,1,0);
32
+
33
+ ```
34
+
35
+ - 検索
36
+
37
+ ```SQL
38
+
39
+ select t1.uid,t1.uname
40
+
41
+ ,sum(t2.eid=1 * t2.shukketu=1) as e1s
42
+
43
+ ,sum(t2.eid=1 * t2.nyukin=1) as e1n
44
+
45
+ ,sum(t2.eid=2 * t2.shukketu=1) as e2s
46
+
47
+ ,sum(t2.eid=2 * t2.nyukin=1) as e2n
48
+
49
+ ,sum(t2.eid=3 * t2.shukketu=1) as e3s
50
+
51
+ ,sum(t2.eid=3 * t2.nyukin=1) as e3n
52
+
53
+ from user as t1
54
+
55
+ left join event_user as t2 on t1.uid=t2.uid
56
+
57
+ group by t1.uid
58
+
59
+
60
+
61
+ ```
62
+
63
+
64
+
65
+ ピボットとか使うともうちょいそれっぽく見えるかも