1. count中的属性过滤 xxx or null
SELECT
DATE_FORMAT(create_time,'%Y-%m-%d') as r_create_time,
COUNT((process_state=1 and unknow=1) or null) unknown,
COUNT((process_state=1 and unknow=0 and ignore_state=0) or null) process_state,
COUNT((process_state=1 and ignore_state=1) or null) ignore_state
FROM
rs_plate_sure
GROUP BY r_create_time
2. GROUP_CONCAT组合字符串(遇到有分页的时候有用,比如查询某人所有车的车牌,但是作为一个属性显示在一行)
t_score
t_r
select GROUP_CONCAT(distinct(t_s.score)), t_s.no from t_score t_s left join t_r on t_s.id=t_r.id group by t_s.no
SELECT
GROUP_CONCAT( DISTINCT (if(t_r.id='2','特殊处理',t_r.data) )), t_s.id
FROM
t_score t_s
LEFT JOIN
t_r ON t_s.id = t_r.id
GROUP BY t_s.id
3. 找到最近流水的那一行
select b.* from (SELECT id,max(score) score,no FROM t_score group by no) a
left join t_score b
on a.no = b.no and a.score= b.score;
4. 有left join order by 遇到分页的时候混乱
解决办法 order by 一个id 保证left join有序 如果oder by后的顺序依然有等值的就再加个属性来排序
5. update select 将其他表的内容 更新到目标表
UPDATE t_target t_tar
LEFT JOIN
(SELECT
ifnull(sum(ifnull(park_state,0)),0)sm, park_id
FROM
t_park_state
GROUP BY park_id) r_st ON r_st.park_id = e_s.park_id
LEFT JOIN
t_park t_p ON t_p.park_id = e_s.park_id
SET
t_tar.empty_num = (ifnull(t_p.total_space,0) - ifnull(r_st.sm,0))
存储过程添加字段
<update id="initBerthCordinatesTable">
DELIMITER $$
drop procedure if exists add_col_homework;
create procedure add_col_homework()
BEGIN
IF NOT EXISTS ( SELECT
COLUMN_NAME
FROM
information_schema.COLUMNS
WHERE
1 = 1
AND TABLE_NAME ='t_detail_berth_ordinates'
AND COLUMN_NAME ='min_berth_number')
THEN
ALTER TABLE `t_detail_berth_ordinates` ADD COLUMN `min_berth_number`
varchar(36);
end if;
end;
CALL add_col_homework;
$$
</update>
存储IP4 地址
select INET_ATON('192.168.0.0')
自动更新的时间戳
CREATE TABLE `t_user5` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
`updatetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间随时更新',
`createtime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间是创建的时间,不随时更新',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
insert into test.t_user (id, name) values("1","Tom");
insert into test.t_user (id, name) values("2","Jack");
update test.t_user set name ='June' where id =2;
SELECT * FROM test.t_user;
时间处理
对于long
select FROM_UNIXTIME(ctime/1000,'%Y-%m-%d') dt
对于datetime
date_format(create_at,'%Y%-%c%-%e')dt