简单记录几个有用的sql查询

时间：2010-10-17 来源：Jeff Wong

一、限制返回的行数

1、Sql Server

SELECT TOP 10 Id,FirstName, LastName
FROM Person

2、Oracle

SELECT Id,FirstName, LastName
FROM Person
WHERE ROWNUM<=10

3、DB2

SELECT Id,FirstName, LastName
FROM Person FETCH FIRST 10 ROWS ONLY

4、MySql

SELECT Id,FirstName, LastName
FROM Person LIMIT 5

5、PostgreSQL

SELECT Id,FirstName, LastName
FROM Person LIMIT 5

小结：查询语句都很基础，MySql和PostgreSQL的写法是相同的，可以看到各个DBMS的可读性都不错，用户（开发者）使用体验很重要。

二、按特定格式查询日期（Sql Server版）

实际开发中通常都直接查询结果，然后用编程语言进行日期格式输出（如C#、Java等），但是数据库同样也提供了这种转换处理能力，下面整理一下Sql Server的常用时间格式查询和对应输出结果：

SELECT CONVERT(VARCHAR(100), GETDATE(), 0)-- 10 17 2010  4:51PM
SELECT CONVERT(VARCHAR(100), GETDATE(), 1)-- 10/17/10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 2)-- 10.10.17 
SELECT CONVERT(VARCHAR(100), GETDATE(), 3)-- 17/10/10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 4)-- 17.10.10
SELECT CONVERT(VARCHAR(100), GETDATE(), 5)-- 17-10-10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 6)-- 17 10 10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 7)-- 10 17, 10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 8)-- 16:52:13
SELECT CONVERT(VARCHAR(100), GETDATE(), 9)-- 10 17 2010  4:52:13:960PM
SELECT CONVERT(VARCHAR(100), GETDATE(), 10)-- 10-17-10 
SELECT CONVERT(VARCHAR(100), GETDATE(), 11)-- 10/10/17
SELECT CONVERT(VARCHAR(100), GETDATE(), 12)-- 101017
SELECT CONVERT(VARCHAR(100), GETDATE(), 13)-- 17 10 2010 16:53:39:403 
SELECT CONVERT(VARCHAR(100), GETDATE(), 14)-- 16:53:39:403 
SELECT CONVERT(VARCHAR(100), GETDATE(), 20)-- 2010-10-17 16:53:39
SELECT CONVERT(VARCHAR(100), GETDATE(), 21)-- 2010-10-17 16:54:55.100 
SELECT CONVERT(VARCHAR(100), GETDATE(), 22)-- 10/17/10  4:54:55 PM 
SELECT CONVERT(VARCHAR(100), GETDATE(), 24)-- 16:54:55 
SELECT CONVERT(VARCHAR(100), GETDATE(), 25)-- 2010-10-17 16:54:55.100 
SELECT CONVERT(VARCHAR(100), GETDATE(), 100)-- 10 17 2010  4:54PM 
SELECT CONVERT(VARCHAR(100), GETDATE(), 101)-- 10/17/2010
SELECT CONVERT(VARCHAR(100), GETDATE(), 102)-- 2010.10.17 
SELECT CONVERT(VARCHAR(100), GETDATE(), 103)-- 17/10/2010 
SELECT CONVERT(VARCHAR(100), GETDATE(), 104)-- 17.10.2010 
SELECT CONVERT(VARCHAR(100), GETDATE(), 105)-- 17-10-2010 
SELECT CONVERT(VARCHAR(100), GETDATE(), 106)-- 17 10 2010 
SELECT CONVERT(VARCHAR(100), GETDATE(), 107)-- 10 17, 2010 
SELECT CONVERT(VARCHAR(100), GETDATE(), 108)-- 16:56:36 
SELECT CONVERT(VARCHAR(100), GETDATE(), 109)-- 10 17 2010  4:56:36:370PM 
SELECT CONVERT(VARCHAR(100), GETDATE(), 110)-- 10-17-2010
SELECT CONVERT(VARCHAR(100), GETDATE(), 111)-- 2010/10/17 
SELECT CONVERT(VARCHAR(100), GETDATE(), 112)-- 20101017 
SELECT CONVERT(VARCHAR(100), GETDATE(), 113)-- 17 10 2010 16:57:51:713 
SELECT CONVERT(VARCHAR(100), GETDATE(), 114)-- 16:59:19:640
SELECT CONVERT(VARCHAR(100), GETDATE(), 120)-- 2010-10-17 16:59:19
SELECT CONVERT(VARCHAR(100), GETDATE(), 121)-- 2010-10-17 16:59:19.640
SELECT CONVERT(VARCHAR(100), GETDATE(), 126)-- 2010-10-17T16:59:19.640
SELECT CONVERT(VARCHAR(100), GETDATE(), 130)-- 10 ?? ?????? 1431  4:59:19:640PM

小结：ms真够贴心的，还有哪种格式它没有帮我们实现呢？

三、从表中随机返回n条记录

1、Sql Server

SELECT TOP 10 Id, FirstName, LastName
FROM Person (NOLOCK)
ORDER BY NEWID()

2、Oracle

SELECT Id, FirstName, LastName FROM(
        SELECT Id, FirstName, LastName FROM Person
        ORDER BY DBMS_RANDOM.VALUE()
)
WHERE ROWNUM<=10

3、DB2

SELECT Id, FirstName, LastName FROM Person
ORDER BY RAND() FETCH  FIRST 10 ROWS ONLY

4、MySql

SELECT Id, FirstName, LastName FROM Person
ORDER BY RAND() LIMIT 10

5、PostgreSQL

SELECT Id, FirstName, LastName FROM Person
ORDER BY RANDOM() LIMIT 5

小结：

（1）、对比各数据库产品（DBMS）的sql查询书写方式，可以发现它们的相似之处都需要使用ORDER BY 子句对行进行随机排序，而随机函数都使用各自的内置函数。好玩的地方在于，虽然各个DBMS的随机函数有相似或相同的地方，但是没有一个查询是相同的。

（2）、Oracle的查询方式相比其他稍显啰嗦，可读性稍差，但是可以看出它的思路，理解它的实现原理，对开发中是很有益的。

四、将空值转换为实际值

通常情况下，我们通过case when可以实现空值到实际值的转换：

   SELECT TOP 10
        Id,
        FirstName,
        CASE 
        WHEN LastName IS NULL THEN ''
        ELSE LastName
        END AS LastName
    FROM
        Person (NOLOCK)

但是，更简洁的写法是使用COALESCE函数：

SELECT TOP 10
        Id,
        FirstName,
        COALESCE(LastName,'') AS LastName
FROM
        Person (NOLOCK)

最后，在开发中写出简洁高效的sql语句一直是我孜孜追求的目标，每次碰到动辄上百乃至上千行的sql语句或者存储过程需要维护就头疼不已，尤其是那些业务逻辑比较复杂的，不知道大家在开发中有没有过这种经历。如何规避复杂的sql语句和存储过程开发易维护的系统，不知道您有什么看法或者好的解决方案？欢迎讨论。