今天

select * from 表名 where to_days(時間字段名) = to_days(now());

昨天 SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 時間字段名) <= 1

近7天 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,時間字段名FROM enterprise WHERE YEARWEEK(date_format(時間字段名,'%Y-%m-%d')) = YEARWEEK(now());

查詢上周的數據 SELECT name,時間字段名FROM enterprise WHERE YEARWEEK(date_format(時間字段名,'%Y-%m-%d')) = YEARWEEK(now())-1;

查詢上個月的數據 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 下月第一天

查詢當前月份的數據 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

COUNT(1) AS countNumber,

DATE_FORMAT(createTime,'%Y-%m-%d') AS dateTime

FROM

testTable

GROUP BY DATE_FORMAT(createTime,'%Y-%m-%d')

查詢每周的數據： SELECT

COUNT(1) AS countNumber,

WEEK(createTime) as dateTime

FROM

testTable

GROUP BY WEEK(createTime)

查詢每月的數據： SELECT

COUNT(1) AS countNumber,

MONTH(createTime) as dateTime

FROM

testTable

GROUP BY MONTH(createTime)

查詢每年的數據： SELECT

COUNT(1) AS countNumber,

YEAR(createTime) as dateTime

FROM

testTable

GROUP BY YEAR(createTime)

其它：

DATE_ADD(createTime,INTERVAL 1 DAY) 得到指定日期后一天的日期/把1改為任意數字就可以得到后N天的日期

DATE_SUB(createTime,INTERVAL 1 DAY) 得到指定日期前一天的日期/把1改為任意數字就可以得到前N天的日期

聲明變量查詢：

SET @counter=0;

select @counter:=@counter+1 as id,count(*) as num FROM testTable ORDER BY num desc

去重選擇后返回條數 SELECT COUNT(DISTINCT(id)) from 數據表 WHERE createdDate BETWEEN '2009-08-06 15:53:14' AND '2009-09-01 15:53:14'

查出第幾周到第幾周的區間數據: SELECT

COUNT(DISTINCT(id)) ,

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1) AS startweek,

WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1) AS endweek

from 數據表

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1);

多表關聯查詢: SELECT

COUNT(DISTINCT(account)) as fuNum,

op.*,

p.*

from

A o

LEFT JOIN B op

ON o.id = op.id

LEFT JOIN C p

ON op.id = p.id

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)

AND id = 3

函數查詢求和: SELECT

COUNT(DISTINCT(op.a)) as pa,

COUNT(p.b) AS sa,

SUM(o.c) AS ta

from

A o

LEFT JOIN B op

ON o.id = op.id

LEFT JOIN C p

ON op.id = p.id

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)

AND id = 3

嵌套查詢: SELECT

DISTINCT

(CASE account

WHEN '0' THEN 'null'

ELSE (SELECT COUNT( DISTINCT(account) ) FROM eps_order WHERE payStatus = 'paid') END) AS payNum,

COUNT(p.`order`) AS saleOrders,

SUM(o.amount) AS totalAmount

from

A o

LEFT JOIN B op

ON o.id = op.orderID

LEFT JOIN C p

ON op.productID = p.id

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)

AND o.`status` != 'canceled'

AND productID = 3

多重嵌套查詢: SELECT

DISTINCT

(CASE o.account

WHEN NULL THEN 'null'

ELSE

(

SELECT

COUNT( DISTINCT(o.account) )

from

A o

LEFT JOIN B op

ON o.id = op.orderID

LEFT JOIN C p

ON op.productID = p.id

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2009-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2009-08-06 15:53:14','%Y-%m-%d'),1)

AND o.`status` != 'canceled'

AND productID = 3

)

END) AS payNum,

COUNT(p.`order`) AS saleOrders,

SUM(o.amount) AS totalAmount

from

A o

LEFT JOIN B op

ON o.id = op.orderID

LEFT JOIN C p

ON op.productID = p.id

WHERE

WEEK(date_format(createdDate,'%Y-%m-%d'),1)

BETWEEN

WEEK(date_format('2018-08-05 15:53:14','%Y-%m-%d'),1)

AND WEEK(date_format('2018-08-06 15:53:14','%Y-%m-%d'),1)

AND o.`status` != 'canceled'

AND productID = 3

總結

以上是生活随笔為你收集整理的mysql事务 brave,mysql 查询当天、本周，本月，上一个月的数据的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。