【T-SQL基础】01.单表查询-几道sql查询题

概述：

本系列【T-SQL基础】主要是针对T-SQL基础的总结。

【T-SQL基础】01.单表查询-几道sql查询题

【T-SQL基础】02.联接查询

【T-SQL基础】03.子查询

【T-SQL基础】04.表表达式-上篇

【T-SQL基础】04.表表达式-下篇

【T-SQL基础】05.集合运算

【T-SQL基础】06.透视、逆透视、分组集

【T-SQL基础】07.数据修改

【T-SQL基础】08.事务和并发

【T-SQL基础】09.可编程对象

 ----------------------------------------------------------

【T-SQL进阶】01.好用的SQL TVP~~独家赠送[增-删-改-查]的例子

 ----------------------------------------------------------

【T-SQL性能调优】01.TempDB的使用和性能问题

【T-SQL性能调优】02.Transaction Log的使用和性能问题

【T-SQL性能调优】03.执行计划

【T-SQL性能调优】04.死锁分析

持续更新......欢迎关注我！

 

本篇主要总结了常见的对单表查询的SQL查询题目。

首先我们必须了解SQL查询的各字句在逻辑上按以下顺序进行处理：

1.FROM

2.WHERE

3.Group BY

4.HAVING

5.SELECT

6.ORDER BY

 

在做下面的题目之前，我们可以先把环境准备好，以下的SQL脚本可以帮助大家创建数据库，创建表，插入数据。

下载脚本文件：TSQLFundamentals2008.zip

 

题目：

1.返回2007年6月生成的订单：

(30 row(s) affected)

 

本题考察的是过滤日期范围

参考答案：

方案一：

 如果要过滤日期范围（比如，整年或正月），比较自然的方法就是使用YEAR和MONTH之类的函数。

1 2 3 4

SELECT  orderid, orderdate, custid, empid FROM  Sales.Orders WHERE  YEAR (orderdate)= 2007 AND  MONTH (orderdate)=6;

 

不过，应该小心的一点是：在大多数情况下，当对过滤条件中的列应用了一定的处理后，就不能以有效的方式来使用索引了。为了潜在地有效利用索引，就需要对为此进行调整，以便对过滤条件中的列不进行处理。

使用一个范围过滤条件：

orderdate >= '20070601'
AND orderdate < '20070701'

方案二：

1 2 3 4

SELECT  orderid, orderdate, custid, empid FROM  Sales.Orders WHERE  orderdate >=  '20070601'    AND  orderdate <  '20070701' ;

2.返回每个月的最后一天生成的订单

涉及的表：Sales.Orders表

 

 

本题主要考察DATEADD和DATEDIFF的用法

DATEADD:

将指定 number 时间间隔（有符号整数）与指定 date 的指定 datepart 相加后，返回该 date。

1.语法

DATEADD (datepart , number , date)

2.参数：

  datepart

datepart	缩写
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

  number

是一个表达式，可以解析为与 date 的 datepart 相加的 int。用户定义的变量是有效的。

如果您指定一个带小数的值，则将小数截去且不进行舍入。

   date

是一个表达式，可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。如果表达式是字符串文字，则它必须解析为一个 datetime值。为避免不确定性，请使用四位数年份。有关两位数年份的信息，请参阅 two digit year cutoff 选项。

3.返回值

datepart 参数

dayofyear、day 和 weekday 返回相同的值。

每个 datepart 及其缩写都返回相同的值。

如果 datepart 为 month 且 date 月份比返回月份的天数多，因而 date 中的日在返回月份中不存在，则返回返回月份的最后一天。例如，9 月份有 30 天；因此，下面两个语句返回 2006-09-30 00:00:00.000：

SELECT DATEADD(month, 1, '2006-08-30')

SELECT DATEADD(month, 1, '2006-08-31')

number 参数

number 参数不能超出 int 的范围。在下面的语句中，number 的参数超出 int 范围 1。将返回如下错误消息：“将表达式转换为数据类型 int 时出现算术溢出错误。”

SELECT DATEADD(year,2147483648, '2006-07-31'); SELECT DATEADD(year,-2147483649, '2006-07-31');

date 参数

date 参数不能增加至其数据范围之外的值。在下面的语句中，与 date 值相加的 number 值超出了 date 数据类型的范围。将返回如下错误消息：“将值添加到 'datetime' 列导致溢出。”

SELECT DATEADD(year,2147483647, '2006-07-31'); SELECT DATEADD(year,-2147483647, '2006-07-31');

DATEDIFF:

返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数（带符号的整数）。

语法：

DATEDIFF ( datepart , startdate , enddate )

参数

    datepart

是指定所跨边界类型的 startdate 和 enddate 的一部分。下表列出了所有有效的 datepart 参数。用户定义的变量等效项是无效的。

datepart	缩写
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

  

startdate

是一个表达式，可以解析为 time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 值。date 可以是表达式、列表达式、用户定义的变量或字符串文字。从 enddate 减去 startdate。

为避免不确定性，请使用四位数年份。有关两位数年份的信息，请参阅two digit year cutoff 选项。

   enddate

请参阅 startdate。

返回类型

int

返回值

每个 datepart 及其缩写都返回相同的值。

 

