代码之家 › 专栏 › 技术社区 › AnApprentice

如何将SQL查询的结果输出到报表表中?[副本]

mariadb sql mysql

AnApprentice · 技术社区 · 7 年前

这个问题已经有了答案:

multiple query same table but in different columns mysql 4个答案

我有一个下表字段:

Invitations (user_id, type, created_at, completed_at)

我目前可以通过运行以下查询和手动计算获得上周的邀请对话率。

SELECT * 
FROM invitations
WHERE created_at >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND created_at < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
AND user_id != 1
AND type = 'email'
ORDER BY completed_at, created_at

SQL是否可以输出更多的报表。。。返回的东西:

LAST WEEK | Total Invitations | Invitations Completed | % Conversion    
          | 100               | 50                    | 50%  
TWO WEEKS | Total Invitations | Invitations Completed | % Conversion    
          | 100               | 60                    | 60%

这种情况在SQL中可能发生吗?还是我需要用应用程序逻辑创建它?

1 回复 | 直到 7 年前

sticky bit 7 年前

也许你想用 count() 然后做一个 UNION ALL .

SELECT 'LAST WEEK' `Period`,
       count(created_at) `Total Invitations`,
       count(completed_at) `Invitations completed`,
       concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion` 
       FROM invitations
       WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 6 DAY
             AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
             AND user_id <> 1
             AND type = 'email'
UNION ALL
SELECT 'TWO WEEKS' `Period`,
       count(created_at) `Total Invitations`,
       count(completed_at) `Invitations completed`,
       concat(count(completed_at) / count(created_at) * 100, '%') `% Conversion` 
       FROM invitations
       WHERE created_at >= curdate() - INTERVAL dayofweek(curdate()) + 13 DAY
             AND created_at < curdate() - INTERVAL dayofweek(curdate()) - 1 DAY
             AND user_id <> 1
             AND type = 'email';

count(completed_at) 只计算行,其中 completed_at 不为空。我想 完成时间 只有在邀请未完成时才为空。 count(created_at) 工作模拟。但我假设该列中没有空值(如果是,那么这些行将与 WHERE 条款,所以他们甚至都不是计数的候选人)。 全部联合 只需将两个结果集合并(不排除重复项,这些重复项无论如何都不在其中,至少 Period 不同)。