sql关联查询和连接查询

往往经常有这样的需求,我需要在查询的结果中添加一列类似于Identity的数字,虽然在Client编程中并不难实现,但是有时我想留用现有的Class,不希望在Client
side做额外的coding,那么就只有在Sql里面想办法了
首先介绍一种用一条SQL语句完成的办法,原理是在结果中查询大于等于该纪录的纪录条数,就可以得到它的Rank了
Example:
USE pubs
SELECT COUNT(*) AS Rank, a1.au_lname, a1.au_fname
FROM authors a1, authors a2
WHERE a1.au_lname a1.au_fname >= a2.au_lname a2.au_fname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY Rank
不过呢,这种方法有它的局限性,第一是性能不好,第二是如果存在相同的纪录,那么Rank就会出现并列的情况,比如出现两个2,但是没有3了
有没有别的方法呢?当然有的,SQL提供了一个IDENTITY
Function,可以得到标识列的值,不过可惜的很的是,这个函数只能用于SELECT
INTO语句,所以我们只好引入一个临时表了
Example:
USE pubs
SELECT IDENTITY(INT, 1, 1) AS Rank,au_lname,au_fname
INTO #tmp
FROM authors
SELECT * FROM #tmp
DROP TABLE #tmp
这种方法的性能和适用性都比第一种方法要强,不过缺点是必须通过几条SQL语句才能完成。
所以如果可能的话,一般还是建议在客户端完成这一操作
Thanks for your read and any advise.

1 取得表中第6到第10条记录的值

  1.1 第一种方法,使用minus语句

  假设ddl语句如下:

  CREATE TABLE T(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)

  那么第一种方法就是取出前5条,再取出前10条,然后采用集合运算的方法把前10条减去前5条就OK了,SQL语句如下

以下是引用片段:
  SELECT * FROM T WHERE ROWNUM <= 10
  MINUS
  SELECT * FROM T WHERE ROWNUM <= 5;

  1.2 另外一种方法,采用子查询

  子查询的这种方法相对比较复杂一点,不过性能要比刚才的集合相减要好一些。这种方法首先在子查询中得到前10条数据,顺路也取得前10条数据的rownum,然后再一次查询的时候取得刚才查询的rownum大于5的那些数据。SQL语句如下

以下是引用片段:
  SELECT ID, VALUE FROM
  (SELECT ID, VALUE, ROWNUM R FROM T WHERE R <= 10)
  WHERE
  R > 5;

  通过上面的语句,就得到了6到第10条数据了。

  2 利用外连接替代not in语句

  in语句还有not
in语句的效率是非常的差的,因为数据库在遇到这两种语句的时候是要把数据进行一条一条的比对,如果in或者not
in两侧的数据量在上万条的时候,进行比对的次数就是上亿次,很可能一个简单的sql语句就要执行半个小时以上。这种效率客户是肯定不能够接受的。那我们可以考虑两种方法进行替代,第一种就是采用exist语句和not
exist语句,这种大家应该比较熟悉了。另外一种就是巧用外关联语句,这种方法可能大家不是很熟悉,我来稍微说一下。假设数据表的建表DDL语句为

  CREATE TABLE T1(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)

  而in或者not in的表的建表DDL语句为

  CREATE TABLE T2(VALUE INT)

  Oracle中外关联采用的是(+)符号表示外关联,也就是说标识了(+)符号的部分在找不到对应的值的时候为NULL。下面是替代in语句的时候的SQL语句

以下是引用片段:
  SELECT T1.ID, T1.VALUE
  FROM T1, T2
  WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NOT NULL;

  而类似的。替代not in语句的时候的SQL语句则为

以下是引用片段:
  SELECT T1.ID, T1.VALUE
  FROM T1, T2
  WHERE T1.VALUE = T2.VALUE(+)
  AND T2.VALUE IS NULL;

  大家可以试验一下,在数据量多的时候,采用外关联比用in或者not
in的执行效率要高很多很多。

数据库的左连接,右连接问题

前一阶段经历了几次程序员的面试,发现数据库这个部分占了很大的比重。而左连接,右连接又是很多人问的重点,当初我就是不太明白怎么一回事,所以吃了几次的亏。今天把专门作了一次关于左连接和右连接的文章,巩固一下知识:
要点:left join,right join,inner join
首先有如下两个表:
Student:

ID(int)
 Name(nvarchar)
 
1
 a
 
2
 b
 
3
 c
 
4
 d
 
5
 e
 
6
 f
 

Quiz:

ID(int)
 score(int)
 
1
 60
 
2
 70
 
4
 80
 
6
 90
 
8
 100
 
