回答編集履歴
3
更新
answer
CHANGED
|
@@ -63,7 +63,7 @@
|
|
|
63
63
|
```
|
|
64
64
|
- 結果
|
|
65
65
|
|
|
66
|
-
|id|name|x|y|create_time|cnt_0_0|cnt_0_1|cnt_1_0|cnt_1_1|
|
|
66
|
+
|id|name|x|y|create_time|cnt_0_0|cnt_0_1|cnt_1_0|cnt_1_1|
|
|
67
67
|
|--:|--:|--:|--:|--:|--:|--:|--:|--:|
|
|
68
68
|
|1|A|0|0|2019-11-20 01:00:00|0|0|0|0|
|
|
69
69
|
|2|A|0|1|2019-11-20 01:10:00|1|0|0|0|
|
2
しゅうせい
answer
CHANGED
|
@@ -36,4 +36,41 @@
|
|
|
36
36
|
|1|1|2019-11-20 01:50:00|1|
|
|
37
37
|
|2|0|2019-11-20 02:00:00|1|
|
|
38
38
|
|0|1|2019-11-20 03:50:00|1|
|
|
39
|
-
|0|1|2019-11-20 05:50:00|1|
|
|
39
|
+
|0|1|2019-11-20 05:50:00|1|
|
|
40
|
+
|
|
41
|
+
# 修正版
|
|
42
|
+
```SQL
|
|
43
|
+
create table tbl(id int primary key auto_increment,name varchar(10),x int,y int,create_time datetime,
|
|
44
|
+
index(name,x,y,create_time));
|
|
45
|
+
insert into tbl(name,x,y,create_time) values
|
|
46
|
+
('A',0,0,'2019-11-20 01:00'),
|
|
47
|
+
('A',0,1,'2019-11-20 01:10'),
|
|
48
|
+
('B',0,1,'2019-11-20 01:20'),
|
|
49
|
+
('A',0,1,'2019-11-20 01:30'),
|
|
50
|
+
('A',1,1,'2019-11-20 01:40'),
|
|
51
|
+
('B',1,1,'2019-11-20 01:50'),
|
|
52
|
+
('A',0,1,'2019-11-20 02:00'),
|
|
53
|
+
('A',0,1,'2019-11-20 03:50'),
|
|
54
|
+
('B',0,1,'2019-11-20 05:50');
|
|
55
|
+
|
|
56
|
+
select a.*,
|
|
57
|
+
coalesce((select sum(x=0 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_0,
|
|
58
|
+
coalesce((select sum(x=0 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_1,
|
|
59
|
+
coalesce((select sum(x=1 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_0,
|
|
60
|
+
coalesce((select sum(x=1 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_1
|
|
61
|
+
from tbl as a
|
|
62
|
+
order by id;
|
|
63
|
+
```
|
|
64
|
+
- 結果
|
|
65
|
+
|
|
66
|
+
|id|name|x|y|create_time|cnt_0_0|cnt_0_1|cnt_1_0|cnt_1_1||
|
|
67
|
+
|--:|--:|--:|--:|--:|--:|--:|--:|--:|
|
|
68
|
+
|1|A|0|0|2019-11-20 01:00:00|0|0|0|0|
|
|
69
|
+
|2|A|0|1|2019-11-20 01:10:00|1|0|0|0|
|
|
70
|
+
|3|B|0|1|2019-11-20 01:20:00|0|0|0|0|
|
|
71
|
+
|4|A|0|1|2019-11-20 01:30:00|1|1|0|0|
|
|
72
|
+
|5|A|1|1|2019-11-20 01:40:00|1|2|0|0|
|
|
73
|
+
|6|B|1|1|2019-11-20 01:50:00|0|1|0|0|
|
|
74
|
+
|7|A|0|1|2019-11-20 02:00:00|1|2|0|1|
|
|
75
|
+
|8|A|0|1|2019-11-20 03:50:00|1|3|0|1|
|
|
76
|
+
|9|B|0|1|2019-11-20 05:50:00|0|1|0|1|
|
1
かくにｎ
answer
CHANGED
|
@@ -20,4 +20,20 @@
|
|
|
20
20
|
select a.*,
|
|
21
21
|
(select sum(x=1 and y=2) from tbl where a.create_time>create_time) as cnt
|
|
22
22
|
from tbl as a;
|
|
23
|
-
```
|
|
23
|
+
```
|
|
24
|
+
|
|
25
|
+
# 確認
|
|
26
|
+
「●期待する結果」がなぜそういう結果になるかわかりません
|
|
27
|
+
|
|
28
|
+
自分のcreate_timeより前のx=1かつy=2の件数だったらこうなりませんか？
|
|
29
|
+
|x|y|create_time|count|
|
|
30
|
+
|--:|--:|--:|--:|
|
|
31
|
+
|0|0|2019-11-20 01:00:00|NULL|
|
|
32
|
+
|0|1|2019-11-20 01:10:00|0|
|
|
33
|
+
|0|2|2019-11-20 01:20:00|0|
|
|
34
|
+
|0|1|2019-11-20 01:30:00|0|
|
|
35
|
+
|1|2|2019-11-20 01:40:00|0|
|
|
36
|
+
|1|1|2019-11-20 01:50:00|1|
|
|
37
|
+
|2|0|2019-11-20 02:00:00|1|
|
|
38
|
+
|0|1|2019-11-20 03:50:00|1|
|
|
39
|
+
|0|1|2019-11-20 05:50:00|1|
|