SQL SERVER相关知识

内容以官方为准：SSMS官方文档

下方补充知识

查锁、杀锁

相关语句
1.查锁：

SELECT request_session_id spid,OBJECT_NAME(resource_associated_entity_id)  tableName
FROM sys.dm_tran_locks
WHERE resource_type='OBJECT '

2.杀锁：

Kill SPID

3.查锁表语句 以及 需要锁的语句

select t1.resource_type                                [资源锁定类型]
     , DB_NAME(resource_database_id)                as 数据库名
     , t1.resource_associated_entity_id                锁定对象
     , t1.request_mode                              as 等待者请求的锁定模式
     , t1.request_session_id                           等待者SID
     , t2.wait_duration_ms                             等待时间
     , (select TEXT
        from sys.dm_exec_requests r
               cross apply
             sys.dm_exec_sql_text(r.sql_handle)
        where r.session_id = t1.request_session_id) as 等待者要执行的SQL
     , t2.blocking_session_id                          [锁定者SID]
     , (select TEXT
        from sys.sysprocesses p
               cross apply
             sys.dm_exec_sql_text(p.sql_handle)
        where p.spid = t2.blocking_session_id
)                                                      锁定者执行语句
from sys.dm_tran_locks t1,
     sys.dm_os_waiting_tasks t2
where t1.lock_owner_address = t2.resource_address

SQL 查询 Oracle相关数据

-- 查询链接服务器(LinkedServer)中数据的方法： [DBLINK名].[对方数据库名].[对方数据库下模式名].[对方数据库表名]

-- 方式1
SELECT * FROM [192.168.110.189].[erp25new].[dbo].[fee_data]
SELECT * FROM [HIS5]..[HISDB].MZCPR_CLINIC_ROOM

-- 方式2
SELECT * FROM openquery(链接服务器名,'SQL语句');	
SELECT 2 FROM OPENQUERY(HIS5, 'SELECT dept_sn, room_sn FROM MZCPR_CLINIC_ROOM')

临时表

drop table #Tmp   --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(
    ID   int IDENTITY (1,1)     not null, --创建列ID,并且每次新增一条记录就会加1
    WokNo                varchar(50),   
    primary key (ID)      --定义ID为临时表#Tmp的主键      
);
Select * from #Tmp    --查询临时表的数据
truncate table #Tmp --清空临时表的所有数据和约束

跨库查询时的单引号转移问题

-- 当 ' 单引号里再出现单引号时，嵌套的那个单引号可以使用两个单引号替代''
SELECT * FROM openquery(SYHQDB,'select a.EXEC_DEPT                                             执行编码,
       e.name                                                  执行科室,
       c.NAME                                                  检查分类,
       b.D_CODE                                                自定义码,
       b.NAME                                                  组合名称,
       cast(f2.CHARGE_PRICE as number(10, 2))                  总金额,
       sum(f1.AMOUNT)                                          数量,
       cast(sum(f1.amount) * f2.charge_price as number(10, 2)) 收费金额
from JC_APPLY_RECORD a
         inner join JC_ZD_ITEM b on a.JC_CODE = b.CODE
         inner join JC_ZD_CLASS c on b.CLASS = c.CODE
         inner join dic_dept_code e on a.EXEC_DEPT = e.DEPT_SN
         inner join JC_ITEM_CHARGE f1 on f1.CODE = b.CODE
         inner join WJ_CHARGE_ITEM f2 on f1.charge_code = f2.CODE
where a.APPLY_DATE >= date ''2023-12-11''
  and a.APPLY_DATE < date ''2023-12-12''
group by c.NAME, a.EXEC_DEPT, e.name, b.D_CODE, f2.CHARGE_PRICE, b.NAME');

存在即更新，不存在即更新

if not exists( select 1 from YJYY_TEST_PRINT where patient_id='1' and admiss_times='1')
	insert into YJYY_TEST_PRINT(patient_id,admiss_times,print_date) values('1','1',CONVERT(varchar(100), GETDATE(), 20))
	else
	update YJYY_TEST_PRINT set print_date=CONVERT(varchar(100), GETDATE(), 20) where  patient_id='1' and admiss_times='1'
	```