特定条件下mysql中的sql计算

xvw2m8pv 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(397)

我在下表提到：

ID    Var1      Date
A-1   5         2017-04-01 18:45:05
A-2   8         2017-04-01 18:45:05
A-3   5         2017-04-01 18:45:05
A-3   5         2017-04-02 18:45:05
A-4   8         2017-04-02 18:45:05
A-5   8         2017-04-02 18:45:05
A-6   8         2017-04-03 18:45:05

我想计算 ID 有 Var1 值为 8 在每一天。我试过这个，但没用。

SELECT
  COUNT( ID) AS ID, 
  ((COUNT( Var1) / (COUNT( ID) )) * 100 ) AS percentage
FROM
  Table1 
WHERE Var1=8
GROUP BY
  Date(Date);

sql mysql

来源：https://stackoverflow.com/questions/50112495/calculation-in-mysql-with-specific-conditions

2条答案

按热度按时间

v09wglhw1#

drop table if exists t;
create table t (ID varchar(3),   Var1 int,     Dt datetime);
insert into t values
('A-1'  , 5     ,    '2017-04-01 18:45:05'),
('A-2'  , 8     ,    '2017-04-01 18:45:05'),
('A-3'  , 5     ,    '2017-04-01 18:45:05'),
('A-3'  , 5     ,    '2017-04-02 18:45:05'),
('A-4'  , 8     ,    '2017-04-02 18:45:05'),
('A-6'  , 8     ,    '2017-04-03 18:45:05');

select dt,count(id) NofID,
         sum(case when var1 = 8 then 1 else 0 end) nofeights,
         (sum(case when var1 = 8 then 1 else 0 end) / count(id)) * 100 eights
from t
group by dt;

+---------------------+-------+-----------+----------+
| dt                  | NofID | nofeights | eights   |
+---------------------+-------+-----------+----------+
| 2017-04-01 18:45:05 |     3 |         1 |  33.3333 |
| 2017-04-02 18:45:05 |     2 |         1 |  50.0000 |
| 2017-04-03 18:45:05 |     1 |         1 | 100.0000 |
+---------------------+-------+-----------+----------+
3 rows in set (0.00 sec)

赞(0）回复(0）举报 2021-06-21

vjhs03f72#

我建议这样写：

SELECT Date(Date), SUM(Var1 = 8) AS ID, 
       AVG(Var1 = 8) * 100 AS percentage
FROM Table1 
GROUP BY Date(Date);

它使用mysql快捷方式，允许在数字上下文中将布尔值视为数字。

赞(0）回复(0）举报 2021-06-21

我来回答

特定条件下mysql中的sql计算

2条答案

相关问题

热门标签

最新问答