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

81 阅读 0 评论 54 点赞

我是靠谱客的博主烂漫咖啡豆，最近开发中收集的这篇文章主要介绍[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
[ms <wbr>sql]实用sql查询语句集锦

想要得到如下结果：

对应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