mysql实用函数

统计函数

一对多，多的一端只查询最新数据

ROW_NUMBER() over(PARTITION BY evt_id ORDER BY evt_node_receipt_time DESC）node from evt_info

适用场景：

事件——我要查出当前事件的最新节点。
一个事件发起下面很多个节点处置。一对多映射只要拿到最新的节点处置。
先来查一个事件id下面的所有节点，可以看到节点太多，如果按照往常的连表查询之后数据会有超级多。

现在按正常的连表查询，结果144条，不是我想要的结果。应该只要事件主表的所有数据

事件主表的所有数据

查询正在进行中的事件最新处置节点下的数据

 select af.evt_id,af.evt_title,af.evt_type_name,af.nick_name as evtInitiatorName,af.evt_start_time,
        af.evt_node_name,af.evt_longitude,af.evt_latitude
        from (select ei.evt_id,ei.del_flag,ei.evt_status,ei.evt_start_time,ei.evt_title,ei.evt_longitude,ei.evt_latitude,
        et.evt_type_name,su.nick_name,en.evt_node_name,
        ROW_NUMBER() over(PARTITION BY en.evt_id ORDER BY en.evt_node_receipt_time DESC) node_rank
        from evt_info ei
        left join evt_node en
        on en.evt_id=ei.evt_id
        left join sys_user su
        on ei.evt_initiator=su.user_id
        left join evt_type et
        on ei.evt_type_id = et.evt_type_id) af
        WHERE af.node_rank = 1 and af.evt_status='processing' and af.del_flag=0

YEAR 年份函数

#查询注册时间是当年的用户
SELECT *
from sys_user
WHERE YEAR(create_time) = YEAR(NOW())

MONTH 月份函数

#统计每个月的注册人数
SELECT count(1)
from sys_user
GROUP BY MONTH(create_time)

QUARTER 季度函数

#统计每个季度的注册人数
SELECT QUARTER(create_time),count(1)
from sys_user
GROUP BY QUARTER(create_time)

往前递推十年

SELECT YEAR(DATE_SUB(NOW(), INTERVAL 10 YEAR))

往后递推十年

SELECT YEAR(DATE_ADD(NOW(), INTERVAL 10 YEAR))

查询去年12月份

#查询去年12月份
SELECT *
FROM sys_user 
WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))
AND MONTH(create_time)=12

统计身份证户籍所在地人数

SELECT `name`,count(a.id_card) as `value`
        FROM
        (
            select t.id_card,
            CASE LEFT(t.id_card,2)
            WHEN 11 THEN '北京' WHEN 12 THEN '天津'
            WHEN 13 THEN '河北省' WHEN 14 THEN '山西省'
            WHEN 15 THEN '内蒙古自治区' WHEN 21 THEN '辽宁省'
            WHEN 22 THEN '吉林省' WHEN 23 THEN '黑龙江省'
            WHEN 31 THEN '上海' WHEN 32 THEN '江苏省'
            WHEN 33 THEN '浙江省' WHEN 34 THEN '安徽省'
            WHEN 35 THEN '福建省' WHEN 36 THEN '江西省'
            WHEN 37 THEN '山东省' WHEN 41 THEN '河南省'
            WHEN 42 THEN '湖北省' WHEN 43 THEN '湖南省'
            WHEN 44 THEN '广东省' WHEN 45 THEN '广西壮族自治区'
            WHEN 46 THEN '海南省' WHEN 50 THEN '重庆'
            WHEN 51 THEN '四川省'WHEN 52 THEN '贵州省'
            WHEN 53 THEN '云南省' WHEN 54 THEN '西藏'
            WHEN 61 THEN '陕西' WHEN 62 THEN '甘肃省'
            WHEN 63 THEN '青海' WHEN 64 THEN '宁夏回族自治区'
            WHEN 65 THEN '新疆维吾尔自治区' end `name`
            from basic_resident t WHERE t.outsider=0
        ) as a
        group by `name`

截取函数

截取指定字符

存储：1层
前端展示 1
SUBSTRING_INDEX(str,delim,count) str是字段名，delim是特定字符串，count是从第几个开始截取。
例如：
SELECT SUBSTRING_INDEX(br.floors,'层',1)
FROM basic_room br

ROUND计算比例-去除多余的0

计算比例：
ROUND(1/3,2)*100
去除多余0
SELECT 0+CAST(ROUND(1/3,2)*100 AS CHAR)
拼接%比
SELECT CONCAT(0+CAST(ROUND(1/3,2)*100 AS CHAR),'%')

TIMESTAMPDIFF-计算两个日期之间的差值

截取计算身份证的年龄
SUBSTR 查的字段，开始截取位置，截取长度
TIMESTAMPDIFF 单位，开始时间戳，当前时间戳
TIMESTAMPDIFF(YEAR, SUBSTR(id_card, 7, 8), NOW()) 

查询本周

查询的时间是周一到周天
YEARWEEK(date_format(create_time,'%Y-%m-%d'),1) = YEARWEEK(now(),1)

查询A表字段更到A表另一字段。

查询id更新到user_id。

UPDATE t_order_info
INNER JOIN ( SELECT id,user_id FROM t_order_info ) temp 
ON t_order_info.id = temp.id 
SET t_order_info.user_id = temp.id

查询A表字段更到B表字段。

查询basic_house_test_demo的 longitude_and_latitude 更新到basic_house的longitude_and_latitude

UPDATE basic_house
INNER JOIN (
SELECT bht.longitude_and_latitude,bht.house_id
from basic_house_testdemo bh
INNER join basic_house_testdemo bht
on bh.house_id = bht.house_id) temp 
ON basic_house.house_id = temp.house_id 
SET basic_house.longitude_and_latitude = temp.longitude_and_latitude

int时间戳转时间

实体层
	@NotBlank(message = "使用时间不能为空")
    @ApiModelProperty("使用时间")
    private String useTime;

数据库int存的时间戳，1651804908
sql查询语句
SELECT FROM_UNIXTIME(1651804908)

SELECT FROM_UNIXTIME(字段名)
FROM 表名