乐趣区

关于javascript:SQL-复杂查询

SQL 简单查问指的就是子查问。

为什么子查问叫做简单查问呢?因为子查问相当于查问嵌套查问,因为嵌套导致复杂度简直能够被有限放大(有限嵌套),因而叫简单查问。上面是一个最简略的子查问例子:

SELECT pv FROM (SELECT pv FROM test)

下面的例子等价于 SELECT pv FROM test,但因为把表的地位替换成了一个新查问,所以摇身一变成为了简单查问!所以简单查问不肯定真的简单,甚至可能写出和一般查问等价的简单查问,要防止这种无意义的行为。

咱们也要借此机会理解为什么子查问能够这么做。

了解查问的实质

当咱们查一张表时,数据库认为咱们在查什么?

这点很重要,因为上面两个语句都是非法的:

SELECT pv FROM test

SELECT pv FROM (SELECT pv FROM test)

为什么数据库能够把子查问当作表呢?为了对立了解这些概念,咱们有必要对查问内容进行形象了解: 任意查问地位都是一条或多条记录

比方 test 这张表,显然是多条记录(当然只有一行就是一条记录),而 SELECT pv FROM test 也是多条记录,然而因为 FROM 前面能够查问任意条数的记录,所以这两种语法都反对。

不仅是 FROM 能够跟单条或多条记录,甚至 SELECTGROUP BYWHEREHAVING 后都能够跟多条记录,这个前面再说。

说到这,也就很好了解子查问的变种了,比方咱们能够在子查问内应用 WHEREGROUP BY 等等,因为无论如何,只有查问后果是多条记录就行了:

SELECT sum(people) as allPeople, sum(gdp), city FROM (
  SELECT people, gdp, city FROM test
  GROUP BY city
  HAVING sum(gdp) > 10000
)

这个例子就有点业务含意了。子查问是从内而外执行的,因而咱们先看外部的逻辑:依照城市分组,筛选出总 GDP 超过一万的所有地区的人口数量明细。外层查问再把人口数加总,这样就能比照每个 GDP 超过一万的地区,总人口和总 GDP 别离是多少,不便对这些重点城市做比照。

不过这个例子看起来还是不太天然,因为咱们没必要写成简单查问,其实简略查问也是等价的:

SELECT sum(people) as allPeople, sum(gdp), city FROM test
GROUP BY city
HAVING sum(gdp) > 10000

那为什么要多此一举呢?因为简单查问的真正用法并不在这里。

视图

正因为子查问的存在,咱们才可能以相似抽取变量的形式,抽取子查问,这个抽取进去的形象就是视图:

CREATE VIEW my_table(people, gdp, city)
AS
SELECT sum(people) as allPeople, sum(gdp), city FROM test
GROUP BY city
HAVING sum(gdp) > 10000

SELECT sum(people) as allPeople, sum(gdp), city FROM my_table

这样的益处是,这个视图能够被多条 SQL 语句复用,不仅可维护性变好了,执行时也仅需查问一次。

要留神的是,SELECT 能够应用任何视图,但 INSERT、DELETE、UPDATE 用于视图时,须要视图满足一下条件:

  1. 未应用 DISTINCT 去重。
  2. FROM 单表。
  3. 未应用 GROUP BY 和 HAVING。

因为下面几种模式都会导致视图成为聚合后的数据,不不便做除了查以外的操作。

另外一个知识点就是物化视图,即应用 MATERIALIZED 形容视图:

CREATE MATERIALIZED VIEW my_table(people, gdp, city)
AS ...

这种视图会落盘,为什么要反对这个个性呢?因为一般视图作为长期表,无奈利用索引等优化伎俩,查问性能较低,所以物化视图是较为常见的性能优化伎俩。

说到性能优化伎俩,还有一些比拟常见的理念,即把读的复杂度摊派到写的时候,比方提前聚合新表落盘或者对 CASE 语句固化为字段等,这里先不开展。

标量子查问

下面说了,WHERE 也能够跟子查问,比方:

SELECT city FROM test
WHERE gdp > (SELECT avg(gdp) from test
)

