我正在尝试使用以下规则从以下session_values表计算val字段的总和：
如果sessionid只有'start'或'stop'，则val字段将用于sessionid。例如，sessionid 2的值为6而sessionid 5的值为7
mysql> select * from session_values;
+-------------------------+-----------+-------+------+
| time | sessionid | event | val |
+-------------------------+-----------+-------+------+
| 2019-07-24 15:52:24.671 | 1 | start | 5 |
| 2019-07-24 15:52:27.730 | 1 | stop | 10 |
| 2019-07-24 15:52:30.658 | 2 | stop | 6 |
| 2019-07-24 15:52:33.919 | 3 | stop | 8 |
| 2019-07-24 15:52:44.671 | 4 | start | 5 |
| 2019-07-24 15:52:47.293 | 4 | stop | 5 |
| 2019-07-24 16:36:03.097 | 5 | start | 7 |
| 2019-07-24 16:36:09.399 | 6 | start | 3 |
+-------------------------+-----------+-------+------+
请参考以下网址以获取详细信息：http : //www.sqlfiddle.com/#!9/23b7a1/4
我使用以下sql代码来计算仅一个事件的sessionid之和：
select sum(val) from (
select * from (select * from session_values ) s1
where s1.event in ('start','stop')
group by sessionid
HAVING COUNT(DISTINCT s1.event) = 1
) s2 ;
可以正常工作并返回24
以下sql用于具有多个事件的sessionid：
select sum(val) from (
select * from (select * from session_values ) s1
where s1.event in ('start','stop')
group by sessionid
HAVING COUNT(DISTINCT s1.event) = 2
) s2 ;
我有两个问题：
第一个问题是，我找不到一种方法来确保第二个SQL仅返回“ stop”事件（包含多个事件），这意味着在运行以下查询时，我只会获得“ start”事件。是否可以增强查询以仅返回“停止”事件？
select * from (
select * from (select * from session_values ) s1
where s1.event in ('start','stop')
group by sessionid
HAVING COUNT(DISTINCT s1.event) = 2
) s2 ;
+-------------------------+-----------+-------+------+
| time | sessionid | event | val |
+-------------------------+-----------+-------+------+
| 2019-07-24 15:52:24.671 | 1 | start | 5 |
| 2019-07-24 15:52:44.671 | 4 | start | 5 |
+-------------------------+-----------+-------+------+
第二个问题是，我想按照说明的规则组合一个SQL查询来计算值的总和。是否可以将两个SQL结合在一起？
stop 起止对的事件
select *
from session_values
where sessionid in (
select sessionId
from session_values
where event in ('start','stop')
group by sessionid
HAVING COUNT(1)=2
)
and event='stop'
和总和：
select sum(val)
from session_values
where sessionid in (
select sessionId
from session_values
where event in ('start','stop')
group by sessionid
HAVING COUNT(1)=2
)
and event='stop'
or sessionid in (
select sessionId
from session_values
where event in ('start','stop')
group by sessionid
HAVING COUNT(1)=1
)
and event='start'
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句