按月分组，并在日期为 yyyy-mm-dd 时进行汇总

发布于 2025-01-16 23:53:41 字数 265 浏览 0 评论 0原文

我正在上 SQL 在线课程，我需要 yyyy-mm-dd 格式的日期。我需要按月分组以显示 MedalId 的总销售额和一个月内销售的总 MedalId。

我似乎无法设计一种使用这种格式汇总月份的方法。任何建议和指导将不胜感激：

SELECT date, COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY date WITH ROLLUP;

原文

I'm doing an online course in SQL and I need to have date in yyyy-mm-dd format. I need to group by month to show total sales of medalId and total medalId sold in a month.

I just can't seem to be able to engineer a way to rollup with month using this format. Any advice and guidance would be really appreciated:

SELECT date, COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY date WITH ROLLUP;

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我喜欢麦丽素 2025-01-23 23:53:41

MySQL 的列数据类型为DATE。使用它。尽量避免使用 VARCHAR() 或 CHAR() 列来保存日期。这是值得的，因为当你这样做时，你会得到很多很酷的日期算术。而且，它们排序正确。您的 2020-03-25 格式与 DATE 数据类型兼容。

你想要这样的东西，使用 LAST_DAY() 日期算术函数。

SELECT LAST_DAY(date) month_ending, 
       COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY LAST_DAY(date) WITH ROLLUP;

专业提示：在进行数据库编程时，值得花时间学习 DBMS 的日期/时间和字符串处理函数。

MySQL has the DATE datatype for columns. Use it. Take the trouble to avoid using VARCHAR() or CHAR() columns to hold dates. It's worth it because you get a lot of cool date arithmetic when you do that. Also, they sort correctly. Your 2020-03-25 format is compatible with the DATE datatype.

You want something like this, using the LAST_DAY() date-arithmetic function.

SELECT LAST_DAY(date) month_ending, 
       COUNT(medalId) AS totalProducts, SUM(medalId) as totalSales
from orders
GROUP BY LAST_DAY(date) WITH ROLLUP;

Pro tip: When doing database programming, it is worth your time to learn your DBMS's date/time and string processing functions.

回复收藏 0 原文

~没有更多了~