文章目录
mysql中的join
可查出左表所有数据
select * from tb_zdxm p left join tb_zdxm_plan pl on p.id=pl.pro_id and pl.plan_year='2017' ;
只能查出部分数据
select * from tb_zdxm p left join tb_zdxm_plan pl on p.id=pl.pro_id where pl.plan_year='2017' ;
重复数据取最新数据的问题
错误做法
重复数据单纯排序后分组取出的数据仍然不是最新数据,order by失效
select *
FROM (select me2.matter_id m_id, me2.satisfaction_degree
FROM matter_evaluate me2
LEFT JOIN matter_order mo3
ON me2.matter_id = mo3.id
order by me2.found_time desc) me3
group by me3.m_id
正确做法
排序后增加一个“Limit${填写一个很大的值}”,此时order by生效
select *
FROM (select *
FROM (select me2.matter_id m_id, me2.satisfaction_degree
FROM matter_evaluate me2
LEFT JOIN matter_order mo3
ON me2.matter_id = mo3.id
order by me2.found_time desc
LIMIT 1000000000) me3
group by me3.m_id) me4
效率更改的写法
select matter_id, max(found_time) found_time
from matter_evaluate
group by matter_id;
mysql中的find_in_set函数
FIND_IN_SET(str,strList)
- str 要查询的字符串
- strList 字段名,参数以“,”分隔,如(1,2,6,8)
- 查询字段(strList)中包含的结果,返回结果null或记录。
假如字符串str在由N个子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则FIND_IN_SET() 函数被优化,使用比特计算。 如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。
select find_in_set('1','1,2,3,4,5,6');
mysql locate函数
判断字符串(string)中是否包含另一个字符串(subStr)
locate(subStr,string) :函数返回subStr在string中出现的位置
// 如果字符串 string 包含 subStr
locate(subStr,string) > 0
// 如果字符串 string 不包含 subStr
locate(subStr,string) = 0
SELECT *
FROM party_course_study
WHERE LOCATE(findCode, '00001') >0
// 注:Mybatis使用场景,需要加 <![CDATA[ ]]>
SELECT *
FROM party_course_study
WHERE <![CDATA[ LOCATE(findCode, '00001') > 0 ]]>
mysql datetime类型求差
mysql中两个datetime类型的数据相减得到两个日期的时间差
start='2018-12-06 05:00:00'
end='2018-12-06 10:00:00'
求差
TIMESTAMPDIFF(unit,start,end)
- unit分类
- MINUTE:以分钟为单位
- HOUR:小时为单位
- DAY:天为单位
- MONTH:月为单位
mysql datediff(获取两个日期的时间间隔)
DATEDIFF(date1,date2) 返回起始时间 date1 和结束时间 date2 之间的天数。date1 和 date2 为日期或 date-and-time 表达式。
计算时只用到这些值的日期部分。
mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,
-> DATEDIFF('2017-11-30','2017-12-15') AS col2;
+------+------+
| COL1 | col2 |
+------+------+
| 1 | -15 |
+------+------+
1 row in set (0.00 sec)
mysql统计一年12月份数据
效果
select
sum(case month(createTime) when '1' then 1 else 0 end) as 一月份,
sum(case month(createTime) when '2' then 1 else 0 end) as 二月份,
sum(case month(createTime) when '3' then 1 else 0 end) as 三月份,
sum(case month(createTime) when '4' then 1 else 0 end) as 四月份,
sum(case month(createTime) when '5' then 1 else 0 end) as 五月份,
sum(case month(createTime) when '6' then 1 else 0 end) as 六月份,
sum(case month(createTime) when '7' then 1 else 0 end) as 七月份,
sum(case month(createTime) when '8' then 1 else 0 end) as 八月份,
sum(case month(createTime) when '9' then 1 else 0 end) as 九月份,
sum(case month(createTime) when '10' then 1 else 0 end) as 十月份,
sum(case month(createTime) when '11' then 1 else 0 end) as 十一月份,
sum(case month(createTime) when '12' then 1 else 0 end) as 十二月份,
year(createTime) as year
from fire_disaster
where year(createTime) in('2015','2016','2017','2018') group by year;
逗号分隔字符求交集函数
CREATE FUNCTION str_intersection(str1 TEXT, str2 TEXT) RETURNS TINYINT
BEGIN
DECLARE idx INT DEFAULT 0; -- B 集合单元索引
DECLARE len INT DEFAULT 0;-- B 集合表达式长度
DECLARE llen INT DEFAULT 0;-- 最后检查位置
DECLARE clen INT DEFAULT 0;-- 当前检查位置
DECLARE tmpStr varchar(255);-- 临时检查数据集
DECLARE curt varchar(255);-- B 当前检查的单元
SET len = LENGTH(str1);
WHILE idx < len
DO
SET idx = idx + 1;
SET tmpStr = SUBSTRING_INDEX(str2, ',', idx);
SET clen = LENGTH(tmpStr);
-- 获取当前 setB 中的单元
IF idx = 1 THEN
SET curt = tmpStr;
ELSE
SET curt = SUBSTRING(str2, llen + 2, clen - llen - 1);
END IF;
-- 检查是否存在于 setA 中
IF curt != '' and FIND_IN_SET(curt, str1) > 0 THEN
RETURN 1;
END IF;
-- 当前检查终点与上次检查终点相同则跳出
IF clen <= llen THEN
RETURN 0;
END IF;
SET llen = clen;
END WHILE;
RETURN 0;
END;
正则表达式匹配字符串交集
create function is_mixed(str1 text, str2 text) returns tinyint
BEGIN
DECLARE ismixed TINYINT DEFAULT 0;
set ismixed:=(select concat(str1, ',') regexp concat(replace(str2,',',',|'), ','));
RETURN ismixed;
END;