随笔- 174 文章- 0 评论- 2 阅读- 15万

SQL查询 —— 特殊查询

1.SQL（含索引）2018-06-11 2.SQL优化（SQL + 索引）2018-06-11 3.BTree和B+Tree和Hash索引详解2018-06-11 4.InnoDB和MyISAM是否支持hash索引2018-06-11 5.MySQL常见的三种存储引擎2018-06-11 6.Explain执行计划2018-06-11 7.explain执行计划包含的信息2022-05-31 8.MySQL 慢查询日志如何查看及配置2022-05-30 9.如何保障mysql和redis之间的数据一致性？2022-05-30 10.MySQL中有哪些锁？2022-05-26 11.mysql⾼并发插⼊慢优化【mysql层面优化】2022-05-25 12.MySql隔离级别：RU / RC / RR / S + 脏读 / 不可重复读 / 幻读 / 可重复读2020-08-11

13.SQL查询 —— 特殊查询2018-06-22

14.MySQL主从复制（Master-Slave）实践2018-06-12 15.Innodb引擎中B+树一般有几层？能容纳多少数据量？2023-08-28 16.MYSQL的JOIN是如何使用索引的2023-07-25 17.MySQL如何保证事务内一直读到最新数据？2023-07-25 18.MySQL 回表和覆盖索引详解2023-07-25 19.Mysql联合索引是如何找到数据的2023-07-25 20.MySQL 通过Docker搭建主从同步2022-06-02 21.初步理解MySQL的gap锁2022-06-02 22.mysql_14_count（*）为什么这么慢2022-06-01 23.MYSQL索引使用注意事项2022-06-01 24.二叉树、B树、B+树、红黑树的本质区别以及各个应用场景2022-06-01

一、去重，去除多个字段相同的记录，保留一个

场景一：去除表中所有重复数据，均只保留一个

delete from tbl_dept where dept_id not in (
    select id from (
        select min(dept_id) as id
        from tbl_dept 
        group by dept_name,rm_flag
    ) as temp
)

场景二：只去除表中市声部的重复数据，且只保留一个

delete from tbl_dept where dept_id not in (
    select id from (
        select min(dept_id) as id
        from tbl_dept 
        group by dept_name,rm_flag
    ) as temp
) and dept_name like '%市声部%'

二、查询名称为'%user%'的表和有'user'字段的表

名称中有'%user%'的表

select table_name from information_schema.tables where table_schema='neimenggu_new2' and table_name like '%user%'

有'user'字段的表

select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME from information_schema.columns where column_name like 'user'

三、存在一个表而不在另一个表中的数据

A、B两表，找出ID字段中，存在A表，但是不存在B表的数据。A表总共13w数据，去重后大约3W条数据，B表有2W条数据，且B表的ID字段有索引。

方法一

使用 not in ,容易理解,效率低~执行时间为：1.395秒~

select distinct A.ID from A where A.ID not in (select ID from B)

方法二

使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID字段为 null的记录~执行时间：0.739秒~

select A.ID from A left join B on A.ID=B.ID where B.ID is null

方法三

逻辑相对复杂,但是速度最快~执行时间: 0.570秒~

select * from B where (select count(1) as num from A where A.ID = B.ID) = 0

四、行转列、合并字段、列转行

-- 行转列（最大值）

select user_name,
    MAX(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
    MAX(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
    MAX(CASE WHEN course='英语' THEN score ELSE 0 END) 英语
    from TEST_TB_GRADE GROUP BY user_name

-- 行转列（合计）

select user_name,
    SUM(CASE WHEN course='数学' THEN score ELSE 0 END) 数学,
    SUM(CASE WHEN course='语文' THEN score ELSE 0 END) 语文,
    SUM(CASE WHEN course='英语' THEN score ELSE 0 END) 英语
    from TEST_TB_GRADE GROUP BY user_name

-- 合并字段

select user_name,GROUP_CONCAT(course,":",score) from TEST_TB_GRADE GROUP BY user_name

-- 列转行

select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE;

SQL递归向下、向上

SELECT level, DATA.* FROM( 
    SELECT @ids as _ids, 
        (SELECT @ids := GROUP_CONCAT(id) FROM base_document WHERE FIND_IN_SET(parent_id, @ids)) as cids, 
        @l := @l+1 as level 
    FROM base_document, 
    (SELECT @ids :=1561620440273649664, @l := 0 ) b 
    WHERE @ids IS NOT NULL 
) id, base_document DATA 
WHERE FIND_IN_SET(DATA.id, _ids) ORDER BY level, id


SELECT T2.* 
FROM ( 
    SELECT 
        @r AS _id, 
        (SELECT @r := parent_id FROM base_document WHERE id = _id) AS parent_id, 
        @l := @l + 1 AS lvl 
    FROM 
        (SELECT @r := 1561620402101288960, @l := 0) vars, 
        base_document h 
    WHERE @r <> 0) T1 
JOIN base_document T2 
ON T1._id = T2.id 
ORDER BY T1.lvl DESC