zhongyuan_p的博客

概念下面示例

SELECTID.LEVEL,DATA.*FROM(SELECT@ids AS _ids,( SELECT @ids := GROUP_CONCAT( defect_id ) FROM t_defect_classification WHERE FIND_IN_SET( p_defect_id, @ids ) ) AS cids,@l := @l + 1 AS LEVELFROMt_defect_classification,( SELECT @ids := ‘493416568936

直接运行sql 看结果-- mysql 时间计算函数-- 函数 TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)select TIMESTAMPDIFF(DAY, '2022-02-20 23:59:00', '2022-02-22 00:00:00');　select TIMESTAMPDIFF(HOUR, '2022-02-20 23:59:00', '2022-02-22 00:00:00');　select TIMESTAMPDIFF(

出处： https://www.cnblogs.com/hider/p/9067010.html今天遇到一个需要以时间差为条件的查询场景，需要查询出用户在某天通话时间大于15秒的记录，由于对sql了解的并不多，一开始尝试直接用时间计算，发现果然行不通，然后Google了一些资料，发现各个数据库都有针对时间计算的函数，在这里总结一下MySQL的，其他数据库的有轻微的差异，不过也都是大同小异,也可以借鉴TIMESTAMPDIFF（时间单位，参数1，参数2）可以设置参数，可以精确到天（DAY），小时（HOU

准备一张表CREATE TABLE `sc` ( `SId` varchar(10) DEFAULT NULL, `CId` varchar(10) DEFAULT NULL, `score` decimal(18,1) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `sc` VALUES ('01', '01', 80.0);INSERT INTO `sc` VALUES ('01', '02', 90.0)

准备2 张表CREATE TABLE `sc` ( `SId` varchar(10) DEFAULT NULL, `CId` varchar(10) DEFAULT NULL, `score` decimal(18,1) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `student` ( `SId` varchar(10) DEFAULT NULL, `Sname` varchar(10) DEF

刚开始 以为mysql 有时间转换的函数，一直在找这样的函数！！！！ 0.0后来发现用数学中的思维就可以解决秒转分 ，/60秒转时，/3600秒转天，/86400下面实例SELECT (SUM(time)/60/60) as xx from(select SECOND (CAST( timediff('2008-08-08 23:08:08', '2008-08-08 23:09:00') AS TIME )) as time UNION all select SECOND (CA

select * from (SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.`timeIndex`, ',', b.help_topic_id + 1 ), ',', - 1 ) AS XTime, 0 ratioFROM ( SELECT '0,1,2,3,4,5,6,7,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23' AS timeIndex ) AS a JOIN mysql.help_topic AS b O

#convert() 函数可用来获取一个类型的值#返回数字表达式并四舍五入为指定的长度或精度。字符型: CHAR 日期 : DATE 时间: TIME 日期时间型 : DATETIME 浮点数 : DECIMAL 整数 : SIGNED 无符号整数 : UNSIGNED SELECT ROUND(10/3,3),Convert(10/3,decimal(18,2))FROM t_defect_rectification_order

用法 case xxx when xx then xx else xx end注意sql 中 as 取别名不能为数字SELECT count(1) as totalnum ,sum(CASE order_state WHEN 1 THEN 1 ELSE 0 END ) AS newSend,sum(CASE order_state WHEN 2 THEN 1 ELSE 0 END) as ss,sum(CASE order_state WHEN 3 THEN 1 ELSE 0 END)