随笔分类 - MySQL / 常用SQL笔记
摘要：例如 祖级节点为：企业/TI技术部/后端 假设后端节点的id 为1416957672636129281 需求：需要查询出后端所有的父级级节点，即企业/TI技术部 SELECT t2.* FROM ( SELECT @r AS _id, ( SELECT @r := parent_id FROM sy
阅读全文
摘要：- 查询30天 select DATE_SUB(CURDATE(), INTERVAL 30 DAY) frm table; 今天 select * from 表名 where to_days(时间字段名) = to_days(now()); 昨天 SELECT * FROM 表名 WHERE DA
阅读全文
摘要：SELECT * FROM your_table WHERE date_column >= CURDATE() - INTERVAL 30 DAY
阅读全文
摘要：SELECT object_id, SUM(CASE WHEN date_column >= CURDATE() - INTERVAL 30 DAY AND date_column < CURDATE() THEN amount ELSE 0 END) AS amount_30_days, SUM(
阅读全文
摘要：/*获取指定id的上一条记录，我这里是以id=5为例的*/ select cid,cname from company order by cid>=5,cid desc limit 1 /*获取指定id的下一条记录，我这里是以id=5为例的*/ SELECT cid,cname from compa
阅读全文
摘要：SELECT * FROM tb_product WHERE create_date BETWEEN STR_TO_DATE( date_format( CURRENT_DATE (), '%Y-%m-01 00:00:00' ), '%Y-%m-%d %H:%i:%s' ) AND STR_TO_
阅读全文
摘要：创建表 Create table If Not Exists Scores (Id int, Score DECIMAL(3,2)); insert into Scores (Id, Score) values ('1', '3.5'); insert into Scores (Id, Score)
阅读全文
摘要：SELECT * FROM sys_menu WHERE id in ( select id from ( select t1.id, if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischi
阅读全文
摘要：分类递归查询子节点 delimiter $$ drop function if exists get_child_categoryt$$ create function get_child_categoryt(in_id varchar(10)) returns varchar(1000) begi
阅读全文
摘要：//status = 0 为未支付状态 select * FROM order WHERE TIMESTAMPDIFF(MINUTE,create_date,now()) > 20 AND status = 0;
阅读全文
摘要：-- 返回的是某月某日的数量 SELECT monthDay, count( 0 ) AS numb FROM ( SELECT -- 返回的是月日 date_format( create_date, '%m-%d' ) monthDay FROM tb_product WHERE create_d
阅读全文
摘要：Logins table: + + + | user_id | login_date | + + + | 1 | 2019-07-20 | | 1 | 2019-07-20 | | 1 | 2019-07-20 | | 2 | 2019-07-20 | | 2 | 2019-07-21 | | 2
阅读全文
摘要：近 30天的每日活跃用户数（当天只要有一条活动记录，即为活跃用户）。 Activity table: + + + + + | user_id | session_id | activity_date | activity_type | + + + + + | 1 | 1 | 2019-07-20 |
阅读全文
摘要：这种查询会导致全表扫描 select id,name,create_date from sys_user a where not exists (select * from sys_user b where a.name = b.name and a.create_date < create_dat
阅读全文
摘要：drop procedure if exists init_group_val; delimiter $ create PROCEDURE init_group_val() BEGIN DECLARE group_id VARCHAR(64); -- 遍历数据结束标志 DECLARE done IN
阅读全文
摘要：select a.id,substring_index(substring_index(a.val,',',b.help_topic_id+1),',',-1) from tbl_brand_attr_group a join mysql.help_topic b on b.help_topic_i
阅读全文
摘要：delimiter $ create PROCEDURE phoneDeal() BEGIN DECLARE ids varchar(64); -- 遍历数据结束标志 DECLARE done INT DEFAULT FALSE; -- 游标 DECLARE cur_account CURSOR F
阅读全文
摘要：DELIMITER $$ drop procedure if exists getCategoryAndBrandRankByOrderId $$ CREATE PROCEDURE getCategoryAndBrandRankByOrderId ( _index int, size int ) B
阅读全文
摘要：解决MySQL sql_mode=only_full_group_by问题，this is incompatible with sql_mode=only_full_group_by错误 原因： 看一下group by的语法： select 选取分组中的列 + 聚合函数 from 表名称 group
阅读全文
摘要：create_time 时间不超过当前系统时间 7 天的数据 select create_time from incentive_activity_pub where DATEDIFF(CURDATE(), create_time)<=7
阅读全文
浙公网安备 33010602011771号