数据查询

default

条件查询

CASE

有如下数据:(为了看得更清楚,我并没有使用国家代码,而是直接用国家名作为 Primary Key)

国家(country)

人口(population)

中国

600

美国

100

加拿大

100

英国

200

法国

300

日本

250

德国

200

墨西哥

50

印度

250

根据这个国家人口数据,统计亚洲和北美洲的人口数量。应该得到下面这个结果。

人口

亚洲

1100

北美洲

250

其他

700

想要解决这个问题,你会怎么做?生成一个带有洲 Code 的 View,是一个解决方法,但是这样很难动态的改变统计的方式。 如果使用 Case 函数,SQL 代码如下:

SELECT SUM(population),
CASE country
WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大' THEN '北美洲'
WHEN '墨西哥' THEN '北美洲'
ELSE '其他' END
FROM Table_A
GROUP BY CASE country
WHEN '中国' THEN '亚洲'
WHEN '印度' THEN '亚洲'
WHEN '日本' THEN '亚洲'
WHEN '美国' THEN '北美洲'
WHEN '加拿大' THEN '北美洲'
WHEN '墨西哥' THEN '北美洲'
ELSE '其他' END;

同样的,我们也可以用这个方法来判断工资的等级,并统计每一等级的人数。SQL 代码如下;

SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600 THEN '2'
WHEN salary > 600 AND salary <= 800 THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;

还可以用一个 SQL 语句完成不同条件的分组合并: 有如下数据

国家(country)

性别(sex)

人口(population)

中国

1

340

中国

2

260

美国

1

45

美国

2

55

加拿大

1

51

加拿大

2

49

英国

1

40

英国

2

60

按照国家和性别进行分组,得出结果如下

国家

中国

340

260

美国

45

55

加拿大

51

49

英国

40

60

普通情况下,用 UNION 也可以实现用一条语句进行查询。但是那样增加消耗(两个 Select 部分),而且 SQL 语句会比较长。下面是一个是用 Case 函数来完成这个功能的例子

SELECT country,
SUM( CASE WHEN sex = '1' THEN
population ELSE 0 END), --男性人口
SUM( CASE WHEN sex = '2' THEN
population ELSE 0 END) --女性人口
FROM Table_A
GROUP BY country;

这样我们使用 Select,完成对二维表的输出形式,充分显示了 Case 函数的强大。

联接查询

表联接最常见的即是出现在查询模型中,但是实际的用法绝不会局限在查询模型中。较常见的联接查询包括了以下几种类型:Inner Join / Outer Join / Full Join / Cross Join。

统计查询

子查询

子查询,就是在一个查询中嵌套了其他若干查询,即在一个 SELECT 查询语句的 FROM 或 WHERE 字句中包含另一个 SELECT 查询语句,在这种嵌套的查询语句中,外层的 SELECT 查询语句称为主查询,WHERE 或 FROM 中的查询语句称为子查询,也叫嵌套查询。通过子查询可以实现多表查询,子查询经常出现在 WHERE 或 FROM 字句中。

WHERE 子句中的子查询:该位置处的子查询一般返回单行单列,多行单列,单行多列数据。就是返回能够作为 WHERE 子句查询条件的值。 FROM 子句中的子查询:该位置处的子查询一般返回多行多列数据,相当于是返回一张临时表,符合 FROM 子句后面是表的规则,就是通过这种方式来实现多表查询的。

子查询本质上是嵌套进其他 SELECT,UPDATE,INSERT,DELETE 语句的一个被限制的 SELECT 语句。在子查询中,只有 SELECT, FROM, WHERE, GROUP BY, HAVING 等子句可以使用。

子查询作为数据源使用

当子查询在外部查询的FROM子句之后使用时,子查询被当作一个数据源使用,即使这时子查询只返回一个单一值(Scalar)或是一列值(Column),在这里依然可以看作一个特殊的数据源,即一个二维数据表(Table).作为数据源使用的子查询很像一个View(视图),只是这个子查询只是临时存在,并不包含在数据库中。比如:

SELECT P.ProductID, P.Name, P.ProductNumber, M.Name AS ProductModelName
FROM Production.Product AS P INNER JOIN
(SELECT Name, ProductModelID
FROM Production.ProductModel) AS M
ON P.ProductModelID = M.ProductModelID
上述子查询语句将ProductModel表中的**子集**M,作为数据源(表)和Product表进行内连接。结果如下:
2
作为**数据源**使用也是子查询最简单的应用。当然,当子查询作为数据源使用时,也分为**相关子查询**和**无关子查询。

子查询作为选择条件使用

作为选择条件的子查询也是子查询相对最复杂的应用。作为选择条件的子查询是那些只返回**一列(Column)**的子查询,如果作为选择条件使用,即使只返回**单个值**,也可以看作是只有**一行**的**一列.**比如: 在AdventureWorks中:我想取得总共请病假天数大于68小时的员工:
SELECT [FirstName]
,[MiddleName]
,[LastName]
FROM [AdventureWorks].[Person].[Contact]
WHERE ContactID IN
(SELECT EmployeeID
FROM [AdventureWorks].[HumanResources].[Employee]
WHERE SickLeaveHours>68)

结果如下:

3
上面的查询中,在IN关键字后面的子查询返回一列值作为**外部查询**的**选择条件**使用.同样的,与IN关键字的逻辑取反的NOT IN关键字,这里就不再阐述了 但是要强调的是,不要用IN和NOT IN关键字,这会引起很多潜在的问题,[这篇文章](http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in)对这个问题有着很好的阐述:。这篇文章的观点是永远不要再用IN和NOT IN关键字,我的观点是存在即合理,我认为只有在IN里面是固定值的时候才可以用IN和NOT IN,比如:
SELECT [FirstName]
,[MiddleName]
,[LastName]
FROM [AdventureWorks].[Person].[Contact]
WHERE ContactID IN (25,33)
只有在上面这种情况下,使用IN和NOT IN关键字才是安全的,其他情况下,最好使用EXISTS,NOT EXISTS,JOIN关键字来进行替代. 除了IN之外,用于选择条件的关键字还有**ANY**和**ALL**,这两个关键字和其字面意思一样. 和"<",">",”="连接使用,比如上面用IN的那个子查询:我想取得总共请病假天数大于68小时的员工。 用ANY关键字进行等效的查询为:
SELECT [FirstName]
,[MiddleName]
,[LastName]
FROM [AdventureWorks].[Person].[Contact]
WHERE ContactID =ANY
(SELECT EmployeeID
FROM [AdventureWorks].[HumanResources].[Employee]
WHERE SickLeaveHours>68)

子查询作为计算列使用

当**子查询**作为**计算列**使用时,只返回单个值(Scalar) 。用在SELECT语句之后,作为计算列使用。同样分为**相关子查询**和**无关子查询**。**相关子查询**的例子比如:我想取得每件产品的名称和总共的销量:
SELECT [Name],
(SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderDetail S
WHERE S.ProductID=P.ProductID) AS SalesAmount
FROM [AdventureWorks].[Production].[Product] P

Todos