SQL总结

最新推荐文章于 2025-07-25 19:37:45 发布

OnEstepEnD

最新推荐文章于 2025-07-25 19:37:45 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏：小结文章标签： sql join date null path 数据库

本文链接：https://blog.youkuaiyun.com/OnEstepEnD/article/details/7528130

小结专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用INSTR函数提高SQL查询效率，避免使用LIKE而导致索引失效的问题。此外，还详细讲解了如何利用开窗函数进行复杂的数据统计与分析，并提供了多个实际案例，包括数据拼接、查询结果组合等高级技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

比较字符串，使用like将不能用索引，用instr(str,str)>0代替，效率更高
select count(*) from checkcardtb cc where instr(cc.belongplat,'解放碑')>0

======注意数据库查询时 or条件的使用必须用括号界定条件范围，否则很容易造成错误的连接，极大的影响查询效率。

--对查询出来的数据按某列以某种固定关系拼接的函数。但是有的时候用逗号好像不可以。

SELECT SYS_CONNECT_BY_PATH(cp.checkgroup, '>') "Path"
FROM checkpersonlisttb cp
CONNECT BY PRIOR cp.checkcardid = cp.id;

select * from (SELECT checkcardid,
         MAX(LTRIM(SYS_CONNECT_BY_PATH(checkgroup, ';'), ';')) sum_jm
    FROM (SELECT id,checkcardid,
                 checkgroup,
                 ROW_NUMBER() OVER(PARTITION BY checkcardid ORDER BY id) RN
            FROM checkpersonlisttb)
   START WITH RN = 1
CONNECT BY PRIOR RN + 1 = RN AND PRIOR checkcardid = checkcardid
   GROUP BY checkcardid) cardgroup;

--开窗函数，主要功能是分组统计排序，与group by的区别是用了group by分组函数后，所查询的列只能是被分组的列。而开窗函数可以是任何列。
select distinct cd.platformname,cd.behaivorid,cd.behaivor,count(cd.behaivorid) over(Partition by cd.behaivorid) as countBehavior
from carddetailtb cd where cd.platformname like '%山洞%';
--但是使用了开窗函数，好像不能使用distinct去重，现有的做法是，再查询结果外面再加一层查询
select count(1) from (
select distinct tt.tunit,tt.tplatform,tt.tlevel,tt.countBehaviorlevel,tt.tproject,tt.countBehaviorproject,tt.tbehaior,tt.countBehavior from
(
select a.auint as tunit,a.aplatform as tplatform,a.alevel as tlevel,count(a.alevelid) over(Partition by a.auint,a.aplatform,a.alevelid) as countBehaviorlevel,
                                a.aproject as tproject,count(a.aprojectid) over(Partition by a.auint,a.aplatform,a.aprojectid) as countBehaviorproject,
                                a.abehavior as tbehavior,count(a.abehaviorid) over(Partition by a.auint,a.aplatform,a.abehaviorid) as countBehavior
from (
           select cd.platformdistrict as auint,cd.platformname as aplatform,
                 bl.id as alevelid,bl.levelname as alevel,bp.id as aprojectid,bp.projectname as aproject,b.behaviorid as abehaviorid,b.summary as abehavior
           from carddetailtb cd,behviortb b,behviorprojecttb bp,behviorleveltb bl,checkcardtb cc
                 where cc.checkcardid=cd.checkcardid and cd.behaivorid=b.behaviorid and b.projectid=bp.id and bp.levelprojectid=bl.id
       ) a where a.aplatform like '%山洞交巡警平台%'
) tt order by tt.tunit,tt.tplatform,tt.tlevel,tt.tproject
) aa;

--不同表查询结果的组合。查询时插入新列。
select behaivorid,behaivor,needreply ,0 as yzg, count(*) as cszg,0 as wzg, 0 as wzgcs,0 as num from
   carddetailtb t1 left join behviortb t2 on t1.behaivorid = t2.behaviorid left join behviorprojecttb t3 on t2.projectid = t3.id left join behviorleveltb t4 on t3.levelprojectid = t4.id
     where checkcardid is not null and completestatus = '已整改' and istimeout = '是' and isreply ='是' and t2.pid!='1000' and
      checktime between to_date('2012-02-07 00:00:00','yyyy-MM-dd HH24:mi:ss') and to_date('2012-02-07 00:00:00','yyyy-MM-dd HH24:mi:ss')
      group by behaivor,behaivorid,needreply
      union
      select behaivorid,behaivor,needreply,0 as yzg, 0 as cszg,count(*) as wzg, 0 as wzgcs,0 as num from carddetailtb t1 left join behviortb t2 on t1.behaivorid = t2.behaviorid left join behviorprojecttb t3 on t2.projectid = t3.id left join behviorleveltb t4 on t3.levelprojectid = t4.id
      where checkcardid is not null and completestatus is null and istimeout is null    and isreply ='是' and t2.pid!='1000'
       and sysdate <=timelimit and checktime between to_date('2012-02-07 00:00:00','yyyy-MM-dd HH24:mi:ss') and to_date('2012-02-07 00:00:00','yyyy-MM-dd HH24:mi:ss')
       group by behaivor,behaivorid,needreply

--字符串拼接，双竖线，其用法其实和加号一样。
select cg.groupname as auint,gp.personname as aplatform,
                 bl.id as alevelid,bl.levelname as alevel,bp.id as aprojectid,bp.projectname as aproject,b.behaviorid as abehaviorid,b.summary as abehavior
           from carddetailtb cd,behviortb b,behviorprojecttb bp,behviorleveltb bl,checkcardtb cc,checkgrouptb cg,grouppperson gp,checkpersonlisttb cp,uniteareatb ua
                 where cc.checkcardid=cd.checkcardid and cc.checkcardid=cp.checkcardid and cd.behaivorid=b.behaviorid and b.projectid=bp.id and bp.levelprojectid=bl.id
                       and cp.checkgroup like '%'||cg.groupname||'%' and cg.checkgroupid=gp.groupid and cp.checkname=gp.personname
                         and cc.checkdepartment=ua.areaname
--将整型数据转换成字符串类型，可以用to_char()函数。

注：数据库只是用来存储数据的东西，不要让它做过多的逻辑处理，影响性能啊 = =|||。