MySQL 表达式检索百分比

Question

我有一个包含 3 列的表格 (Table1):

姓名 |注册日期 |活跃

我想知道 RegDate 列中两个日期之间处于事件状态 (Active=1) 的行的百分比是多少。

例如:在“2013-01-01”和“2013-03-31”之间(RegDate 字段)我想知道活跃百分比是多少(事件=1)？

可以在单个 MySQL 表达式中完成此操作吗？

目前我有这个:

$sql = "SELECT * FROM Table1 t WHERE t.RegDate <= '2013-03-31' AND t.RegDate >= '2013-01-01'";

$sql_executa = $conn->prepare($sql) or die ($sql);

$sql_executa->execute();

$total = $sql_executa->rowCount();

$sql_2 = "SELECT * FROM Table1 t WHERE t.RegDate <= '2013-03-31' AND t.RegDate >= '2013-01-01' AND Active = 1";

$sql_executa_2 = $conn->prepare($sql_2) or die ($sql_2);

$sql_executa_2->execute();

$total_2 = $sql_executa_2->rowCount();

$Percentagem = ($total_2 * 100) / $total; // This is the answer I need!

提前致谢

Answer 1

尝试:

SELECT sum( case when Active=1 then 1 else 0 end )
       /
       count(*) 
       * 100  
       As Percent_of_Active_records
FROM Table1 t 
WHERE t.RegDate <= '2013-03-31' 
  AND t.RegDate >= '2013-01-01'