本文共 3628 字，大约阅读时间需要 12 分钟。

1.统计批量查询每人每天最后一条数据

-- 自己join自己的最大时间 就是最后一条数据 select a.device_order_id, a.age, a.value, a.create_time from tb_heart_data a      JOIN (          -- 先查询出最后一条数据的时间        select device_order_id, MAX(create_time) create_time from tb_heart_data        GROUP BY device_order_id      ) b on a.device_order_id = b.device_order_id and a.create_time = b.create_time

今天select * from 表名 where to_days(时间字段名) = to_days(now());昨天SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 17天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)近30天SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)本月SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )上一月SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1#查询本季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());#查询上季度数据select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));#查询本年数据select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());#查询上年数据select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));查询当前这周的数据 SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());查询上周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;查询当前月份的数据select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')查询距离当前现在6个月的数据select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();查询上个月的数据select name,submittime from enterprise   where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')select * from ` user ` where DATE_FORMAT(pudate, ' %Y%m ' ) = DATE_FORMAT(CURDATE(), ' %Y%m ' ) ;select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())select * from user where MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())select * from [ user ] where YEAR (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = YEAR (now())and MONTH (FROM_UNIXTIME(pudate, ' %y-%m-%d ' )) = MONTH (now())select * from [ user ] where pudate between 上月最后一天and 下月第一天where   date(regdate)   =   curdate();select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())SELECT date( c_instime ) ,curdate( )FROM `t_score`WHERE 1LIMIT 0 , 30SQL语句例一：求某一字段在某一时间段内数量的总和　　表名 销售 字段有：编号 金额 统计这个表在某一时间段内金额的总和　　select sum(金额) from 表 where 条件　　SQL语句例二：SQL读出记录并统计出某一字段的总和　　select * ,SUM(字段) from 表名 where 条件 group by 字段　　SQL语句例三：SQL语句按每天、每月、每年统计出销售总额　　表名： 订单 字段有Ordertime(订单时间),money(金额)　　根据订单时间统计每天、每月、每年的销售总额　　1、每年　　select year(ordertime) 年，　　sum(money) 销售合计　　from 订单　　group by year(ordertime)　　2、每月　　select year(ordertime) 年，　　month(ordertime) 月,　　sum(money) 销售合计　　from 订单　　group by year(ordertime)，　　month(ordertime)　　3、每日　　select year(ordertime) 年，　　month(ordertime) 月,　　day(ordertime) 日，　　sum(Total) 销售合计　　from 订单　　group by year(ordertime)，　　month(ordertime),　　day(ordertime)　　SQL语句例四：统计某月销售量前10　　表名: 销售　　字段: 编号, 商品名称 价格 日期　　统计某月商品销量最大的商品前10个商品及销量。　　SELECT DISTINCT TOP 10 商品名称, SUM(价格) AS 产品总价, COUNT(*) AS 销量 FROM 销售 GROUP BY title　　加上月份：　　SELECT DISTINCT TOP 10 商品名称, SUM(价格) AS 产品总价, COUNT(*) AS 销量 FROM 销售 WHERE (MONTH(sdate) = '你要查的月份数字') GROUP BY 商品名称　　统计某月商品销量最大的商品前10个商品及销量并按销量排序　　SELECT DISTINCT TOP 10 商品名称, SUM(价格) AS 产品总价, COUNT(*) AS 销量 FROM 销售 WHERE (MONTH(日期) = '你要查的月份数字') GROUP BY 商品名称 ORDER BY 销量 DESC

转载地址：http://yyllf.baihongyu.com/