SQL语法坑点总结（持续更新至6.29）

Hambert Leung

理性诗人感性攻城狮

直入正题，本帖目的是将日常工作以及项目实践中遇到的SQL疑难杂症做汇总，由于工作环境目前主要是SQL SERVER，兼顾ORACLE、MYSQL，每条知识前都会标注数据库环境是否通用，重点会以加粗标记，供大家参考：

Q ：（通用）行转列时提示：“)”附近有语法错误。
A ：结尾一定要加别名：PIVOT() AS <透视表的别名>；另外，属性列的值无须加引号

SELECT * FROM (SELECT FDATE, FTYPE, COUNT(1) CNT FROM UBS GROUP BY FDATE, FTYPE)  ORIG
PIVOT(
       SUM(CNT) FOR FTYPE IN (pv, cart, fav, buy)
) AS T

Q ：（通用）多个临时表的连用（注意逗号）
A ：WITH T1 AS () , T2 AS ()

Q ：（ SQL SERVER ）子查询加入排序报错：除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效
A ：只要我们在嵌套子查询视图里面加入 TOP 100 PERCENT 即可；因为针对一个表的SELECT其实并不是返回一个表，而是一个游标

--获取复购用户的每次购买日期
SELECT TOP 100 PERCENT ID=IDENTITY(INT), UBS.FID, FDATE 
INTO USER_BUY_DATE
FROM UBS 
JOIN USER_REBUY UR ON UBS.FID = UR.FID
WHERE FTYPE = 'buy'
GROUP BY UBS.FID, FDATE 
ORDER BY UBS.FID ASC, FDATE ASC

Q ：（ SQL SERVER ）SELECT INTO 到临时表里的顺序与查询顺序不一致
A ：查询的时候加入自增长主键即可；SELECT ID = IDENTITY(INT) , * INTO TEMP_TB FROM ORIG_TB；注意，ID = IDENTITY(INT) 须与 INTO 连用，案例同上

Q ：（通用）GROUP BY / ORDER BY 的缩写写法
A ：当查询的字段很长时，虽然不能直接引用别名（执行顺序的原因，SELECT排在最后）；但可以通过指定字段位置（从1开始）来选中对应字段进行排序

# SELECT的计算字段，由于做了大量转换，不方便引用，因此ORDER BY直接指定位置（从1开始，本例指定的就是第三个字段RATE）
SELECT ID=IDENTITY(INT),
              FTYPE,
              COUNT(1) CNT,
              CONVERT(VARCHAR, (CONVERT(FLOAT, ROUND(COUNT(1) * 100.0 / (SELECT  COUNT(1) FROM UBS WHERE FTYPE = 'pv'), 2)))) + '%' RATE
INTO TYPE_CNT_RATE
FROM UBS
GROUP BY FTYPE ORDER BY 3 DESC

Q ：（通用）行偏移量的应用（例如获取第51-75条记录）
A ：语法参考如下：

（ MySQL ）查询句末加入：OFFSET 50 LIMIT 25；
（ SQL SERVER 2012+ ）必须配合ORDER BY，随后加入OFFSET 50 ROWS FETCH NEXT 25 ROWS ONLY

--思路一致：从第50条（不包括）数据开始取出25条数据：
--limit后面跟的是50条数据，offset后面是从第50条开始读取，即读取第51,52...74,75条
--MySQL
SELECT  *
FROM     MyTable 
LIMIT 50 OFFSET 25;
--SQL SERVER
SELECT  *
FROM     MyTable
ORDER BY OrderingColumn ASC
OFFSET  50 ROWS
FETCH NEXT 25 ROWS ONLY

Q ：（通用）PRINT报错： 在此上下文中不允许使用子查询。只允许使用标量表达式 ；报错语句为：PRINT ( SELECT COUNT(*) FROM TABLENAME WHERE FID = @Id );
A ：PRINT后面不能使用查询，可以通过变量来承接需要打印的输出值

DECLARE @CNT INT;
SELECT @CNT = COUNT(*) FROM TABLENAME WHERE FID = @Id;
PRINT @CNT;

Q ：（ SQL SERVER ）列名或别名中有特殊字符或为关键字
A ：特殊字符：单引号、斜杠、百分号等；关键字：from、in、select、where等。
解决办法，列名或别名用中括号括起来；
注： MySQ L可以把列名以反引号“`”（一般键盘的左上角数字1左边的那个键）来处理

SELECT [借/贷] FROM TEST; 
SELECT [FROM] FROM TEST; 
SELECT * FROM [FROM]

Q ：（ SQL SERVER ）获取本周数据时，SQL SERVER 对日期函数 DATEPART() 的日期格式 WEEKDAY ，默认是以周日作为本周第一天
A ：SQL语句开头先设置参数：SET DATEFIRST 1，即可实现日期格式 WEEKDAY 以周一作为本周第一天
注：获取 昨日-本日-本周-本月-本季-本年 的区间的SQL实现伪代码：

SET DATEFIRST 1 --设置周一为每周第一天
declare @now Date =GetDate() --本日
declare @yesterday Date =DATEADD(day, -1, @now) --昨日
declare @weekstartday Date =DATEADD(day, 1 - datepart(weekday, @now), @now) --本周一
declare @weekendday Date =DATEADD(day, -1, DATEADD(week, 1, @weekstartday)) --本周日
declare @monthstartday Date =DATEADD(day, 1-day(@now), @now) --本月第一天
declare @monthendday Date =DATEADD(day,-1,DATEADD(month , 1, @monthstartday)) --本月最后一天