本帖主要内容记录一下我工作内容中的一些mysql查询记录,不是很深奥的内容,有不对的地方欢迎大家斧正。
查询同比增长率
同比增长率=((本期数-同期数)÷同期数)×100%
化简一下= ((本期数÷同期数)- 1 )×100%
表结构:
CREATE TABLE `cashier_order` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`order_amount` decimal(10,2) unsigned NOT NULL DEFAULT '0.00' COMMENT '订单金额',
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=63 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='订单';
mysql查询思路:查询出当天以及前一天支付的订单数据计算出支付金额同比增长率
SELECT
IFNULL((( MAX(sumNow)/MAX(sumOld))-1),0) grossRevenueRatio
FROM (
(
(
SELECT
IFNULL(sum(order_amount),0) AS sumNow,
0 sumOld
FROM cashier_order WHERE to_days(pay_time) = to_days(now())
)
UNION ALL
(
SELECT
0 sumNow,
IFNULL(sum(order_amount),0) AS sumOld
FROM cashier_order WHERE TO_DAYS( NOW( ) ) - TO_DAYS( pay_time) = 1
)
) o
UNION ALL:合并结果集,不会忽略相同值
查询当天统计数据
这里查询的数据的时间粒度为天
pay_time:支付时间 id:订单表索引
SELECT
date_format('时间字段', '%Y-%m-%d') payTime,
COUNT(cashier_order.id) '当天订单数统计'
FROM
cashier_order
group by date_format('时间字段', '%Y-%m-%d')
ORDER BY '时间字段' DESC
实现时间周期为当天思路为,格式化实现格式显示到为年月日,根据年月日分组查询,既:
group by date_format(‘时间字段’, ‘%Y-%m-%d’)
下面是各种时间查询给各位看官老爷:
今天
select * from 表名 where to_days(时间字段名) = to_days(now());
昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) = 1
近7天
SE LECT * 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 * from 表 where week(字段) = week(now());
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();
根据坐标查询行政区名称
数据库行政区表:
CREATE TABLE `districts` (
`district_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '行政区ID',
`district_pid` int unsigned NOT NULL DEFAULT '0' COMMENT '行政区父ID',
`district_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '行政区名称',
`district_shortname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '行政区简称',
`lng_x` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '行政区经度(x轴)',
`lat_y` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '行政区纬度(y轴)',
`level` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '行政区等级',
`district_sort` mediumint DEFAULT NULL COMMENT '行政区排序',
PRIMARY KEY (`district_id`) USING BTREE,
KEY `district_index` (`district_pid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=65901003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='行政区';
行政区等级:level:province(省)city(市)district(区)
SELECT
j.district_name AS NAME,
(
6371 * acos(
cos(
radians( lat )) * cos(
radians( j.lat_y )) * cos(
radians( j.lng_x ) - radians( lng )) + sin(
radians( lat )) * sin(
radians( j.lat_y ))
)) AS distance
FROM
districts j
WHERE
j.`level` = 'district'
ORDER BY
distance ASC
LIMIT 1
查询思路,计算出两个经纬度之间的距离排序,取最近的一点,即可得到数据库中是那个区的坐标点(因为这个数据库只倒区,所以只查出来了区的名称)
好的,差不多就是这个样子了。
可以的话,关注公众号:码了一个bug。