需要帮助创建包含两个不同总和的视图

I have a view that's currently defined as

我有一个目前被定义为的视图

CREATE VIEW riders AS select ridelog.uid AS rid,sum(ridelog.distance) AS distance from ridelog group by ridelog.uid

CREATE VIEW车手AS选择ridelog.uid AS rid,sum(ridelog.distance)AS ridelog.uid与ridelog组的距离

What I'd like to do is add a second field that shows the distance y-t-d. i.e, the sum of the distances for rides that occurred since January 1st of the current year.

我想要做的是添加一个显示距离y-t-d的第二个字段。即自当年1月1日以来发生的游乐设施的距离总和。

The problem is, I can't figure out how to say "current year" in mysql or if I can create a view that has sums with different criteria in them.

问题是,我无法弄清楚如何在mysql中说出“当前年份”,或者我是否可以创建一个具有不同标准的视图。

Is this possible?

这可能吗?

1 个解决方案

#1

This will do it - as long as your view should always contain a column for the current year.

这样做 - 只要您的视图应始终包含当前年份的列。

CREATE VIEW riders AS 
    SELECT 
        ridelog.uid AS rid,
        SUM(ridelog.distance) AS distance,
        SUM(IF(YEAR(ridelog.<<date_time_column>>) = YEAR(NOW()), ridelog.distance, 0) AS distanceThisYear 
    FROM ridelog 
    GROUP BY ridelog.uid

With this approach you cannot specify the year for which you want the distance calculated. Another possibility would be to use a stored procedure with the year as its parameter.

使用此方法,您无法指定计算距离的年份。另一种可能性是使用以年份为参数的存储过程。

#1