万能查询SQL统计方面.docxVIP

上周我总结了SQL的使用及一些使用技巧.这周补充总结下项目上遇到(或根据遇到的情况发散学习)的SQL技巧.?一、万能查询SQL（统计数据时的万能，也许其他方面并不万能）所谓万能SQL是在不考虑效率的情况下，几乎都能通用的查询SQL。目前我发现的万能SQL包括两种形式：形式一、相同的记录需要得到多种该记录对应的唯一结果，而这些结果不能在同一个SQL查询。先看下我的公式：Select * from (从主表查询唯一记录数) t1,(查询t1记录唯一对应的结果1) t2,(查询t1记录唯一对应的结果2)t3,Where t1…=t2….(+)And t1…=t2…..(+)再解释下上面的查询第一步，查询得到t1，t1做为结果的基列，是正确结果的一部分。T1的记录数与最终结果记录数相同。第二步，t2,t3两个查询只是对t1结果列的一个补充。在我的应用中，t2与t3大多都是以与t1相关联的字段进行group by的一个统计结果。需要注意对应t1的一条记录，t2 t3 不能有多条结果。第三步，t1与t2，t3进行外连接，查询出结果。形式二、UNION与UNION ALL两个或多个来源不同的信息需要汇总到一起，就需要UNION或UNION ALL形式一的思想是：固定行数不变。形式二的思想是：固定列数不变。那么形式一加形式二是否就实现了万能查询了呢？至少对我现在这个应用去统计非常苛刻的需求来说万能查询几乎都是万能的。需要注意的是：万能查询效率问题：在一些情况下，大多查询都走索引，效率还是很高的。在另外一些情况下，查询也是走了索引，但效率就很低，为什么？因为查询数据量大，SQL结构不是最优。还有一种情况，查询不走索引，当然不走索引不一定慢。遇到这种问题时，一般把查询拆开借用临时表会有所改善，另外种方法是对SQL的查询语句本身的优化（结构上与SQL效率上进行优化）二、 AVG与SUM当一张表包括两种信息（一种是已经统计好的信息，一种是还没统计的信息），如果需求是统计已经统计好的信息或统计还没统计的信息，很简单的一句SQL就搞定了。但如果要同时统计两种信息时，第一种想到的办法是自连接：已经统计好的信息与还没统计的信息分开统计后再做连接。（用到万能连接思想）第二种方法，例子一时想不起来了，先看下SQL结构Select ,,,avg(t1.r1),sum(t1.r2),from table1 t1group by …t1.r1字段已经是统计过的数据了，使用avg(t1.r1) + group by结构计算的avg(t1.r1)的值还是t1.r1，应该这个group里面每个值都一样，在平均下还是它自己。Sum(t1.r2)根据这个group对r2求和。AVG与SUM这种形式，在一般情况下不会出现，只是在一些不是很规范的表结构的表里会出现，当然不是很规范的表有时确实是为了解决查询效率的问题。三、重复记录由于IT给我的很多数据都是经过统计后的结果给我，我拿到的结果难免会有重复记录。要把重复记录视为一条记录处理起来可不简单，很容易搞错。记住下面的方法就不再会为重复记录头疼。1．先来个group，得到每条重复记录的max(rowid)2． Max(rowid)再和源表关联，重复记录没了。看下面例子表zjl_temp_01中，同一记录中r2,r3,r6重复的为重复记录。不管这条记录其他列是否相同，在后面的查询中我只使用r2,r3,r6重复的记录中的一条。WITH tt AS (SELECT t02.*FROM (SELECT t.r2, t.r3, t.r6, MAX(t.rowid) row_idFROM zjl_temp_01 tGROUP BY t.r2, t.r3, t.r6) t01,(SELECT DISTINCT * FROM zjl_temp_01) t02WHERE t01.row_id = t02.rowid)删除重复记录 delete from t where t.rowid(…max(rowid)…where ….)四、不使用NOT由于NOT不走索引，在SQL中尽量少使用NOT。NOT的替换Select * from t where t.a1!=50可换成Select * from t where (t.a150 or t.a150)NOT IN的替换外连接替换NOT INSELECT gybj, real_priceFROM scm_box_material_listWHERE is_delayed = YAND gybj IS NULLAND real_price != 0替换成SELECT gybj, real_priceFROM scm_box_material_list slWHERE is_delayed = YAND gybj