SQLServer 语句

1、动态执行语句：

set @sql='select top 1 1 from '+@miot_db+'.dbo.syscolumns where id=object_id(''dbo.aa'') and name =''a''';
SET @Sql = 'SELECT * FROM OPENQUERY('+@dblink+', ''' + REPLACE(@Sql, '''', '''''') + ''')';

create table #S2 (c1 varchar(100))
insert #S2 exec(@sql)     

2、排序：

order by b.equipment_code ,convert(varchar(10),a.pick_time,120),charindex(','+left(a.description,1)+',',',1,2,3,4,5,6,'),charindex(','+right(a.description,3)+',',',总电量,尖电量,峰电量,谷电量,'),b.row_num

3、左连接

  select a.id,b.id
  from a left join b on a.id=b.id and a.name='aa'    -- 此时这个条件对 a 没有作用（a表全显示），相反会对b进行过滤（只有name='aa' 的 a 表行后面才会有b表的值，其余为空）。

即：对于主表的过滤是在最后的where语句中。

4、数据特殊的列转行

select [time],
         [value],
 case when lag([value]) over(order by [time])-[value]<0 then 1 else 0 end as 'sign',
         case when [value]=2 then 2 else null end as '2',
         case when [value]=3 then 3
              when LEAD([value]) over(order by [time])=3 then 3 else null end as '3',
         case when [value]=4 then 4
      when LEAD([value]) over(order by [time])=4 then 4 
  when LEAD([value],2) over(order by [time])=4 then 4 end as '4'
  from [zws].[dbo].[time_int]

5、数据库离线、在线

alter  database zws set online/offline;

6、查看session属于哪个数据库。

select session_id,db_name(database_id) from sys.dm_exec_sessions where session_id>50;

exec sp_who 

kill 51      --杀 session id 为51 的session 。

7、with递归查询

with bom as
(
select  bom_material_id,material_id,0 AS rn  from dbo.bd_bom  where bom_material_id=${material_id}
union all
select a.bom_material_id,a.material_id,rn+1 from bd_bom A,bom B where A.bom_material_id=B.material_id
)

8、根据时间段分组，且只取每组的前五条

  select  aa.*
  from 
  (select [equipment_id]
      ,[equipment_name]
      ,[material_id]
      ,[material_name]
      ,[process_id]
      ,[process_name]
      ,[item_name]
      ,[value]
 ,[report_time0]
 ,cast(report_time0 as date) as 'shite_date'
 ,DateName(hour,report_time0)/2 as 'hour_sign'
 ,ROW_NUMBER() OVER ( PARTITION BY equipment_id,
                                          material_id, process_id, 
 item_name,cast(report_time0 as date),DateName(hour,report_time0)/2 ORDER BY report_time ) as 'rn'
  from [zws].[dbo].[fact_c_qc_report_auto]
  order by [report_time0]
  ) as aa
  where rn<=5

9、动态传入参数

declare @start_time datetime
declare @end_time datetime


set @start_time='2017-11-20 00:00:35.000'
--set @end_time='2017-11-20 00:02:34.000'




select  case when  @end_time is null  then @start_time else @end_time end
SELECT *
  FROM [zws].[dbo].[dc_data]
  where pick_time>=@start_time
  and pick_time<=case when  @end_time is null then @start_time else @end_time end