如何编写复杂sql

经常有人问我那非常复杂的sql是怎么写出来的,我一直不知道该怎么回答。 因为虽然我写这样的sql很顺手,可是我却不知道怎么告诉别人怎么写。很多人将这个问题归结为天赋,我却不这么看,我想这个不是天赋的问题,任何人经过一定有效率的学习和练习都能完成。有的人可能学习的快点,有的人可能学习的慢点,这个的确跟每个人有关,但只要经过有规律的练习,我觉得还是能够很快的写出符合要求的sql的。我也一直认为,不知道怎么写是因为没有找到一套行之有效的方法。在看《李小龙传奇》,略有感想,联系到这个问题,举个例子说明一下。 凯恩教授曾说:要善于把复杂的问题简单化。李小龙最后把深奥的武学简而单之的化为功和防,就是中国古典哲学中的阴和阳。对于一个复杂的报表,乍一看,很麻烦,n多张的表的数据堆砌在一起,似乎杂乱五章让初学者看了就头疼,更不用说写了。在复杂的sql中,比较有代表性的就是报表的sql,这里我们举一个简单的例子,套用凯恩教授的话,进行简单的分解:

假设有一张保险的报表,需要出如下数据(人是家庭成员数):

产品线 保险单数量 男性的数量 女性的数量 1人 2人 3人 >3人 25岁 25-35 35-45 45-55 >55

这个报表涉及到三张表,不算很复杂,分别是:

        product
        (
                product_id varchar(64),
                product_name varchar(255)
        )
        insurance
        (
                ins_id varchar(64),
                product_id varchar(64),
                cust_id varchar(64),
                额度等等...
        )
        customer
        (
                cust_id varchar(64),
                cust_name varchar(64),
                cust_sex varchar(8),
                cust_age integer,
                family_num integer
        )

在此我们尽可能的简化表结构,只列出我们用到的字段。从报表结构上看,insurance是主表,这个任何人都能看出来。再看报表产品线从product表中取,那么先将它列出来,在纸上写个字段:

product_id

再看保险单数量,这个从insurance表中取,其实就是count(ins_id),也写上, 现在变成如下形式:

product_id ins_id

再看后面的字段,因为都是从customer表中取,所以化繁为简,变成如下形式:

product_id ins_id cust_id

这个就是我们的报表结构,非常简单,只有三个字段,再看product_id和cust_id,都是从ins_id获得,那么删掉,最后只剩ins_id,ins_id从insurance中取,insurance表也就是我们的主表。这也是我们最初从报表结构上看出来的。

接着上面的,化为简单之后,我们再一步步的将报表字段逐个的填上,上面的过程是将复杂化为简单,下面我们还得从简单变成复杂,毕竟我们最后要的结果是个复杂的结果集。但是,从简单化为复杂,仍然要从简单入手,上面已经将一个复杂的问题简化为了三个简单的问题,那么接下来我们只要分别解决三个简单的问题就可以了。

只要把这三个简单的问题解决了,整个报表也就完成了。

首先,根据上面的简化结果我们得到的报表结构是:

ins_id

1

2

这个就是我们简化之后的报表结构,sql描述如下:

select

ins_id

from insurance

[where ...]

如果有条件,完善where语句即可

第二步,补充上product信息和客户信息,也就是转换为如下结构:

product_id ins_id cust_id

sql语句描述如下:

        select 
                a.ins_id,
                b.product_id,
                c.cust_id
        from
                insurance a,
                product b,
                customer c
        where 
                a.product_id=b.product_id
                and a.cust_id=c.cust_id

通过上面的sql,我们可以得到下面的结果集,也就是我们从上面由繁化简得到的结果集:

product_id ins_id cust_id

QD 1 1

QD 2 2

这里,我们使用了一个product_id代替了产品信息,使用cust_id代替客户信息,因为通过product_id我们就可以拿到有关该产品的任何信息,通过cust_id我们可以拿到关于该客户的任何信息,这也是简化的关键。

第三步,补充上报表需要的所有字段,这个很简单,上面已经查出来了,只需要罗列一下即可:

        select 
                a.ins_id,
                b.product_id,
                b.product_name,
                c.cust_id,
                c.cust_name,
                c.cust_sex,
                c.cust_age,
                c.family_num
        from
                insurance a,
                product b,
                customer c
        where 
                a.product_id=b.product_id
                and a.cust_id=c.cust_id

跟上面的sql相比,这里仅仅是把用到的字段列出来而已,并没有什么特别的地方。 但是,跟我们最后要的结果集相比,它还显得非常简陋,不过它已经具有了我们报表 需要的所有的字段了,已经具有最终报表的雏形了。

