时间戳

首页 > 解决方案 > 如何在子查询中使用“分组依据”？

sql - 如何在子查询中使用“分组依据”？

问题描述

我想在一个状态下找到每个驱动程序 ID 的百分比（在本例中为“isDriving”）。这是我的数据集：

(id, duration, state, driverid) VALUES ('1', '3000', 'isDriving', 'b65');

(id, duration, state, driverid) VALUES ('1', '2500', 'isDriving', 'b65');

(id, duration, state, driverid) VALUES ('1', '500', 'isIdling', 'b65');

(id, duration, state, driverid) VALUES ('1', '5500', 'isIdling', 'b65');

(id, duration, state, driverid) VALUES ('1', '300', 'isDriving', 'b34');

(id, duration, state, driverid) VALUES ('1', '200', 'isIdling', 'b34');

(id, duration, state, driverid) VALUES ('1', '200', 'isDriving', 'b15');

(id, duration, state, driverid) VALUES ('1', '500', 'isIdling', 'b15');

我试过这个查询：

select driverid, 
    (sum(duration) / (select sum(duration) from table_name1 group by driverid)) * 100 as percent
from table_name1 
where state='isDriving' 
group by driverid

我期望这样的输出：

------------------
driverid  percent |
------------------
b65         50    |
b34         60    |
b15         28.6  |

但告诉我：子查询返回超过 1 行

标签： sqlgroup-bysubquery

解决方案

使用条件聚合：

select 
  driverid,
  round(100.0 * sum(case when state = 'isDriving' then duration else 0 end) / sum(duration), 1) percent    
from tablename
group by driverid;

请参阅演示。
结果：

| driverid | percent |
| -------- | ------- |
| b15      | 28.6    |
| b34      | 60      |
| b65      | 47.8    |

推荐阅读