SQLServer 语句
1、动态执行语句:
set @sql='select top 1 1 from '[email protected]_db+'.dbo.syscolumns where id=object_id(''dbo.aa'') and name =''a''';
SET @Sql = 'SELECT * FROM OPENQUERY('[email protected]+', ''' + 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>[email protected]_time
and pick_time<=case when @end_time is null then @start_time else @end_time end
10、时间段连续时,合并时间段显示(另书上说with 公用表达式会提高效率,经实践发现在 递归之前 已有with 表达式,将之前的with 表达式的数据写入临时表反而更快)
with v as
(select a.COL,a.START_ID,a.END_ID,a.START_ID 'sn'
from dbo.TEST a
where not exists(select 1
from dbo.TEST b
where b.COL=a.COL
and b.END_ID=a.START_ID)
union all
select d.COL,d.START_ID,d.END_ID,c.sn
from v c
inner join dbo.TEST d on c.COL=d.COL
and c.END_ID=d.START_ID)
--select * from v order by START_ID
select COL,min(START_ID) 'StartTime',max(END_ID) 'EndTime'
from v
group by COL,sn
10、数据分组,组内排序
DENSE_RANK() over( partition by p.batch_no,p.barcode,p.equipment_name,p.process_name order by p.W_NAME) as group_no, --组号
ROW_NUMBER() over( partition by p.batch_no,p.barcode,p.equipment_name,p.process_name,p.W_NAME order by p.Happen_time) as group_rn --组内序号