下面的步骤可能需要一些小小的技巧,但是也很简单,一点也不复杂。从报表结构上看 需要列出来的东西比上面的sql出来的字段要多很多。但实际上,所有的报表字段都是从上面的sql列出来的字段经过简单处理而得来的,比如性别的数量,其实就是count(‘男’)或者count('女'),年龄是count(cust_age)得到的。

初学者很容易陷入这样的一个陷阱:我统计男性的数量,那么怎么才能根据c.cust_sex算出男性和女性的数量呢?这是初学者的思维方式还没有转换为sql语言的思维方式的原因。对于上面的结果集而言,如果是人工计算的话,可能很容易看出来,但是对于数据库引擎来说,我们必须告诉它一个算法,也就是告诉它怎么计算。简单的说,必须将我们的思维方式转换为数据库引擎的思维方式。如果要计算数量的话,我们很容易想到count函数,但是对于性别来说,怎么才能同时count出男性和女性的数量呢?这只要简单的转化一下即可,把一个字段转换为两个字段,也就是把性别这个字段转换为男、女两个字段(下面我们使用f代表男,m代表女):

        select 
                a.ins_id,
                b.product_id,
                b.product_name,
                c.cust_id,
                c.cust_name,
                c.cust_sex,
                c.cust_age,
                c.family_num,
                -- 男
                0 as f,
                -- 女
                0 as m,
                -- 其他的依次类推
                -- 家庭成员数
                0 as p_1,
                0 as P_2,
                0 as p_3,
                0 as gt3,
                -- 客户年龄
                0 as lt25,
                0 as gt25lt35,
                0 as gt35lt45,
                0 as gt45lt55,
                0 as gt55
        from
                insurance a,
                product b,
                customer c
        where 
                a.product_id=b.product_id
                and a.cust_id=c.cust_id

这样我们就把所有需要的字段补充完整了,到这里离我们所要的结果集已经很近了!基本上已经能够看到结果集的样子了。但是,现在的结果集还不正确,因为大部分的统计字段还都是0,我们需要对它进行转换。 转换完成之后,只需要分组然后count一下即可。这里需要使用到case when语句,这个就是sql中的if else语句:

        select 
                a.ins_id,
                b.product_id,
                b.product_name,
                c.cust_id,
                c.cust_name,
                c.cust_sex,
                c.cust_age,
                c.family_num,
                -- 男
                -- 这个地方根据数据库字段的不同,处理方式也不同
                -- 如果数据库中cust_sex的数据类型本身就是0和1,那么就不需要转换
                -- 只列出来即可
                (case when c.cust_sex='男' then 1 else 0 end) as f,
                -- 女
                (case when c.cust_sex='女' then 1 else 0 end) as as m,
                -- 其他的依次类推
                -- 家庭成员数
                (case when c.family_num=1 then 1 else 0 end) as p_1,
                (case when c.family_num=2 then 1 else 0 end) as P_2,
                (case when c.family_num=3 then 1 else 0 end) as p_3,
                (case when c.family_num>3 then 1 else 0 end) as gt3,
                -- 客户年龄
                (case when c.cust_age<=25 then 1 else 0 end) as lt25,
                (case when c.cust_age>25 and c.cust_age<=35 then 1 else 0 end) as gt25lt35,
                (case when c.cust_age>35 and c.cust_age<=45 then 1 else 0 end) as gt35lt45,
                (case when c.cust_age>45 and c.cust_age<=55 then 1 else 0 end) as gt45lt55,
                (case when c.cust_age>55 then 1 else 0 end) as gt55
        from
                insurance a,
                product b,
                customer c
        where 
                a.product_id=b.product_id
                and a.cust_id=c.cust_id
        最后分组count一下即可:
        select 
                a.product_id,
                a.product_name,
                count(a.ins_id) as ins_num,
                -- 性别
                count(a.f) as f_num,
                count(a.m) as m_num,
                -- 成员数
                count(a.p_1) as p_1_num,
                count(a.p_2) as p_1_num,
                count(a.p_3) as p_1_num,
                count(a.gt3) as gt3_num,
                -- 年龄
                count(lt25) as lt25_num,
                count(gt25lt35) as gt25lt35_num,
                count(gt35lt45) as gt25lt35_num,
                count(gt45lt55) as gt25lt35_num,
                count(gt55) as gt55_num
        from(
                select 
                        a.ins_id,
                        b.product_id,
                        b.product_name,
                        c.cust_id,
                        c.cust_name,
                        c.cust_sex,
                        c.cust_age,
                        c.family_num,
                        -- 男
                        -- 这个地方根据数据库字段的不同,处理方式也不同
                        -- 如果数据库中cust_sex的数据类型本身就是0和1,那么就不需要转换
                        -- 只列出来即可
                        (case when c.cust_sex='男' then 1 else 0 end) as f,
                        -- 女
                        (case when c.cust_sex='女' then 1 else 0 end) as as m,
                        -- 其他的依次类推
                        -- 家庭成员数
                        (case when c.family_num=1 then 1 else 0 end) as p_1,
                        (case when c.family_num=2 then 1 else 0 end) as P_2,
                        (case when c.family_num=3 then 1 else 0 end) as p_3,
                        (case when c.family_num>3 then 1 else 0 end) as gt3,
                        -- 客户年龄
                        (case when c.cust_age<=25 then 1 else 0 end) as lt25,
                        (case when c.cust_age>25 and c.cust_age<=35 then 1 else 0 end) as gt25lt35,
                        (case when c.cust_age>35 and c.cust_age<=45 then 1 else 0 end) as gt35lt45,
                        (case when c.cust_age>45 and c.cust_age<=55 then 1 else 0 end) as gt45lt55,
                        (case when c.cust_age>55 then 1 else 0 end) as gt55
                from
                        insurance a,
                        product b,
                        customer c
                where 
                        a.product_id=b.product_id
                        and a.cust_id=c.cust_id
        ) a
        group by b.product_id, b.product_name

