MySQL

#先按置顶时间排序，置顶时间为NULL时排在最后，然后按创建时间排序
SELECT * FROM p_org_share_info
ORDER BY
    top_time IS NULL ASC, top_time DESC ,create_time DESC



INSERT INTO c_credit_record
(
    `id`,`state`,`mobile`,`real_name`,`id_no`,`create_time`,`deadline`,`loan_amount`,`amount`,`bstate` ,`end_date`,`repay_date`
) SELECT (SELECT CONCAT((SELECT DATE_FORMAT(NOW(),'%Y%m%d%H%m%s')),(SELECT ROUND(ROUND(RAND(),4)*100000)))) AS id, 
         '24' AS state, 
         mi.`mobile` ,md.`real_name` ,md.`id_no` ,bo.`create_time` ,bo.`deadline` ,bo.`amount` AS loan_amount,bo.`amount`  ,bo.`state` AS bstate,
        (SELECT MAX(end_date) FROM t_repay AS trp WHERE trp.borrow_order_no = bo.borrow_order_no) AS end_date ,
        (SELECT repay_date FROM t_repay WHERE repay_plan_no = (SELECT MAX(repay_plan_no) FROM t_repay_plan WHERE borrow_order_no = bo.borrow_order_no)) AS repay_date 
    FROM 
        `t_memb_i` mi,
        `t_memb_d` md,
        `t_order` bo,
        `t_memb_org_r` mor
    WHERE 
        mi.`member_id` = '1111111111111'
        AND mi.`member_id` = md.`member_id` 
        AND mi.`member_id` = mor.`member_id` 
        AND mor.`member_org_id` = bo.`borrow_member_id` 


--比如说，下面这段SQL，你永远无法得到“第二类”这个结果
CASE WHEN col_1 IN ( 'a', 'b') THEN '第一类'
         WHEN col_1 IN ('a')       THEN '第二类'
ELSE'其他' END



--简单Case函数
CASE sex
         WHEN '1' THEN '男'
         WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
         WHEN sex = '2' THEN '女'
ELSE '其他' END

 

mysql workbench中PK,NN,UQ,BIN,UN,ZF,AI字段类型标识说明

PK：primary key 主键

NN：not null 非空

UQ：unique 唯一索引

BIN：binary 二进制数据(比text更大)

UN：unsigned 无符号（非负数）

ZF：zero fill 填充0 例如字段内容是1 int(4), 则内容显示为0001 

AI：auto increment 自增

 

 

#从登陆日志表中查询每个会员最后一次登录的记录
#https://blog.csdn.net/HXNLYW/article/details/102681680
SELECT 
　　mi.*,
　　t1.*
FROM 
    t_member_info mi,

( 
SELECT 
    el.*,
    FROM t_ent_login_log el, (SELECT max(id) id FROM t_ent_login_log group by member_id) md 
    WHERE el.id = md.id
) AS t1

WHERE t1.member_id = mi.member_id
ORDER BY t1.login_time desc ,t1.id

 

# 统计每日注册人数和累计注册人数
SET @i = 0;
SELECT cc.*,(@i:=@i+cc.当日注册人数) as 累计注册人数
FROM
(SELECT date_format(create_time,'%Y-%m-%d') as "日期" , count(*) as "当日注册人数" FROM t_member_info GROUP BY 日期) cc
CROSS JOIN (select @i:=0) x

 

# 查询每天的订单总数及每天的交易成功、交易失败、交易中的订单数
SELECT 
    date_format(create_time,'%Y-%m-%d') as "日期" , 
    count(1) as "当日订单数" ,
    sum(case WHEN apply_status = 6 THEN 1 else 0 end ) 交易成功,
    sum(case WHEN apply_status = 7 or apply_status = 2 THEN 1 else 0 end ) 交易失败,
    sum(case WHEN apply_status NOT IN(2,6,7) THEN 1 else 0 end ) 处理中
    
FROM t_draft_order_apply GROUP BY 日期

# count(1):统计过滤后所有数据，

# sum(case WHEN apply_status = 6 THEN 1 else 0 end ) ：如果apply_status =6，返回1否则返回0 sum:将返回值进行累加。