这样能够查问出 gdp 大于平均值的城市。

那为什么不能间接这么写呢?

SELECT city FROM test
WHERE gdp > avg(gdp) -- 报错,WHERE 无奈应用聚合函数 

看上去很美妙,但其实第一篇咱们就介绍了,WHERE 不能跟聚合查问,因为这样会把整个父查问都聚合起来。那为什么子查问能够?因为子查问聚合的是子查问啊,父查问并没有被聚合,所以这才合乎咱们的用意。

所以下面例子不适合的中央在于,间接在以后查问应用 avg(gdp) 会导致聚合,而咱们并不想聚合以后查问,但又要通过聚合拿到均匀 GDP,所以就要应用子查问了!

回过头来看,为什么这一节叫标量子查问?标量即繁多值,因为 avg(gdp) 聚合进去的只有一个值,所以 WHERE 能够把它当做一个繁多数值应用。反之,如果子查问没有应用聚合函数,或 GROUP BY 分组,那么就不能应用 WHERE > 这种语法,但能够应用 WHERE IN,这波及到单条与多条记录的思考,咱们接着看下一节。

单条和多条记录

介绍标量子查问时说到了,WHERE > 的值必须时繁多值。但其实 WHERE 也能够跟返回多条记录的子查问后果,只有应用正当的条件语句,比方 IN:

SELECT area FROM test
WHERE gdp IN (SELECT max(gdp) from test
  GROUP BY city
)

下面的例子,子查问依照城市分组,并找到每一组 GDP 最大的那条记录,所以如果数据粒度是区域,那么咱们就查到了每个城市 GDP 最大的那些记录,而后父查问通过 WHERE IN 找到 gdp 合乎的复数后果,所以最初就把每个城市最大 gdp 的区域列了进去。

但实际上 WHERE > 语句跟复数查问后果也不会报错,但没有任何意义,所以咱们要了解查问后果是单条还是多条,在 WHERE 判断时抉择适合的条件。WHERE 适宜跟复数查问后果的语法有:WHERE INWHERE SOMEWHERE ANY

关联子查问

所谓关联子查问,即父子查问间存在关联,既然如此,子查问必定不能独自优先执行,毕竟和父查问存在关联嘛,所以关联子查问是先执行外层查问,再执行内层查问的。要留神的是,对每一行父查问,子查问都会执行一次,因而性能不高(当然 SQL 会对雷同参数的子查问后果做缓存)。

那这个关联是什么呢?关联的是每一行父查问时,对子查问执行的条件。这么说可能有点绕,举个例子:

SELECT * FROM test where gdp > (select avg(gdp) from test
  group by city
)

对这个例子来说,想要查找 gdp 大于按城市分组的均匀 gdp,比方北京地区按北京比拟,上海地区按上海比拟。但很惋惜这样做是不行的,因为父子查问没有关联,SQL 并不知道要依照雷同城市比拟,因而只有加一个 WHERE 条件,就变成关联子查问了:

SELECT * FROM test as t1 where gdp > (select avg(gdp) from test as t2 where t1.city = t2.city
  group by city
)

就是在每次判断 WHERE gdp > 条件时,从新计算子查问后果,将平均值限定在雷同的城市,这样就合乎需要了。

总结

学会灵活运用父子查问,就把握了简单查问了。

SQL 第一公民是汇合,所以所谓父子查问就是父子汇合的灵便组合,这些汇合能够呈现在简直任何地位,依据汇合的数量、是否聚合、关联条件,就派生出了标量查问、关联子查问。

更深刻的理解就须要大量实战案例了,但万变不离其宗,把握了简单查问后,就能够了解大部分 SQL 案例了。

探讨地址是:精读《SQL 简单查问》· Issue #403 · ascoders/weekly

如果你想参加探讨,请 点击这里,每周都有新的主题,周末或周一公布。前端精读 – 帮你筛选靠谱的内容。

关注 前端精读微信公众号

<img width=200 src=”https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg”>

版权申明:自在转载 - 非商用 - 非衍生 - 放弃署名(创意共享 3.0 许可证)

退出移动版