php - 如何使用此查询获取最多 10 行和 10 分钟的行？

Question

我正在使用以下查询来查找与过去 2 天相比我的网页的pageviews 的差异

SELECT PAGE, COUNT( * ) AS poso,
       (SUM(DATE(TIMESTAMP) = CURDATE()) - 
        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 
DAY))
       ) diff
FROM  `behaviour` 
WHERE DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 
DAY ) 
GROUP BY PAGE;

查询将我作为页面返回。然而，我想要做的是只有 10 行具有最大的正差异，而 10 行具有最大的负差异。

我怎样才能做到这一点？

Answer 1

使用两个查询的联合，一个获得前 10 个，另一个获得后 10 个。查询看起来像这样:

更新的答案

SELECT 
    PAGE
    , poso
    , diff
from
    (
        (
            SELECT PAGE, COUNT( * ) AS poso,
                (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                    SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                ) diff
            FROM  `behaviour` 
            WHERE 
                DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) 
            GROUP BY PAGE
            ORDER BY (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                    ) desc
            LIMIT 10
        ) 
        UNION
        (   
            SELECT PAGE, COUNT( * ) AS poso,
                (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                    SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                ) diff
            FROM  `behaviour` 
            WHERE 
                DATE( TIMESTAMP ) >= DATE_SUB( CURDATE( ) , INTERVAL 1 DAY ) 
            GROUP BY PAGE
            ORDER BY (SUM(DATE(TIMESTAMP) = CURDATE()) - 
                        SUM(DATE(TIMESTAMP) = DATE_SUB(CURDATE( ), INTERVAL 1 DAY))
                    ) asc
            LIMIT 10
        ) 
    ) as u
order by diff;