`
longgangbai
  • 浏览: 7339343 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

Sql语句中同样实现关联和子查询选择策略

阅读更多

  

 在项目中查询关于客户订单信息和客户所在帐户的信息中,根据订单号码和客户的会员号码,查询客户的订单的信息和客户所在公司的信息:

     会员卡号码在会员表,会员表关联子帐户表,子帐户表关联主帐户表,主帐户关联公司信息表

    订单表关联,酒店表,关联房型表等。

 

 

       选择每位客户的所有订单非常简单;事实上任何 SQL 新手都可以在几分钟内构造出这个查询。然而,当你想在干草堆中找出一根针时,就需要一点窍门了。下面我将向你展示如何选择每位客户最新的订单,稍微改变一点语法还可以选择每位客户最老的订单。

          这里最大的问题是相互关联子查询(correlated subqueries),相互关联子查询与嵌套子查询有很明显的区别。

 

      在嵌套子查询中,内部查询只执行一次并返回它的值给外部查询,然后外部查询在它的处理中使用内部查询返回给它的值。

    而在相互关联子查询中,对于外部查询返回的每一行数据,内部查询都要执行一次。另外,在相互关联子查询中是信息流是双向的。

   外部查询的每行数据传递一个值给子查询,然后子查询为每一行数据执行一次并返回它的记录。然后,外部查询根据返回的记录做出决策。

下面我们使用Northwind 数据库作为一个例子。我们需要列出由每位客户下达的最新的订单。

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate =
(SELECT Max(OrderDate)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)

 

      对于外部查询返回的每行数据,内部查询都会执行,条件是限制其结果集与CustomerID匹配。Max() 调用将结果集限制为感兴趣的一行数据。

      如果有500位客户下达了订单,内部查询将执行500次

     每个CustomerID执行一次。现在我们已经有了答案:返回90行数据,也就是说这90条数据中每条数据都代表一位客户下达了一个订单。

    你可以很容易地对这个查询进行扩展。

   例如,你可能想查询每个客户所下订单的最新两个订单。在这种情况下,需要将内部查询改为以下这种形式:

 

 SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate IN(
SELECT TOP 2 o2.OrderDate
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID

 

你可以以不同方式优化这个例子,以避免开销很大的 IN() 谓词。

 

  以我的经验来看,很多开发人员在学习完相互关联子查询之后很快就会忘记其概念。这真的很让人感到遗憾,因为相互关联子查询可以很简单很优雅地回答难度很大的问题。

 

   我的意见是这个语法可行,并且用一个 SQL 语句就可以查询出期望的结果。这使我想到了一位开发经理反复跟我说的一句话:“第一个版本是使其能够运行,第二个版本是使其能够更快地运行。”

 

 

 

 

开始写的子查询语句实现如下:

                select c.corp_cnname  from t_buss_corporation c  where  c.corp_no  in
                (
                   select mainA.Corp_No  from  t_buss_main_account  mainA where MainA.Main_Acnt_No in
                  (
                    select cAccount.Main_Acnt_No  from t_Buss_Child_Account cAccount where cAccount.Child_Acnt_No in
                    (
                      select CA.CHILD_ACNT_NO  from T_BUSS_CARD  CA where  CA.Memb_Card_No=9123456701
                    )
                  )              
                )

 

改写后的SQL: 使用关联查询如下:

 

                 
                select c.corp_cnname from
                (
                    (T_BUSS_CARD  CA inner join t_Buss_Child_Account childA on childA.Child_Acnt_No=CA.Child_Acnt_No)
                    inner join t_buss_main_account mainA on childA.Main_Acnt_No=mainA.Main_Acnt_No
                ) inner join t_buss_corporation c on c.corp_no=mainA.Corp_No
                 where CA.Memb_Card_No =9123456701

 

 

 

 

 

下面测试转自一位数据管理人员的测试结果:

 

SQL优化--使用关联查询代替子查询

 

   测试例子:

    子查询:

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->select a.*,
(
select workflowname from workflowbase where id=workflowid) workflowname
from  [[zping.com]]] a
where a.operator='402882ed1112669201112a8385892f33'

 

   执行结果:

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->(360 行受影响)
表 
'Worktable'。扫描计数 360,逻辑读取 142334 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 
'workflowbase'。扫描计数 1,逻辑读取 1589 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 
'[zping.com]'。扫描计数 1,逻辑读取 366 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

 

 关联查询:

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->select a.*,b.workflowname
from  [[zping.com]]] a inner join  workflowbase b on a.workflowid=b.id
where operator='402882ed1112669201112a8385892f33'

  执行结果:  

<!--<br /><br />Code highlighting produced by Actipro CodeHighlighter (freeware)<br />http://www.CodeHighlighter.com/<br /><br />-->(360 行受影响)
表 
'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 
'workflowbase'。扫描计数 1,逻辑读取 1589 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 
'[zping.com]'。扫描计数 1,逻辑读取 366 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

 

   这里:子查询IO次数:142334 +1589+366=144289

           关联查询IO次数1589 +366 =1922

       关联查询是子查询的75倍   

 

总结:

 

     使用子查询和关联查询,一般情况下如果能用关联查询就不用子查询,

 

 

      项目中采用Hibernate的运行缓慢,一面时我们对于Hibernate的API掌握的不够,另外一方面原因是因为他自动生产的子查询的原因可能也产生一部原因把。所以我自己写SQL时禁止使用子查询尽可能的使用关联查询,提供自己的查询效率。

 

 

以为数据库人员的blog,很值得去看的!

http://www.cnblogs.com/zping/

分享到:
评论

相关推荐

    Oracle sql语句多表关联查询

    Oracle SQL 语句多表关联查询是数据库管理系统中的一种常见查询方式,通过了解字符串和数字的比较、分组查询、HAVING 子句、查询顺序、Oracle 中的伪列、子查询和表连接等知识点,可以帮助我们更好地编写 SQL 语句,...

    数据库设计(包括select语句、子查询、语句嵌套)

    在本话题中,我们将探讨数据库设计的基础,特别关注SELECT语句、子查询以及语句嵌套这三个核心概念。 首先,数据库设计不仅仅是创建表格,更重要的是理解和分析业务需求,以构建一个能够支持高效数据存储、检索和...

    经典sql语句,关联到四张表,非常经典

    CASE WHEN 语句用于在 SQL 查询中创建条件 (如果...那么...) 语句。它可以用于计算和返回基于某些条件的结果。在例子中,可以看到CASE WHEN 语句用于处理可能为零的情况,避免除以零的错误: ```sql CASE WHEN sbzs ...

    sql多表关联语句

    通过上述分析可以看出,此SQL查询利用了多种高级特性,如左连接、窗口函数、子查询等,实现了从多个表中提取和整合数据的目的。这种复杂的查询方式在实际应用中非常常见,尤其适用于需要处理大量关联数据的企业级...

    3.4-数据查询SQL语句-3.ppt

    他们需要懂得如何使用数据查询SQL语句来检索数据,如何使用子查询、连接关联查询、JOIN…ON连接查询语句等多种查询方式来实现多表关联查询,并了解外部连接的概念和实现方式,以便在数据库中检索数据。

    SQL语句查询数据表主键和所有字段

    在数据库管理中,了解如何通过SQL语句查询数据表的主键和所有字段是一项基本而重要的技能。这不仅有助于数据库管理员或开发人员更好地理解数据库结构,还能在进行数据操作、优化查询性能或解决数据完整性问题时提供...

    SQL语句嵌套的实例.rar_SQL语句_sql 语句_sql嵌套

    嵌套SQL语句是数据库操作中的高级技巧,能够帮助我们处理多表关联、分组统计等复杂数据问题。下面我们将详细探讨SQL语句嵌套的相关知识点。 1. **子查询**:子查询是嵌套SQL的核心概念,它是一个独立的查询语句,...

    SQL查询语句大全

    #### 九、Union操作和子查询语句 - **Union操作**:`UNION`关键字用于合并两个或多个`SELECT`语句的结果集,并且去重。 - **子查询**:一个嵌套在另一个`SELECT`语句中的`SELECT`语句称为子查询。子查询可以在`WHERE...

    SQL实现两张无关联表的数据列合并在一张结果集中

    具体来说,它从`tab1`表中选择所有的`username`字段,并且对于每一行,使用子查询来获取对应行号(`rowIndex`)的`tab2`表中的`username`字段。最终结果集中的每一行包含了来自`tab1`的用户名以及一个额外的`name2`列...

    通过分析SQL语句的执行计划优化SQL(总结)

    基于执行计划的分析,我们可能需要重写SQL语句,如避免全表扫描、减少不必要的子查询、使用连接替代子查询等。 通过以上分析,我们可以看出,理解和优化SQL语句的执行计划是数据库管理员和开发人员的重要技能。这...

    SQL高级查询语句实例

    首先,内联查询(也称为子查询)是指在一个查询语句中嵌套另一个查询,用以获取所需的数据。内联查询可以在SELECT、FROM或WHERE子句中使用。例如,如果你有一个订单表和一个客户表,你可以先查询出最近下单的客户ID...

    PB 从SQL语句获取数据存储(MySQL)

    标题 "PB 从SQL语句获取数据存储(MySQL)" 指的是使用PowerBuilder (PB) 开发工具,通过SQL语句从MySQL数据库中检索和处理数据存储的过程。在这个Demo中,PB11.5 版本被用作开发环境,而MySQL作为后台数据库系统。...

    sql 查询 语句 常用sql语句(有表结构)

    将子查询的结果作为连接条件,实现更复杂的数据关联。 例如,查询计划与物料清单中金额最小的项目: ```sql SELECT a.*, pact FROM dbo.wu_bom a INNER JOIN dbo.wu_plan b ON a.id = b.id WHERE amount = (SELECT ...

    SQL语句基础教程。SQL语句基础教程

    SQL(Structured Query Language)...通过这个“SQL语句基础教程”,初学者将逐步了解和掌握SQL的基本概念和操作,为数据库管理和分析奠定坚实的基础。在实际应用中,不断练习和探索,将使你对SQL的运用更加得心应手。

    JsonSQL:用SQL语句解析JSON文件

    JsonSQL项目应运而生,它允许用户通过SQL语句来操作和解析JSON文件,提供了更为熟悉的查询方式。 JsonSQL的基本概念是将JSON文档视为一种虚拟的表格,其中JSON对象的键被视为列名,JSON数组中的元素被视为行。这样...

    SQL语句的多表查询

    ### SQL语句中的多表查询 在数据库管理与开发中,单个数据表往往难以满足复杂的数据需求。因此,多表查询成为了一种常见的技术手段,尤其在ODBC(Open Database Connectivity,开放数据库互连)编程中尤为重要。...

    数据库SQL语句讲解.pptx

    数据库SQL语句是数据库管理系统中用于操作数据的核心语言,它包括了多种用于创建、更新、查询和删除数据的语句。本讲座主要涵盖了SQL语句的基础知识,特别是针对SQL Server的相关内容。 首先,数据完整性是数据库...

    SQL语句的分析与查询优化策略.pdf

    在数据库应用系统中,SQL(Structured Query Language)语句的高效执行...总之,理解SQL语句的处理过程和优化策略,结合实际数据库环境进行调整,能够显著提升数据库应用系统的性能,满足用户对高效数据处理的需求。

    常用复杂sql语句

    根据提供的文件信息,我们可以归纳出几个重要的SQL...综上所述,这些复杂的SQL语句涉及到了子查询、分组查询、多表关联等多个高级主题。掌握这些技巧不仅有助于提高SQL查询的能力,还能在实际工作中解决更复杂的问题。

Global site tag (gtag.js) - Google Analytics