sql server

首页sql server
16
Aug
0

上一行数据减去下一行数据

Oracle

--1.创建测试表
create table tmp as
select 1 id, 20 percent from dual union all
select 1 id, 30 percent from dual union all
select 1 id, 50 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual union all
select 2 id, 20 percent from dual;
--2.SQL实现
select id,100-sum(percent)over(partition by id order by rownum) percent
from tmp;

SQL SERVER

select b.相减字段-a.相减字段 from(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2=0) a
inner join
(select from (select Row_Number() over ( order by 排序字段 ) as RN , from Table Name) where rn%2<>0) b on a.rn=b.rn-1

mysql

select * from
(select (@i:=@i+1) as no,id from job_log ,(select @i:=0)t where job_id =131) as t1
left join
(select (@k:=@k+1) as no,id from job_log ,(select @k:=0)t where job_id =131) as t2
on (t1.no = t2.no-1)
order by t1.no

19
Nov
0

用脚本更新文件到DB

INSERT INTO T_UPDATE (F_ID, F_REMARK,F_VALUE)
SELECT 'EXE','16.1.66.1071.0',

  • FROM OPENROWSET(BULK N'E:update.rar', SINGLE_BLOB)
  1. F_VALUE
28
Sep
0

关闭所有触发器

exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'
go

--打开部分触发器
ALTER TABLE T_CONSUME ENABLE TRIGGER T_CONSUME_DELETE
GO
ALTER TABLE T_CONSUME ENABLE TRIGGER T_CONSUME_INSERT
GO
ALTER TABLE T_CONSUME ENABLE TRIGGER T_CONSUME_UPDATE
GO
ALTER TABLE T_ATTENDANCE ENABLE TRIGGER ATTENDANCE_ADD
GO

18
Sep
0

备份数据库批处理

@ECHO ON
set d=%date:~0,10%
set d=%d:-=%
set t=%time:~0,8%
set t=%t::=%
set stamp=%p%%d%%t%
set bakupfolder=f:sqlbackup\
rem 1按子目录保存备份文件;0不按
set lay_in_subfolder=1

call :backupone HDWMSSOEDB

goto :EOF

@ECHO OFF

:backupone
setlocal
echo %1
set dbname=%1
if not exist %bakupfolder%%dbname% mkdir %bakupfolder%%dbname%

if %lay_in_subfolder%==1 (
set subfolder=%dbname%\
)else set subfolder=
rem echo %bakupfolder%%subfolder%%dbname%%stamp%.bak
sqlcmd -U sa -P "SOEsoft123" -S localhost -Q "backup database %dbname% to disk='%bakupfolder%%subfolder%%dbname%%stamp%.bak'"
"C:Program FilesWinRARRAR.exe" a -ep1 -r -o+ -m5 -s -df "%bakupfolder%%subfolder%%dbname%%stamp%".rar "%bakupfolder%%subfolder%%dbname%%stamp%.bak"
endlocal&goto :EOF

14
Sep
0

数据库备份,日志收缩代码

//备份
s := 'backup database 大浪淘沙HDWMSSOEDB20160913 to disk=''d:数据备份sql.bak''';
//收缩日志
s := 'ALTER DATABASE [大浪淘沙HDWMSSOEDB20160913] SET RECOVERY SIMPLE;' +

   'DBCC SHRINKFILE (''HDWMSDB_log'',9,TRUNCATEONLY);' +
   'ALTER DATABASE [大浪淘沙HDWMSSOEDB20160913] SET RECOVERY FULL WITH NO_WAIT;'+
   'ALTER DATABASE [大浪淘沙HDWMSSOEDB20160913] SET RECOVERY FULL;';

sqExecSQL.SQL.Text := s;
sqExecSQL.ExecSQL;