如果返回值超出 int 的范围（-2,147,483,648 到 +2,147,483,647），则会返回一个错误。对于 millisecond，startdate 与 enddate 之间的最大差值为 24 天 20 小时 31 分钟 23.647 秒。对于 second，最大差值为 68 年。

如果为 startdate 和 enddate 都只指定了时间值，并且 datepart 不是时间 datepart，则会返回 0。

在计算返回值时不使用 startdate 或 endate 的时区偏移量部分。

由于 smalldatetime 仅精确到分钟，因此将 smalldatetime 值用作 startdate 或 enddate 时，返回值中的秒和毫秒将始终设置为 0。

如果只为某个日期数据类型的变量指定时间值，则所缺日期部分的值将设置为默认值：1900-01-01。如果只为某个时间或日期数据类型的变量指定日期值，则所缺时间部分的值将设置为默认值：00:00:00。如果 startdate 和 enddate 中有一个只含时间部分，另一个只含日期部分，则所缺时间和日期部分将设置为各自的默认值。

如果 startdate 和 enddate 属于不同的日期数据类型，并且其中一个的时间部分或秒小数部分精度比另一个高，则另一个的所缺部分将设置为 0。

参考答案：

方案一：

1 2 3

SELECT  *  FROM  Sales.Orders   WHERE  MONTH (DATEADD( DAY ,1,orderdate)) <>  MONTH (orderdate)

方案二：

1 2 3

SELECT  orderid, orderdate, custid, empid FROM  Sales.Orders WHERE  orderdate = DATEADD( month , DATEDIFF( month ,  '20051231' , orderdate),  '20051231' );

得到每月的最后一天的日期:

1	DATEADD( month , DATEDIFF( month ,  '20051231' , orderdate),  '20051231' )

3.返回姓氏(last name)中包含字母'a'两次获更多次的雇员

涉及的表：HR.Employees表

本题主要考察%(百分号)通配符的用法

百分号代表任意长度的字符串，包括空字符串。

 

参考答案：

1 2 3

SELECT  empid, firstname, lastname FROM  HR.Employees WHERE  lastname  LIKE  '%a%a%' ;

4.返回总价格（数量*单价）大于10000的所有订单，并按总价格排序

涉及的表：Sales.OrderDetails表

 

参考答案：

1 2 3 4 5 6

SELECT  orderid, SUM (unitprice*qty)  AS  totalValue    FROM  Sales.OrderDetails    GROUP  BY  orderid    HAVING  SUM (unitprice*qty)>10000    ORDER  BY  totalValue  desc ;

5.返回2007年平均运费最高的发货国家

涉及的表：Sales.Orders表

参考答案：

1 2 3 4 5

SELECT  TOP (3) shipcountry,  AVG (freight)  AS  avgfreight FROM  Sales.Orders WHERE  orderdate >=  '20070101'  AND  orderdate <  '20080101' GROUP  BY  shipcountry ORDER  BY  avgfreight  DESC ;

6.为每个顾客单独根据订单日期的顺序（用order ID作为附加属性）来计算其订单的行号。

涉及的表：Sales.Orders表

(830 row(s) affected)

参考答案：

1 2 3 4 5 6 7

SELECT   custid ,          orderdate ,          orderid ,          ROW_NUMBER() OVER ( PARTITION  BY  custid  ORDER  BY  orderdate, orderid )  AS  rownum FROM     Sales.Orders ORDER  BY  custid ,          rownum;

7.构造一个SELECT语句，让它根据每个雇员的友好称谓，而返回其性别

涉及的表：HR.Employees表

 

1.搜索格式的CASE表达式

1 2 3 4 5 6 7 8 9

SELECT   empid ,          firstname ,          lastname ,          titleofcourtesy ,          CASE  WHEN  titleofcourtesy  IN  (  'Ms.' ,  'Mrs.'  )  THEN  'Female'               WHEN  titleofcourtesy =  'Mr.'  THEN  'Male'               ELSE  'Unknown'          END  AS  gender FROM     HR.Employees

2.简单的CASE表达式格式

1 2 3 4 5 6 7 8 9 10 11

SELECT   empid ,          firstname ,          lastname ,          titleofcourtesy ,          CASE  titleofcourtesy            WHEN  'Ms.'  THEN  'Female'            WHEN  'Mrs.'  THEN  'Female'            WHEN  'Mr.'  THEN  'Male'            ELSE  'Unknown'          END  AS  gender; FROM     HR.Employees

8.返回每个客户的客户ID和所在区域。对输出中的行按区域排序，NULL值排在最后

注意，T-SQL中NULL值的默认行为是把NULL值排在前面（所有非NULL值之前）。

涉及的表：Sales.Customers表。

参考答案：

1 2 3 4 5 6 7

SELECT   custid ,          region FROM     Sales.Customers ORDER  BY  CASE  WHEN  region  IS  NULL  THEN  1                ELSE  0           END  ,          region;

 

参考资料：

《SQL2008技术内幕：T-SQL语言基础》

 

原文出自：【T-SQL基础】01.单表查询-几道sql查询题

作　　者： Jackson0714 
出　　处：http://www.cnblogs.com/jackson0714/ 
关于作者：专注于微软平台的项目开发。如有问题或建议，请多多赐教！ 
版权声明：本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接。 
特此声明：所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误，共同进步。或者直接私信我 
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是作者坚持原创和持续写作的最大动力！