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

89 阅读 0 评论 59 点赞

我是靠谱客的博主烂漫咖啡豆，这篇文章主要介绍[MS SQL]实用SQL查询语句集锦，现在分享给大家，希望可以做个参考。

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

复制代码 USE [TestDB]USE [TestDB]
复制代码 GO GO
复制代码 
复制代码 SET ANSI_NULLS ON SET ANSI_NULLS ON
复制代码 GO GO
复制代码 SET QUOTED_IDENTIFIER OFF SET QUOTED_IDENTIFIER OFF
复制代码 GO GO
复制代码 ALTER TRIGGER [dbo].[TestTable_U] ON [dbo].[TestTable]ALTER TRIGGER [dbo].[TestTable_U] ON [dbo].[TestTable]
复制代码 FOR UPDATE FOR UPDATE
复制代码 not for replication not for replication
复制代码 AS AS
复制代码 update TestTable set update TestTable set
复制代码 LatUpdDate = getdate()LatUpdDate = getdate()
复制代码 , LatUpdUser = (substring(suser_sname(),(charindex('',suser_sname()) + 1),128)), LatUpdUser = (substring(suser_sname(),(charindex('',suser_sname()) + 1),128))
复制代码 from inserted mfrom inserted m
复制代码 where TestTable.ColumnID = m.ColumnIDwhere 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

复制代码

1
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

复制代码

1
4.动态添加列：

复制代码 alter table add col1 varchar(20) not nullalter table add col1 varchar(20) not null

复制代码

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

复制代码 CAST (sum(workload) AS FLOATCAST (sum(workload) AS FLOAT

复制代码

1
如果为NULL，则显示为0：

复制代码 COALESCE(ColumnName, 0)COALESCE(ColumnName, 0)

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

6.一次插入多条数据

复制代码 INSERT INTO tablename INSERT INTO tablename
复制代码 (column1, column2, column3)(column1, column2, column3)
复制代码 select value1,value2,value3select value1,value2,value3
复制代码 union select value11，value12，value13union select value11，value12，value13
复制代码 union select value21, value22, value23union select value21, value22, value23

7.复制表结构数据

复制代码 SELECT * INTO 新的表名 FROM 原来的表名 WHERE 1=2SELECT * INTO 新的表名 FROM 原来的表名 WHERE 1=2

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

复制代码 select DISTINCT assignee from taskmaster where projectid=1select DISTINCT assignee from taskmaster where projectid=1

Here is the result set
[ms <wbr>sql]实用sql查询语句集锦

想要得到如下结果：

对应SQL语句如下：

复制代码 SELECT * FROM SELECT * FROM
复制代码 ( SELECT [values]= STUFF( SELECT [values]= STUFF
复制代码 (REPLACE(REPLACE
复制代码 (REPLACE((REPLACE(
复制代码 (SELECT DISTINCT assignee FROM taskmaster N WHERE projectid=1 FOR XML AUTO), '<N assignee="', ','), '"></N>', ','), 1, 1, '')(SELECT DISTINCT assignee FROM taskmaster N WHERE projectid=1 FOR XML AUTO), '<N assignee="', ','), '"/>', ','), 1, 1, '')
复制代码 )N)N

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

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

复制代码 SELECT name FROM syscolumnsSELECT name FROM syscolumns
复制代码 WHERE (id =WHERE (id =
复制代码 (SELECT id FROM sysobjects(SELECT id FROM sysobjects
复制代码 WHERE (id = OBJECT_ID('tablename'))))WHERE (id = OBJECT_ID('tablename'))))
复制代码 ORDER BY colidORDER BY colid

10.复制某表的一行数据

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

复制代码 begin tran t1begin tran t1
复制代码 SET IDENTITY_INSERT shipment On SET IDENTITY_INSERT shipment On
复制代码 insert into shipment (ShipmentID,Address,ContactPhone,CreateDate,CreateUser,Custno)insert into shipment (ShipmentID,Address,ContactPhone,CreateDate,CreateUser,Custno)
复制代码 select select
复制代码 '343566',Address,ContactPhone,CreateDate,CreateUser,Custno from shipment'343566',Address,ContactPhone,CreateDate,CreateUser,Custno from shipment
复制代码 where shipmentID = '12345' where shipmentID = '12345'
复制代码 SET IDENTITY_INSERT shipment Off SET IDENTITY_INSERT shipment Off
复制代码    
复制代码 commit tran t1commit tran t1
复制代码 rollback t1rollback t1