[MS SQL]实用SQL查询语句集锦

1.SQL Server中用Trigger自动记录最后输入的时间和用户名

 USE [TestDB]

 GO 

 SET ANSI_NULLS ON 

 GO 

 SET QUOTED_IDENTIFIER OFF 

 GO 

 ALTER TRIGGER [dbo].[TestTable_U] ON [dbo].[TestTable]

 FOR UPDATE 

 not for replication 

 AS 

 update TestTable set 

 LatUpdDate = getdate()

 , LatUpdUser = (substring(suser_sname(),(charindex('\',suser_sname()) + 1),128))

 from inserted m

 where TestTable.ColumnID = m.ColumnID

2.几个SQL函数用法示例

SELECT LEFT(columnname,1),COUNT(*) FROM  TableName WHERE columnname NOT LIKE '0____' GROUP BY LEFT(columnname,1) ORDER BY 1

SELECT RIGHT(LEFT(columnname,2),1),COUNT(*) FROM TableName WHERE columnname NOT LIKE '_0___' GROUP BY RIGHT(LEFT(columnname,2),1) ORDER BY 1

SELECT
SUM(CASE WHEN LEFT(promotion,1)='1' THEN 1 ELSE 0 END),
SUM(CASE WHEN RIGHT(LEFT(promotion,3),1)='1' THEN 1 ELSE 0 END),
SUM(CASE WHEN RIGHT(LEFT(promotion,17),1)='1' THEN 1 ELSE 0 END) FROM TableName

3.ROW_NUMBER示例：
 

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', SalesYTD, PostalCode FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

SELECT ( SELECT Name FROM TableName1 where [ProjectID] = TableName .ID) as ProjectName , ROW_NUMBER() OVER(Order by LastModifiedDate ASC) AS 'RowNumber', *  FROM TableName Order by LastModifiedDate ASC

4.动态添加列：  

 alter table add col1 varchar(20) not null

 可参看另外一篇博文：对某个DB中所有的表加上一个字段
 
 5.转换数据类型：  

 CAST (sum(workload) AS FLOAT

如果为NULL，则显示为0：

 COALESCE(ColumnName, 0)

可参看另外一篇博文：CAST和CONVERT实例讲解 和 COALESCE用法讲解

6.一次插入多条数据

 INSERT INTO tablename 

 (column1, column2, column3)

 select value1,value2,value3

 union select value11，value12，value13

 union select value21, value22, value23

7.复制表结构数据

 SELECT * INTO 新的表名 FROM 原来的表名 WHERE 1=2

8.将查询结果拼接成字符串
若查询语句如下：

 select DISTINCT assignee from taskmaster where projectid=1

Here is the result set

想要得到如下结果：

对应SQL语句如下：

 SELECT * FROM 

 ( SELECT [values]= STUFF

 (REPLACE

 (REPLACE(

 (SELECT DISTINCT assignee FROM taskmaster N WHERE projectid=1 FOR XML AUTO), '<N assignee="', ','), '"/>', ','), 1, 1, '')

 )N

如果执行FOR XML语句，结果如下：

将其中的<N assignee=" 替换成空格，将"/>替换成,即可以得到如上结果。
9.取出某表的所有列名

 SELECT name FROM syscolumns

 WHERE (id =

 (SELECT id FROM sysobjects

 WHERE (id = OBJECT_ID('tablename'))))

 ORDER BY colid

10.复制某表的一行数据

有时候我们需要在某个表中插入一条数据。如有个客户姓名的字段。这个字段可以是重复的。同时还有客户的地址，联系信息等字段。这些内容都是一样的。只有一两个字段是不同的。如果这个表的字段特别多，自己写Insert Into就显得很累很麻烦。如果可以复制这个表中的这个客户的记录，成为一条新纪录，然后我们只需要更新那一两个不同的字段内容就行了。

 begin tran t1

 SET IDENTITY_INSERT shipment On 

 insert into shipment (ShipmentID,Address,ContactPhone,CreateDate,CreateUser,Custno)

 select 

 '343566',Address,ContactPhone,CreateDate,CreateUser,Custno from shipment

 where shipmentID = '12345' 

 SET IDENTITY_INSERT shipment Off 

 commit tran t1

 rollback t1