T-SQL - 随笔分类 - 光照峋山

SQL中声明变量并赋值

摘要：如下例：declare @name varchar(30), @wage moneyselect @name=e_name，@wage=e_wagefrom employeewhere emp_id='10010001'赋予的值应通过WHERE筛选成唯一值阅读全文

posted @ 2011-05-05 15:57 光照峋山阅读(3180) 评论(0) 推荐(0)

合并多行查询数据到一行：使用自连接、FOR XML PATH('')、STUFF或REPLACE函数

摘要：示例表 tb 数据如下id value—————1 aa1 bb2 aaa2 bbb2 ccc第一种SELECT id, [val]=( SELECT [value] +',' FROM tb AS b WHERE b.id = a.id FOR XML PATH('') ) FROM tb AS a 第一种显示结果 1 aa,bb, 1 aa,bb, 2 aaa,bbb,ccc, 2 aaa,bbb,ccc, 2 aaa,bbb,ccc,第二种SELECT id, [val]=( SELECT [value] +',' FROM tb AS b 阅读全文

posted @ 2011-04-12 21:33 光照峋山阅读(10393) 评论(0) 推荐(2)

SELECT时的with(nolock)选项说明

摘要：I used to see my senior developers use WITH (NOLOCK) when querying in SQL Server and wonder why they use. Now i explored it and found that it's useful to improve the performance in executing the query . However there is a disadvantage in using it. The disadvantage is that one may not be sure tha 阅读全文

posted @ 2011-04-12 20:45 光照峋山阅读(1223) 评论(0) 推荐(0)

用SET STATISTICS TIME命令显示T-SQL语句执行时间

摘要：下面的示例显示服务器的执行、分析和编译时间。USE AdventureWorks; GO SET STATISTICS TIME ON GO SELECT * FROM Production.ProductCostHistory WHERE StandardCost < 500.00; GO SET STATISTICS TIME OFF; GO下面是输出结果：SQL Server parse and compile time:CPU time = 0 ms, elapsed time = 1 ms.SQL Server parse and compile time:CPU time = 阅读全文

posted @ 2011-04-12 20:43 光照峋山阅读(497) 评论(0) 推荐(0)

使用带参数的简单存储过程

摘要：下面的存储过程只从视图中返回指定的雇员（提供名和姓）及其职务和部门名称。此存储过程接受与传递的参数精确匹配的值。CREATE PROCEDURE HumanResources.uspGetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SELECT FirstName, LastName, JobTitle, Department FROM HumanResources.vEmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName; G 阅读全文

posted @ 2011-04-12 20:34 光照峋山阅读(266) 评论(0) 推荐(0)

T-SQL 循环语句 sample

摘要：declare @a int set @a=0 while @a<=100 begin update table set title=(Select Replace(title,'<script src=http://cn.daxia123.cn/cn.js></script>','') from table where id=@a) where id=@a set @a=@a+1 end 阅读全文

posted @ 2011-04-12 20:23 光照峋山阅读(565) 评论(0) 推荐(0)

查询时用ROW_NUMBER()返回行号

摘要：SELECT ROW_NUMBER() OVER (PARTITION BY partition_column ORDER BY order_column) FROM tablename ORDER BY后为排序列，必须指明PARTITION BY 可选，它将结果集按照分区列分为多个分区，函数分别应用于每个分区，并为每个分区重新启动计算。如下列查询结果Rownumber Category ProductName 1 Book My Life 2 Book Harry Poter 3 Book Beginning C++ 1 Pencil 2B 2 Pencil HB 阅读全文

posted @ 2011-04-12 19:46 光照峋山阅读(426) 评论(0) 推荐(0)

奥の翰林