到现在未知,我们所要的结果就完全出来了。整个过程是一个化繁为简,再由简单堆砌为复杂的过程。对于初学者,培养出这样的思维方式似乎还很难,但是只要经过一两个这样的需求的练习,这中思维方式就很容易形成了,到最后,当你看到一个报表结构的时候,这样的思维过程仅仅是一瞬间的事,你的脑海里是n张的数据表格,经过相应关联之后,你的脑海里得到是报表结构的前一张结果集的结构,然后再往前推前一张结果集的结构,直到推到主表,然后再正向推一编,最后推到完整的报表结构,这个思维过程非常快,很可能再你的脑子只推一两步的时候,你就已经知道怎么写了。

原文发布于微信公众号 - java达人(drjava)

原文发表时间:2015-09-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏JavaEdge

软件设计七大原则实战(二)-开闭原则1 开闭原则的定义2 开闭原则的庐山真面目3 实例

开闭原则是Java世界里最基础的设计原则,它指导我们如何建立一个稳定的、灵活的系统,先来看开闭原则的定义: Software entities like cl...

2432
来自专栏java达人

通过人工智能编写自修改/自完善的程序

作者:Kory Becker 译者: Mr派 来源:http://www.primaryobjects.com/2013/01/27/using-artific...

2208
来自专栏钱曙光的专栏

一周极客热文:你工作了几年以及待遇大概多少?

本周被盖到400+楼的最最最热门文章是《大家聊聊待遇:工作几年,待遇大概多少??》。由于回复的内容“炒鸡”多,小编也无法很好的整理并得出高大上的结论,我想大家还...

21510
来自专栏SDNLAB

SDN实战团分享(二十):From Lithium to Beryllium,ODL最新动态变化

大家好, 感谢宇峰的邀请, 和大家分享OpenDaylight最新发布的一些进展,胶片主要来自Neela Jacques, Phil Robbs和Colin D...

2764
来自专栏大数据钻研

证明你是坏程序员的7个迹象

你是一个好程序员还是坏程序员? 下面这七种迹象表明,你可能正在往坏的方向发展。 1)开始编码之前没有计划 说到这一点,我自己其实也并没有做到,我总是喜欢直接编码...

2896
来自专栏斑斓

教会你何时定义领域服务

若遵循基于面向对象设计范式的领域驱动设计,并用以应对纷繁复杂的业务逻辑,则强调领域模型的充血设计模型已成为社区不争事实。我将Eric提及的战术设计要素如Enti...

1043
来自专栏精讲JAVA

程序员们,曾经是否有个bug让你开始怀疑人生

相信程序员们都有一个共同的女朋友。这个女朋友总是阴魂不散,时不时还不忘调戏下男朋友程序员,而且你依然对她欲罢不能、想入非非。

1072
来自专栏Java学习网

如何编写可靠的代码

介绍 当你得到一个小older-my但你妻子说我不是老愤世嫉俗者。这是为什么许多老男人不要说(或写)那么多:我们知道没有人注意。当你获得AARP另一个问题是,你...

3338
来自专栏GopherCoder

『Go 语言学习专栏』-- 第十三期

2102
来自专栏TEG云端专业号的专栏

【Augustzhang 张元龙】知根知底,方能游刃有余

小编语:据江湖传闻,龙哥从初中就开始写代码,高中通过计算机竞赛免试上了大学,大学里则是ACM大神。2010年毕业加入腾讯,先后从事密保、验证码等后台研发工作,...

3504

扫码关注云+社区

领取腾讯云代金券