SQL常用语句，按月统计，查询最后一条数据

一、sql常用语句
1.同时插入多条语句
INSERT INTO admin_role_resources (uuid, roleid, resourcesid)
VALUES
(32, 1, 17),
(33, 2, 18);

2.统计数量
SELECT
COUNT(ev_attention.uuid)
FROM
ev_attention
WHERE
ev_attention.wxOpenId = ‘onQv7wjX8X9SvDJKAHGvlKRi9-S0’

3.查询重复数据
查询同一ope4nId存在多条数据的数据
SELECT
*
FROM
wx_user
WHERE
openid IN (
SELECT
openid
FROM
wx_user
GROUP BY
openid
HAVING
COUNT(openid) > 1
)

4.子查询
用子查询出的结果填充，定义列名
SELECT ‘活跃电梯总量’ AS title, SUM(A.total) AS elevatorTotal FROM (
SELECT COUNT(*) AS total
FROM ev_operation_client
WHERE user_type=1
AND evId IS NOT NULL
GROUP BY evId
) A

5.创建表
CREATE TABLE timer_count (
uuid bigint(20) NOT NULL AUTO_INCREMENT,
resType int(11) DEFAULT NULL COMMENT ‘1.电梯基础数据 2.电梯品牌统计 3.电梯用途统计 4.电梯健康指数 5.电梯速度数据 6.电梯类
型实际占比 7.电梯年龄结构 8.自动扶梯区段长度统计 9.曳引与强制驱动电梯楼层统计 10.区域电梯数据 11.电梯类型月度需求’,
resTypeStr varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
totalCount bigint(20) DEFAULT NULL,
content json DEFAULT NULL,
createAt datetime DEFAULT NULL,
PRIMARY KEY (uuid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

6.INNER JOIN的使用
根据维特号查询工单
SELECT
ei.uuid,
ei.wtCode,
wi.CODE,
wi.date
FROM
c_elevator ce
INNER JOIN ev_info ei ON ce.evId = ei.uuid
INNER JOIN wo_info wi ON ce.woId = wi.uuid
WHERE
ei.wtCode = ‘00293712’;

7.更新数据UPDATE
UPDATE admin_resources
SET sortId = 6
WHERE
uuid = 11

8.删除DELETE
删除待处理报警列表
DELETE c_elevator
FROM
c_elevator
INNER JOIN rescue_note ON rescue_note.c_ev_id = c_elevator.uuid
WHERE
rescue_note.r_status = 1;

9.添加列ALTER
ALTER TABLE user_evalaute ADD name VARCHAR(255)
ALTER TABLE user_evalaute ADD photoPath VARCHAR(255)

10.NOT IN查询在一张表中存在，在另一张表中不存在
SELECT
uuid,
retrospectCode,
CODE,
deviceType,
sn,
phone,
createAt,
STATUS,
buyCompanyName
FROM
device
WHERE
device.uuid NOT IN (
SELECT
ev_device.deviceId
FROM
ev_device
)

11.MAX,MIN使用时，【除了MAX,MIN字段，其余的返回字段需要group by,否则报错】
查询某些电梯最后一个工单对应的userId（这里用了两层max,子查询层虽然返回了唯一数据，但是到主查询中又可能产生多条不用的数据，so,还需要max一次，我这里子查询出数据紧有1000-2000条数据，基本不影响效率，如是子查询出的数据太多会很影响效率）
SELECT
a.userId,cev.evId,MAX(cev.last)
FROM
wo_info a,
c_elevator b
INNER JOIN (
SELECT
evId,
MAX(lastopstime) last
FROM
c_elevator
WHERE
evId IN (
102554480551642920,
110326792150685429,
124127621907485380,
116095462856984099
)
GROUP BY
1
) cev ON cev.last = b.lastopstime
WHERE
a.uuid = b.woId
GROUP BY 1,2

12.按单表中年-月统计数据
点击下方链接到之前的文章查看
https://blog.csdn.net/yingtian648/article/details/88787349