mysql

最新推荐文章于 2025-02-12 15:31:59 发布

WSF �

最新推荐文章于 2025-02-12 15:31:59 发布

阅读量185

点赞数

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/WSFLOVE/article/details/89517375

版权

数据库专栏收录该内容

2 篇文章

订阅专栏

手动结束失败的事物回滚

select * from information_schema.innodb_trx;
KILL trx_mysql_thread_id

CURRENT_TIMESTAMP
创建数据自动插入更新时间
格式化数据库时间戳
https://blog.youkuaiyun.com/u010307687/article/details/87273255

 FROM_UNIXTIME(FLOOR(t2.create_time/1000),'%Y-%m-%d') as '创建日期'

行转列

CASE t1_sub.F0000006 WHEN 'true' THEN t1_sub.F0000029 ELSE 0  END  '中标金额',
CASE t1_sub.F0000006 WHEN 'false' THEN t1_sub.F0000029 ELSE 0  END '未中标金额',

多表关联，子查询…

关于一对多的表关联
第一次使用jion之后就变成了多对多关系
按照某个字段去重

	SELECT * FROM (SELECT * FROM table1 ORDER BY table1.CREATETIME DESC LIMIT 100000000) GROUP BY `name````
	全外链接 full join    MySQL不支持
(SELECT * from a left JOIN b on a.name=b.id) UNION&nbsp;&nbsp;&nbsp;(SELECT * from a RIGHT JOIN b on a.name=b.id );

去重

SELECT z1.app_run_id,z1.F0000073 ,z1.F0000096
FROM (SELECT * FROM t_eca0fdfe7c0730989b9a097dc2c802c2 tz1 ORDER BY tz1.F0000008 desc LIMIT 10000000) z1
GROUP BY z1.F0000073

只筛选条件内的不能以哪个表为主

SELECT * FROM
left 
on 
where  //筛选条件
GROUP BY //分组

按自身字段进行筛选求和等…
==>先自关联，在筛选，在分组求和；

SELECT agga.F0000009,agga.adate,agga.tadate,SUM(IFNULL(agga.tF0000083,0)) QSJCB FROM 
(SELECT * FROM 
(SELECT * FROM 
(SELECT * FROM 
(SELECT tt1.F0000009,tt1.F0000083,tt2.task_name,FROM_UNIXTIME(FLOOR(tt2.deal_time/1000),'%Y-%m-%d-%H-%i') adate FROM t_90d9a46277df0bd1a524366785fd283b tt1 
LEFT JOIN ru_wf_task tt2 
ON tt1.app_run_id=tt2.app_run_id AND tt2.task_name='反馈申请人(经办)' AND task_action=1) ta1
WHERE ta1.task_name is not null ) at1
LEFT JOIN 
(SELECT * FROM 
(SELECT tt1.F0000009 tF0000009,tt1.F0000083 tF0000083,tt2.task_name ttask_name,FROM_UNIXTIME(FLOOR(tt2.deal_time/1000),'%Y-%m-%d-%H-%i') tadate FROM t_90d9a46277df0bd1a524366785fd283b tt1 
LEFT JOIN ru_wf_task tt2 
ON tt1.app_run_id=tt2.app_run_id AND tt2.task_name='反馈申请人(经办)' AND task_action=1) ta1
WHERE ta1.ttask_name is not null ) at2
ON at1.F0000009=at2.tF0000009) aga 
WHERE aga.tadate<=aga.adate) agga
GROUP BY agga.F0000009,agga.adate

结果集拼接

select sum(ifnull(tt1.F0000033,0)) ljyfje from t_d223905f0ee767df70154f67b1e4bc3c tt1
    left join ru_wf_task tt2
    on tt1.app_run_id=tt2.app_run_id
    where tt2.task_name='出纳办理' and tt2.task_action=1
union
select sum(ifnull(tt3.F0000033,0)) ljyfje from t_86e71ec553040620f835f1da4a04fc82 tt3
    left join ru_wf_task tt4
    on tt3.app_run_id=tt4.app_run_id
    where tt4.task_name='出纳办理' and tt4.task_action=1

查找

select tt1.F0000001 title,
        tt1.app_run_id id
        from t_f082721cb1780f419512e5139bec5612 tt1
        where find_in_set(tt1.app_run_id,
        REPLACE(REPLACE(REPLACE('${id}','[',''),']',''), '"', ''))