SQL 语句集合(行转列，参数化...)

1.查找表中行总数以及去重行数

SELECT COUNT (*) AS '总行数' ,(SELECT COUNT(*) FROM ( SELECT DISTINCT(字段) FROM  table)a) AS '去重行数'
FROM table

2.实现参数化的模糊查询（like，in怎么写？）

--方法一：
DECLARE @test nvarchar(100)
set @test='画'
set @test='%'+@test+'%'
select @test

select * from table where name like @test
--方法二：
DECLARE @test2 nvarchar(100)
set @test2='画'

select * from table where name like '%'+@test2+'%'

3.sql数据库文件存放在哪

select filename from master.dbo.sysdatabases
where name='DataBaseName'

4.如何筛选出哪些人的次数不是从1开始
找到每个人的min（date）插入临时表，然后跟原表联合查询where date=min（date）and time <>1

5.判断字段中是否包含某些内容（CharIndex）
语法：
CHARINDEX ( expression1 , expression2 [ , start_location ] )
Expression1是要到expression2中寻找的字符串，start_location是CHARINDEX函数开始在expression2中找expression1的位置，默认从第一位开始。
这个函数将返回一个整数，返回的整数是要找的字符串在被找的字符串中的位置，假如CharIndex没有找到要找的字符串，那么返回整数‘0’
用作条件的话，charindex(g.code,‘1,2,3’)=0等同于 not in(1,2,3),但是not in 返回的是0/1，charindex返回出现的位置

--用作条件的话，charindex(g.code,'1,2,3')=0等同于 not in(1,2,3),但是not in 返回的是0/1，charindex返回出现的位置
SELECT charindex(code,'1,2,3') FROM dbo.yz_supply_class

示例：

6.自增长列插入指定值

--需要指明要插入的标识列
SET IDENTITY_INSERT table ON

INSERT INTO TABLE(id,name)
VALUES('2','测试')

SET IDENTITY_INSERT table OFF

7.查到数据表中是否有对应的列，没有就新增

  if not exists(select * from syscolumns where id=object_id('tableName') and name='columnName')  
  alter table tableName add columnName  INT NULL

8.重命名列名

  IF  EXISTS(SELECT * FROM syscolumns WHERE id = OBJECT_ID('tableName') AND name = 'columnName' )  
  EXEC sp_rename '表名.原列名', '新列名' , 'COLUMN';

9.删除默认约束

--在SQLserver中判断指定列的默认值是否存在
if exists(select A.name as DefaultName,B.name as TableName from sysobjects A 
INNER join sysobjects B on A.parent_obj = B.id where A.xtype = 'D' and B.xtype = 'U' and B.name = 'MessageInformation')
--因为不能直接修改默认值所以先删除默认值约束
alter table MessageInformation drop constraint DF_MessageInformation
GO
--重新添加新约束，并指定默认值
ALTER TABLE [dbo].[MessageInformation] ADD CONSTRAINT DF_MessageInformation DEFAULT  ((0)) FOR [Analysis]
GO

10.数值类型转换成字符类型并进行文字拼接

CAST(e.Volume as nvarchar(20)) + e.Unit+'/袋' as 规格

11.sql行转列，Max函数忽略NULL

SELECT c.Name,
	MAX(CASE WHEN a.ABO='A' THEN b.Name ELSE ''END) A,
	MAX(CASE WHEN a.ABO='B' THEN b.Name ELSE ''END) B,
	MAX(CASE WHEN a.ABO='O' THEN b.Name ELSE ''END) O,
	MAX(CASE WHEN a.ABO='AB' THEN b.Name ELSE ''END) AB
	FROM BasBloodLevel a
	LEFT JOIN  (SELECT Value,Name FROM dbo.SysEnum  WHERE Type='InvBloodLevel')b ON a.Value=b.Value
	LEFT JOIN (SELECT Value,Name FROM dbo.SysEnum  WHERE Type='BasXyzl')c ON a.BasXyzl=c.Value
	WHERE c.Value IN ('1','2')
	GROUP BY c.Name

12.统计年龄，将时间转换为年月日

(CASE WHEN ( CASE WHEN DATEADD(YEAR,DATEDIFF(YEAR, a.birth_date, a.admiss_date),a.birth_date) > a.admiss_date
                         THEN DATEDIFF(YEAR, a.birth_date, a.admiss_date) - 1
                         ELSE DATEDIFF(YEAR, a.birth_date, a.admiss_date) END ) >= 1
					     THEN CONVERT(VARCHAR(5), 
						 ( CASE WHEN DATEADD(YEAR,DATEDIFF(YEAR,a.birth_date,a.admiss_date),a.birth_date) > a.admiss_date
                                     THEN DATEDIFF(YEAR, a.birth_date,a.admiss_date) - 1
                                     ELSE DATEDIFF(YEAR, a.birth_date,a.admiss_date)
                                     END )) + 'Y'
						 ELSE CASE WHEN ( CASE WHEN DATEADD(MONTH,DATEDIFF(YEAR, a.birth_date,a.admiss_date),a.birth_date) > a.admiss_date
                                   THEN DATEDIFF(MONTH, a.birth_date,a.admiss_date) - 1
                                   ELSE DATEDIFF(MONTH, a.birth_date,a.admiss_date)
                              END ) >= 1
                       THEN CONVERT(VARCHAR(5), ( CASE WHEN DATEADD(MONTH, DATEDIFF(YEAR, a.birth_date,a.admiss_date),a.birth_date) > a.admiss_date
                                                       THEN DATEDIFF(MONTH,
                                                              a.birth_date,
                                                              a.admiss_date) - 1
                                                       ELSE DATEDIFF(MONTH,
                                                              a.birth_date,
                                                              a.admiss_date)
                                                  END )) + 'M'
                       ELSE CONVERT(VARCHAR(5), DATEDIFF(DAY, a.birth_date,
                                                         a.admiss_date)) + 'D'
                  END
        END ) AS age

未完待续…