MySQL 根据用户ID查询某一天的消费汇总，以及按照指定时间查询数据

最新推荐文章于 2023-11-30 12:18:31 发布

小洪帽i

最新推荐文章于 2023-11-30 12:18:31 发布

阅读量1.5k

点赞数

分类专栏： MySQL SQL语句优化文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/qq_39408664/article/details/120735065

版权

MySQL 同时被 2 个专栏收录

86 篇文章 4 订阅

订阅专栏

SQL语句优化

5 篇文章 0 订阅

订阅专栏

根据用户ID查询某一天的消费汇总



//time为字段，字段类型为int的使用这个
select uid,SUM(price) as prices from xiaofei where FROM_UNIXTIME(time,'%Y-%m-%d') = '2021-08-08' GROUP BY uid

//字段类型为 timestamp  或日期格式的（1970-01-01 00:00:00）使用这个
select uid,SUM(price) as prices from xiaofei where DATE_FORMAT(time,'%Y-%m-%d') = '2021-08-08' GROUP BY uid

根据用户ID查询某一个月的消费汇总


//time 为字段，字段类型为int的使用这个
select uid,SUM(price) as prices from xiaofei where FROM_UNIXTIME(time,'%Y-%m') = '2021-08' GROUP BY uid

//字段类型为 timestamp 或日期格式的使用这个
select uid,SUM(price) as prices from xiaofei where DATE_FORMAT(time,'%Y-%m') = '2021-08' GROUP BY uid

根据用户ID查询某一年的消费汇总



//time 为字段，字段类型为int的使用这个
select uid,SUM(price) as prices from xiaofei where FROM_UNIXTIME(time,'%Y') = '2021' GROUP BY uid

//字段类型为 timestamp 或日期格式的使用这个
select uid,SUM(price) as prices from xiaofei where DATE_FORMAT(time,'%Y') = '2021' GROUP BY uid

根据用户ID查询某一小时的消费汇总



//time 为字段，字段类型为int的使用这个
select uid,SUM(price) as prices from xiaofei where FROM_UNIXTIME(time,'%H') = '11' GROUP BY uid

//字段类型为 timestamp 或日期格式的使用这个
select uid,SUM(price) as prices from xiaofei where DATE_FORMAT(time,'%H') = '11' GROUP BY uid

SUM 是MySQL返回指定字段的总和；

更多函数请看这里：

MySQL 函数 | 菜鸟教程