--字符串连接
SELECT CONCAT('Hello','world',null,'2012')
--字符串格式化
--参考http://msdn.microsoft.com/en-US/library/c3s1ez6e.aspx
SELECT FORMAT(GETDATE(),'g') --2012/8/10 14:12
UNION ALL
SELECT FORMAT(GETDATE(),'t') --14:13
UNION ALL
SELECT FORMAT(GETDATE(),'d') --2012/8/10
UNION ALL
SELECT FORMAT(1.00/3,'c') --¥0.33 货币
UNION ALL
SELECT FORMAT(1.00/3,'f4') -- 0.3333 保留4位小数
--选择
DECLARE @t TABLE(studentid int,class varchar(50),grade int)
INSERT INTO @t
SELECT 1,'数学',10 UNION ALL
SELECT 1,'语文',20 UNION ALL
SELECT 1,'英语',30 UNION ALL
SELECT 2,'英语',60 UNION ALL
SELECT 2,'数学',40 UNION ALL
SELECT 2,'语文',50 UNION ALL
SELECT 3,'语文',80 UNION ALL
SELECT 3,'数学',70 UNION ALL
SELECT 3,'英语',90
SELECT
CHOOSE(studentid,'孙小美','阿土伯','小叮铛'),class,grade,
IIF(grade < 60, '不及格','及格')
FROM @t
SELECT EOMONTH(GETDATE(),1), --下个月最后一天
EOMONTH(GETDATE(),0), --本月最后一天
EOMONTH(GETDATE(),-1), --上个月最后一天
EOMONTH(GETDATE(),-2) --上上个月最后一天
--尝试转换
SELECT TRY_PARSE('1' as int),TRY_PARSE('1' as datetime)
SELECT
*,
first_value(class) over (order by studentid), --第一位同学的第一门课的名称
last_value(class) over (order by studentid)
FROM @t
/*
First_value() returns the first available value in the ordered set while last_value() returns the last value available.
It should be noted that these values are calculated for each row.
So the value returned by last_value() changes from row to row.
first_value is same for the resultset, while the last_value is dynamically changed for each row
*/
SELECT
*,
first_value(class) over (partition by studentid order by studentid), --每一位同学的第一门课的名称
last_value(class) over (partition by studentid order by studentid)
FROM @t
--分页方便了
select * from @t
order by studentid
offset 2 rows
fetch next 2 rows only
SELECT *,
lead(grade) over (order by studentid),--下一课成绩
LAG(grade) over (order by studentid) --上一课成绩
FROM @t