9
 30
 

内连接:(inner join)包括连接表的匹配行
select Student.Name,Quiz.score from Quiz inner join Student on
Student.ID=Quiz.ID

Name
 score
 
a
 60
 
b
 70
 
d
 80
 
f
 90
 

左连接:(left join)包括连接表匹配行以及左连接表的所有行
select Student.Name,Quiz.score from Student left join Quiz on
Student.ID=Quiz.ID

Name
 score
 
a
 60
 
b
 70
 
c
 null
 
d
 80
 
e
 null
 
f
 90
 

右连接:(right join)结果包括连接表的匹配行以及右连接表的所有行
select Student.Name,Quiz.score from Student right join Quiz on
Student.ID=Quiz.ID

Name
 score
 
a
 60
 
b
 70
 
d
 80
 
f
 90
 
null
 100
 
null
 30
 

当然,也可以看出左连接也可以写成右连接的形式:
select Student.Name,Quiz.score from Student right join Quiz on
Student.ID=Quiz.ID等价于
select Student.Name,Quiz.score from Quiz left join Student on
Student.ID=Quiz.ID

 

 

使用外联接

仅当至少有一个同属于两表的行符合联接条件时,内联接才返回行。内联接消除与另一个表中的任何行不匹配的行。而外联接会返回
FROM 子句中提到的至少一个表或视图的所有行,只要这些行符合任何 WHERE 或
HAVING
搜索条件。将检索通过左向外联接引用的左表的所有行,以及通过右向外联接引用的右表的所有行。完整外部联接中两个表的所有行都将返回。

Microsoft® SQL Server™ 2000 对在 FROM 子句中指定的外联接使用以下 SQL-92
关键字:

·                         LEFT OUTER JOIN 或 LEFT JOIN

·                         RIGHT OUTER JOIN 或 RIGHT JOIN

·                         FULL OUTER JOIN 或 FULL JOIN

SQL Server 支持 SQL-92 外联接语法,以及在 WHERE 子句中使用 *= 和 =*
运算符指定外联接的旧式语法。由于 SQL-92 语法不容易产生歧义,而旧式
Transact-SQL 外联接有时会产生歧义,因此建议使用 SQL-92 语法。

使用左向外联接

假设在 city 列上联接 authors 表和 publishers
表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和
Cheryl Carson)。

若要在结果中包括所有的作者,而不管出版商是否住在同一个城市,请使用
SQL-92 左向外联接。下面是 Transact-SQL 左向外联接的查询和结果:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors a LEFT OUTER JOIN publishers p

   ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

下面是结果集:

au_fname             au_lname                       pub_name         


Reginald             Blotchet-Halls                 NULL

Michel               DeFrance                       NULL

Innes                del Castillo                   NULL

Ann                  Dull                           NULL

Marjorie             Green                          NULL

Morningstar          Greene                         NULL

Burt                 Gringlesby                     NULL

Sheryl               Hunter                         NULL

Livia                Karsen                         NULL

Charlene             Locksley                       NULL

Stearns              MacFeather                     NULL

Heather              McBadden                       NULL

Michael              O’Leary                        NULL

Sylvia               Panteley                       NULL

Albert               Ringer                         NULL

Anne                 Ringer                         NULL

Meander              Smith                          NULL

Dean                 Straight                       NULL

Dirk                 Stringer                       NULL

Johnson              White                          NULL

Akiko                Yokomoto                       NULL

Abraham              Bennet                         Algodata Infosystems

Cheryl               Carson                         Algodata Infosystems

 

(23 row(s) affected)

不管是否与 publishers 表中的 city 列匹配,LEFT OUTER JOIN
均会在结果中包含 authors
表的所有行。注意:结果中所列的大多数作者都没有相匹配的数据,因此,这些行的
pub_name 列包含空值。

使用右向外联接

假设在 city 列上联接 authors 表和 publishers
表。结果只显示在出版商所在城市居住的作者(本例中为 Abraham Bennet 和
Cheryl Carson)。SQL-92 右向外联接运算符 RIGHT OUTER JOIN
指明:不管第一个表中是否有匹配的数据,结果将包含第二个表中的所有行。

若要在结果中包括所有的出版商,而不管城市中是否还有出版商居住,请使用
SQL-92 右向外联接。下面是 Transact-SQL 右向外联接的查询和结果:

USE pubs

SELECT a.au_fname, a.au_lname, p.pub_name

FROM authors AS a RIGHT OUTER JOIN publishers AS p

   ON a.city = p.city

ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC

下面是结果集:

au_fname             au_lname                 pub_name            


Abraham              Bennet                   Algodata Infosystems

相关文章