MySQL聚合

Question

大家好，我正在尝试汇总客户正在进行的搜索次数。我目前有这个工作 1 天。我还想在该周、月、年和总计的搜索列中放置

    USE live_travelcoglog; 
    SELECT lu.Name, lu.UID, IFNULL(l.AgentId, 'CP Total') AS "CP", COUNT(*) AS   "DateTotal", MAX(l.Submitted) AS "LastSearchTime" 
    FROM logs l INNER JOIN live_travelcog.users lu ON l.ChannelPartnerId = lu.CustId 
    WHERE Submitted BETWEEN '2014-04-23 00:00:00' AND '2014-04-23 23:59:59' 
    AND l.MessageType = 'COG_HotelAvail_RS' 
    GROUP BY lu.Name, l.AgentId ASC WITH ROLLUP;

现在我可以针对我所追求的不同值运行查询，但我确信有一种更好的方法可以将它们全部组合在一起。如果有人能给我指明正确的方向，我们将不胜感激。

谢谢

达兹

Answer 1

这就是您要找的那种东西吗？

USE live_travelcoglog; 
SELECT
    lu.Name,
    lu.UID,
    IFNULL(l.AgentId, 'CP Total') AS "CP",
    SUM(Submitted BETWEEN '2014-04-23 00:00:00' AND '2014-04-23 23:59:59') AS DateTotal,
    SUM(Submitted BETWEEN '2014-04-17 00:00:00' AND '2014-04-23 23:59:59') AS WeekTotal,
    SUM(Submitted BETWEEN '2014-04-01 00:00:00' AND '2014-04-23 23:59:59') AS MonthTotal,
    MAX(l.Submitted) AS "LastSearchTime" 
FROM logs l
INNER JOIN live_travelcog.users lu
    ON l.ChannelPartnerId = lu.CustId 
WHERE
    l.MessageType = 'COG_HotelAvail_RS'
GROUP BY
    lu.Name,
    l.AgentId ASC
WITH ROLLUP;