Question

我试图在单一询问中履行两个不同的特别论坛。

user   points   date_achieved
-----------------------------
Foo    10       2000-01-01
Foo    20       2011-11-18
Bar    10       2000-01-01
Bar    20       2011-11-15
Bar    30       2011-11-18

(1) 总数

SELECT user, SUM(points) AS points_total
FROM myTable
GROUP BY user

(2) 上个月累积的要点

SELECT user, SUM(points) AS points_month
FROM myTable
WHERE date_achieved >= CURRENT_DATE - INTERVAL 1 MONTH
GROUP BY user

如何以单一方式实现这一目标?

Many thanks.

Answer 1

在符合你的标准时,仅用案件说明计数。

SELECT 
       user, 
        SUM(points) AS points,
        SUM(case  
            when date_achieved >= (CURRENT_DATE - INTERVAL 1 MONTH) 
            THEN points 
             else 0 END) points_last_Month
FROM myTable
GROUP BY user

Answer 2

select
    user,
    sum(points) as points_total,
    sum(
        case
            when date_achieved >= current_date - interval 1 month then points
            else 0
        end
    ) as points_month
from myTable
group by user

友情链接