sql server 数据库用什么工具 sql server数据库使用

转载
mob64ca14122c74 2023-08-29 13:06:42
文章标签 sql server 数据库用什么工具数据库 sql 字段 EXEC 文章分类 SQL Server 数据库
以下内容为平时工作自己个人总结，仅供参考，欢迎交流
SELECT A.CBM,A.CMC,SUM(B.MJE)MJE FROM YXHIS..TBZDZTHZ A,YXHIS..TBZDZTMX B where A.CBM LIKE '%CGZ%' AND B.CZTMC=A.CMC GROUP BY A.CBM,A.CMC
1.定时自动计划任务 每日未结算病人的记录
select * from YXHIS2017..tbzybrjl201706
select * from YXHIS2017..tbzybrWJZjl201706
2.查询原记账单为空的病人
SELECT CYJZD,MJE, * FROM YXHIS2017..TBFYMX201706 WHERE (CDYLB = '城乡居民') AND (CSFXM NOT LIKE '联网划价%') AND (MJE < 0) AND (CYJZD = '') ORDER BY CZYH
3.住院发票查询语句
SELECT * from yxhis..TBZYSJZDYXM
SELECT * from yxhis..TBZYZDSJ
update yxhis..TBZYZDSJ set ITOP=ITOP-14 WHERE CSJMC LIKE '%省直医保%' and IBH<>0 解锁没有办法执行修改表字段的语句以及方法
alter table yxlis2017..tbbrybxx05 alter column inl varchar(20)use yxlis2017
sp_help tbbrybxx06kill 230
select*from sysobjects where name='tbbrybxx05'
 select convert (smallint, req_spid) As spid, 
 rsc_dbid As dbid, 
 rsc_objid As ObjId, 
 rsc_indid As IndId from master.dbo.syslockinfo where rsc_objid=9906265724.游标
use ysgzz
go
DECLARE CurTable CURSOR /*定义一个游标*/
 FOR 
 SELECT name FROM sysobjects where name like 'TB%' AND TYPE='U' 
OPEN CurTable /*打开游标*/
 DECLARE @TBNAME VARCHAR(20) /*定义一个局部变量*/ 
FETCH NEXT FROM CurTable INTO @TBNAME /*读取数据存放在指定变量中*/
WHILE (@@FETCH_STATUS <> -1) /*@@为全局变量0表示上一个FETCH执行成功,-1所要读取的行不在结果集中,-2被提取的行已不存在(已被删除)*/
BEGIN
 if exists (select * from sysobjects where name=@TBNAME)
 begin
 if exists(select * from syscolumns where name='cbrnl' and id=object_id(@TBNAME))
 exec('alter table '+@TBNAME+' alter column cbrnl varchar(20)')
 end
 FETCH NEXT FROM CurTable INTO @TBNAME
END
CLOSE CurTable /*关闭游标*/
DEALLOCATE CurTable /*删除游标*/ 
go 5.统计语句
SELECT * FROM YXHIS..TBYXXTCSI where ccsmc='IZYSYFY'
select sum(sl) from (
select left(DRQ,10) RQ,CZYH,1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='121600001' and isl>0 group by left(DRQ,10),CZYH
union all 
select left(DRQ,10),CZYH,-1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='121600001' and isl<0 group by left(DRQ,10),CZYH ) A select sum(sl) from (
select left(DRQ,10) RQ,CZYH,1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='1216000010' and isl>0 group by left(DRQ,10),CZYH
union all 
select left(DRQ,10),CZYH,-1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='1216000010' and isl<0 group by left(DRQ,10),CZYH ) A select sum(sl) from (
select left(DRQ,10) RQ,CZYH,1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='1216000011' and isl>0 group by left(DRQ,10),CZYH
union all 
select left(DRQ,10),CZYH,-1 sl from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='1216000011' and isl<0 group by left(DRQ,10),CZYH ) A select CKDKSMC,sum(isl) 数量 from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='121600001' group by CKDKSMC order by CKDKSMC
select CKDKSMC,sum(isl) 数量 from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='1216000010' group by CKDKSMC order by CKDKSMC
select * from YXHIS2017..VTBFYMX2017 where DRQ>='2017-06-01' and DRQ<'2017-07-01' and CSFXMBM='121600001' group by CKDKSMC order by CKDKSMC
6.医保接口费用字段解释
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBZYJSXX](
 [CLSH] [varchar](30) NULL,--住院交易流水号
 [CZYH] [varchar](15) NOT NULL,--住院号
 [CYBH] [varchar](20) NULL,--医保号(个人编号)
 [CSFD] [varchar](20) NULL,--收费单
 [CXM] [varchar](30) NULL,--姓名
 [CXB] [varchar](4) NULL,--性别
 [MZJE] [money] NULL,--总金额 医院端
 [MXJZF] [money] NULL,--现金支付 
 [MZHZF] [money] NULL,--账户支付
 [MTCZF] [money] NULL,--统筹支付 
 [MZHYE] [money] NULL,--账户余额 
 [CCZYGH] [varchar](10) NULL,--操作员工号
 [CCZYXM] [varchar](20) NULL,--操作员姓名
 [CKSBM] [varchar](30) NULL,--可是编码
 [CKSMC] [varchar](30) NULL,--科室名称
 [DRYSJ] [datetime] NULL,--入院时间
 [DJSSJ] [datetime] NULL,--结算时间
 [DCYSJ] [datetime] NULL,--出院时间
 [IZYCS] [int] NULL,--住院次数
 [IZTJZCS] [int] NOT NULL,--中途结账次数
 [ITFBZ] [int] NOT NULL,--退费标志 0是正常结算 其他为退费
 [CYBLB] [varchar](20) NULL,--医保类别 
 [CYLLB] [varchar](20) NULL,--医疗类别 
 [CZDMC] [varchar](200) NULL,--诊断编码
 [CSFZH] [varchar](30) NULL,--无用字段
 [ISFZL] [int] NULL,--无用字段
 [CSFZL] [varchar](30) NULL,---无用
 [IQXCS] [int] NOT NULL,--出院取消次数
 [IZT] [int] NULL,--状态
 [MFY1] [money] NULL,--总费用 MFY1-MFY21 对应接口文档结算业务返回的字段
 [MFY2] [money] NULL,--本次账户支付
 [MFY3] [money] NULL,--统筹支付
 [MFY4] [money] NULL,--大病救助
 [MFY5] [money] NULL,--公务员补助
 [MFY6] [money] NULL,--企业基金支付
 [MFY7] [money] NULL,--本次现金支付
 [MFY8] [money] NULL,--自费费用
 [MFY9] [money] NULL,--乙类药品自理
 [MFY10] [money] NULL,--乙类项目自理
 [MFY11] [money] NULL,--特检特治
 [MFY12] [money] NULL,--超出方案费用
 [MFY13] [money] NULL,--起付标准自付
 [MFY14] [money] NULL,--进入统筹费用
 [MFY15] [money] NULL,--统筹分段自付
 [MFY16] [money] NULL,--进入救助金费用
 [MFY17] [money] NULL,--救助金自付
 [MFY18] [money] NULL,--超封顶线自付
 [MFY19] [money] NULL,--符合基本医疗费用
 [MFY20] [money] NULL,--一般起付标准额
 [MFY21] [money] NULL,--住院次数
 [MFY22] [money] NULL,
 [MFY23] [money] NULL,
 [MFY24] [money] NULL,
 [MFY25] [money] NULL,
 [MFY26] [money] NULL,
 [MFY27] [money] NULL,
 [MFY28] [money] NULL,
 [MFY29] [money] NULL,
 [MFY30] [money] NULL,
 [MFY31] [money] NULL,
 [MFY32] [money] NULL,
 [MFY33] [money] NULL,
 [MFY34] [money] NULL,
 [MFY35] [money] NULL,
 [MFY36] [money] NULL,
 [MFY37] [money] NULL,
 [MFY38] [money] NULL,
 [MFY39] [money] NULL,
 [MFY40] [money] NULL,
 [MFY41] [money] NULL,
 [MFY42] [money] NULL,
 [MFY43] [money] NULL,
 [MFY44] [money] NULL,
 [MFY45] [money] NULL,
 [MFY46] [money] NULL,
 [MFY47] [money] NULL,
 [MFY48] [money] NULL,
 [MFY49] [money] NULL,
 [MFY50] [money] NULL,
 [CBZ1] [varchar](20) NULL,--结算类别 ZO1 
 [CBZ2] [varchar](20) NULL,--单位编码
 [CBZ3] [varchar](100) NULL,--单位名称
 [CBZ4] [varchar](20) NULL,
 [CBZ5] [varchar](20) NULL,
 [CBZ6] [varchar](20) NULL,
 [CBZ7] [varchar](20) NULL,
 [CBZ8] [varchar](20) NULL,
 [CBZ9] [varchar](20) NULL,
 [CBZ10] [varchar](20) NULL,
 [CSCFS] [varchar](50) NULL,
 CONSTRAINT [PK_TBZYJSXX] PRIMARY KEY CLUSTERED 
(
 [CZYH] ASC,
 [IZTJZCS] ASC,
 [ITFBZ] ASC,
 [IQXCS] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO7.SELECT B.IBM 财务编码 , B.CMC 财务统计名称, A.CBM 物价编码,A.CMC 物价名称 FROM TBZDYLXM A,YXHIS..TBZDCWTJMZ B WHERE A.BENABLEMZ='1' AND B.IBM =A.IMZCWBM order by b.ibm 
SELECT B.IBM 财务编码 , B.CMC 财务统计名称,A.CBM 物价编码, A.CMC 物价名称 FROM TBZDYLXM A,YXHIS..TBZDCWTJ B WHERE A.BENABLE='1' AND B.IBM = A.IZYCWBM order by b.ibm 住院
8.正式库和测试库连接查询
select * from YXHIS..TBUSERPARAM WHERE CNBMC NOT IN (
select CNBMC From CSK.YXHIS.DBO.TBUSERPARAM )9.取消绿色通道流程
SELECT BLSTD, * FROM YXHIS2017..TBMZGHMX201707 WHERE CXM='邹春华' 
UPDATE YXHIS2017..TBMZGHMX201707 SET BLSTD='0' WHERE CXM='邹春华' 
SELECT * FROM YXHIS2017..TBLSBRJL2017 WHERE CXM='邹春华' 
INSERT INTO YXHIS2017..TBLSBRJL2017 SELECT * FROM YXHIS..TBLSBRJL WHERE CXM='邹春华'
DELETE YXHIS..TBLSBRJL WHERE CXM='邹春华' 
SELECT * FROM YXHIS2017..TBLSBRJL2017 WHERE CXM='邹春华' 
UPDATE YXHIS2017..TBLSBRJL2017 SET DQXSJ='2017-07-26 12:03:07.000',DQJRBM='53109',DQJRXM='张慧垒' WHERE CXM='邹春华' 10.PACS住院医生站以及门诊医生站看图片的配置
 SELECT * FROM TBPACS_DY WITH(NOLOCK) WHERE ('|' + CMBBH + '|' LIKE '%|03378|%' or CMBBH = '*') AND URLLX = 'PACS_REPWEBPATH_ZY'
 Select * from TBXMFMBHZ WHERE CMBMC LIKE '%乳腺%'
 INSERT INTO TBPACS_DY VALUES ('03469','PACS_PICWEBPATH_MZ','http://172.16.0.6:8081/TakeImage.aspx?colid0=3078&colvalue0=[CJCDH2]')
 INSERT INTO TBPACS_DY VALUES ('03469','PACS_PICWEBPATH_ZY','http://172.16.0.6:8081/TakeImage.aspx?colid0=3078&colvalue0=[CJCDH]')
 INSERT INTO TBPACS_DY VALUES ('03469','PACS_REPWEBPATH_MZ','http://172.16.0.6:8081/WebReport.aspx?colid0=3078&colvalue0=[CJCDH]')
 INSERT INTO TBPACS_DY VALUES ('03469','PACS_REPWEBPATH_ZY','http://172.16.0.6:8081/WebReport.aspx?colid0=3078&colvalue0=[CJCDH]')11.物资退库报错
SELECT MAX(CCRD) FROM YXHQGL..TBWZCRKMX2017 WHERE CCRD LIKE '%K%' AND IKWBM='3081'SELECT * FROM YXHQGL..TBWZCRKMX2017 WHERE CCRD LIKE '%K%' AND IKWBM='3081'
SELECT ITKD,* FROM YXHQGL..TBWZZDKW WHERE IKWBM='3027'
UPDATE YXHQGL..TBWZZDKW SET ITKD=7 WHERE IKWBM='3081'12.
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
13.
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>114.农合 费用 处理
select * From HNSNHJK293..tbfymx_mid2017 WHERE iksbm1 is nullselect * From HNSNHJK293..tbfymx_mid2017 a,yxhis2017..tbfymx201707 b 
where 'CF'+a.ccfh=CDJH AND iksbm1 is nullUPDATE A SET IKSBM1=IZYKS,IKSMC1=CZYKS From HNSNHJK293..tbfymx_mid2017 a,yxhis2017..tbfymx201707 b 
where a.ccfh='0'+B.CJZD AND iksbm1 is nullUPDATE A SET IKSBM1=IZYKS,IKSMC1=CZYKS From HNSNHJK293..tbfymx_mid2017 a,yxhis2017..tbfymx201707 b 
where 'CF'+a.ccfh=CDJH AND iksbm1 is null
15.查询挂号超500的数据
select CONVERT(VARCHAR(10),DGH,23),COUNT(*) from yxhis2017..VTBMZGHMX2017 where bth=0 GROUP BY CONVERT(VARCHAR(10),DGH,23) HAVING COUNT(*)>500 ORDER BY CONVERT(VARCHAR(10),DGH,23) 16.药房发药报临时表错误修改语句
SELECT * FROM YSGZZ..TBYZBYZYPBQ105 WHERE CXDYS='管理员' 删除医嘱本内容SELECT * FROM YXHIS..TBZYYZYPSQBQ111 WHERE CSZ='230001' 这个也要改
SELECT * FROM YXHIS..TBZYYZYPBQ111 WHERE CSZ='230001' 这个也要改
update YXHIS..TBZYYZYPBQ111 set CBZ='' WHERE CSZ='230001'
SELECT * FROM HNSNHJK293..TBYPXXDZ WHERE CBM='230001'
SELECT * FROM HNSNHJK293..YPML WHERE len(备注)>20
update HNSNHJK293..YPML set 备注='限耐多药肺结核治疗。' WHERE 项目编码='XYX01299000'
报错可能牵扯其他的药品17.链接服务器
-1- EXEC sp_addlinkedserver 'ITSV123','','SQLOLEDB','172.17.19.64\SQL2016SVR\HNSSLYYDW1'
EXEC sp_addlinkedsrvlogin 'ITSV123','FALSE',NULL,'sa','P@$$w0rd'
SELECT * FROM ITSV123.DataSource.DBO.DATAKEY
-2- SELECT * FROM openrowset('SQLOLEDB','SQL2016SVR\HNSSLYYDW1';'sa';'P@$$w0rd',DataSource.DBO.DATEKEY)
SELECT * FROM openrowset('SQLOLEDB','192.168.1.1';'sa';'123',yxhis2017.DBO.tbmzghmx201708)
SELECT * INTO HNERYY..tbmzghmx FROM openrowset('SQLOLEDB','192.168.1.1';'sa';'123',yxhis2017.DBO.tbmzghmx201708)
查询链接服务器数量 SELECT * FROM SYS.SERVERS18.门诊程序问题：退费数据：select * from YXHIS2017..TBMZFYMX201706 where CSFD='0082000517'AND CXMMC='联网划价西药'
19.//A表是多的 B表是少的 筛选出在A表和B表不一样的数据，按编码条件检索
SELECT * FROM YXBAK..WJWTTJWJXM3 A WHERE NOT EXISTS (SELECT * FROM YXBAK..WJWTTJWJXM1 B WHERE A.收费项目编码=B.收费项目编码)
20.链接数据库查询
select * from nhqzj.[hzyl_qzj].[dbo].[hzyl_bc_bczb_history] where zyh='17060425'21.触发器
/****** Object: Trigger [dbo].[TBFYMX201710_INSERT] Script Date: 2017-10-10 09:34:27 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GOALTER TRIGGER [dbo].[TBFYMX201710_INSERT] ON [dbo].[TBFYMX201710]
AFTER INSERT 
AS
UPDATE A SET CKDKSBM=B.CBM,CKDKSMC=B.CMC FROM YXHIS2017..TBFYMX201710 A,(SELECT A.CBM,A.CMC,B.IBM,B.CMC CYSMC FROM YXHIS..TBZDZXKS A,YXHIS..TBZDZYYS B WHERE A.IZYKSBM=B.CKSBM AND B.CKSBM IS NOT NULL AND B.CKSBM<>'0')B,INSERTED C WHERE A.IZYYS=B.IBM AND A.CJZD=C.CJZD AND A.IID=C.IID AND (A.CKDKSMC like'%病区%' OR A.CKDKSMC='')
--UPDATE A SET CZXKSBM=B.CBM,CZXKSMC=B.CMC FROM YXHIS2017..TBFYMX201710 A,YXHIS..TBZDZXKS B,INSERTED C WHERE A.IZYKS=B.IZYKSBM AND A.CJZD=C.CJZD AND A.IID=C.IID AND (A.CZXKSMC like'%病区%' OR A.CZXKSMC='')
UPDATE A SET MDJ=B.MDJ3,MJE=MDJ3*A.ISL,MSJ=MDJ3*A.ISL*FBL6,FBL=FBL6 FROM YXHIS2017..TBFYMX201710 A,YXHIS..TBZDYLXMJGTX B,YXHIS..TBZDYLXMBL C,INSERTED D WHERE A.IID=D.IID AND A.CJZD=D.CJZD AND A.CSFXMBM=B.CBM AND B.CBM=C.CBM AND A.MDJ<>B.MDJ3 AND A.IDYLB IN(SELECT IBM FROM YXHIS..TBZDSFZL WHERE IJGTX=3)
SELECT * FROM YXHIS2017..TBFYMX201710 A,YXHIS..TBZDZXKS B WHERE A.CKDKSBM=B.CBM AND A.CKDKSMC<>B.CMC
UPDATE A SET CKDKSMC=B.CMC FROM YXHIS2017..TBFYMX201710 A,YXHIS..TBZDZXKS B,inserted C WHERE A.CKDKSBM=B.CBM AND A.CJZD=C.CJZD AND A.IID=C.IID AND A.CKDKSMC<>B.CMCGO
22.用replace
设定 字段名 volumnName
需要修改的字符 A 替换成 BCDupdate 表名 set volumnName=replace(volumnName,'A','BCD')
23.组套项目查询
SELECT A.CBM 组套编码,A.CMC 组套名称,A.CYJKSMC 医技科室名称,B.CSFXMBM 收费项目编码,B.CSFXMMC 收费项目名称,B.CDW 单位,sum(MJE) 单价 FROM TBZDZTHZ A ,TBZDZTMX B WHERE A.CBM=B.CZTBM and a.benable=1 group by A.CBM,A.CMC,A.CYJKSMC,B.CSFXMBM,B.CSFXMMC,B.CDW order by A.CBM,A.CMC,A.CYJKSMC,B.CSFXMBM,B.CSFXMMC,B.CDW24.处方退药找不到的情况
SELECT * FROM YXHIS2017..TBZYCFMX201710 WHERE CCFH='1710001856'
SELECT * FROM VTBZYCFHZWJZ WHERE CCFH='1710017723'
SELECT * FROM TBZYCFHZWJZ_03 WHERE CCFH='1710017723'
SELECT * FROM TBZYCFMXWJZ_03 WHERE CCFH='1710017723'
DELETE FROM TBZYCFTYSQ_Mid WHERE CYCFH='1710001856|2'25.检验检查
SELECT CBRH 门诊号,CBRXM 患者姓名,CJLRMC 医生,CSQZXDWMC 科室,CKZXXM 项目名称, DJLRQ 日期,SUM(MCOSTS)金额 FROM YSGZZ..VTBMZJYSQDXXWZX Left Join TBZDMZYS on TBZDMZYS.CCZYGH=CJLRBM WHERE 1=1 and IZXZT<>3 and (ISTATUS>0) and DJLRQ>='2017-01-01' and DJLRQ<='2017-11-13 23:59:59' and (ISFZT<>1 and IZXZT=0 and IBGZT=0) and CMBBH in ('1','03020','03031','03032','03033','03034','03075','03234','03253','03496') group by CBRH ,CBRXM ,CJLRMC ,CSQZXDWMC ,CKZXXM , DJLRQ SELECT CBRH 住院号,CBRXM 患者姓名,CJLRMC 医生,CSQZXDWMC 科室,CBQMC 病区, CKZXXM 项目名称, DJLRQ 日期,SUM(MCOSTS)金额 FROM YSGZZ..VTBZYJYSQDXXWZX Left Join TBZDZYYS on TBZDZYYS.CCZYGH=CJLRBM WHERE 1=1 and IZXZT<>3 and (ISTATUS>0) and DJLRQ>='2017-01-01' and DJLRQ<='2017-11-13 23:59:59' and (ISFZT<>1 and IZXZT=0 and IBGZT=0) and CMBBH in ('1','03020','03031','03032','03033','03034','03075','03234','03253','03496') and BQZ=1 group by CBRH ,CBRXM ,CJLRMC 
,CSQZXDWMC ,CKZXXM , DJLRQ ,CBQMC order by DJLRQ26.一个表里的数据不包含另一个表的数据里，就是筛查出两张表里不一样的数据
select * from HNSYBJK184..TBFYMX_Mid2017 a where not exists(select 1 from HNSYBJK184..TBZYJSXX b where A.CZYH=B.CZYH)
select * from HNSYBJK184..TBFYMX_Mid2017 B where (select count(1) as num from HNSYBJK184..TBZYJSXX A where A.CZYH=B.CZYH)= 0
方法一(仅适用单个字段)
使用 not in ,容易理解,效率低select A.ID from A where A.ID not in (select ID from B)
方法二(适用多个字段匹配)
使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录select A.ID from A left join B on A.ID=B.ID where B.ID is null 
方法三(适用多个字段匹配)
select * from B where (select count(1) as num from A where A.ID = B.ID) = 0
方法四(适用多个字段匹配)
select * from A where not exists(select 1 from B where A.ID=B.ID
27.农合发票号更新语句
SELECT A.CFPH,B.CJZH, * FROM YXHIS2018..TBZYBR2018 A,HNSNHJK293..TBZYJSXX B WHERE A.CZYH=B.CZYH AND B.DJSSJ > '2018-02-01 00:00:00.000' AND B.DJSSJ < '2018-02-28 23:59:59.000' AND B.CJZH=''
UPDATE B SET B.CJZH=A.CFPH FROM YXHIS2018..TBZYBR2018 A,HNSNHJK293..TBZYJSXX B WHERE A.CZYH=B.CZYH AND B.DJSSJ > '2018-02-01 00:00:00.000' AND B.DJSSJ < '2018-02-28 23:59:59.000' AND B.CJZH=''28.科室对应病区查询语句
SELECT A.CKSMC,B.CMC FROM YXHIS..TBZDKS A,YXHIS..TBZDBQ B WHERE IBQBM=IBM ORDER BY CMC
 SELECT A.CKSMC,B.CMC FROM YXHIS..TBZDKS A,YXHIS..TBZDBQ B WHERE IBQBM=IBM and A.BENABLE='1' AND B.BENABLE='1' AND CKSMC NOT IN ('产房','儿童康复科') ORDER BY CMCSELECT CZYH FROM HNSYBJK184..TBFYMX_Mid2017 WHERE CZYH IN ( SELECT * FROM (select CZYH from HNSYBJK184..TBFYMX_Mid2017 a where not exists(select 1 from HNSYBJK184..TBZYJSXX b where A.CZYH=B.CZYH))C)
-----医保类别查询门诊丢失添加----
SELECT * FROM HNSYBJK184..TBZDYLLB
INSERT INTO HNSYBJK184..TBZDYLLB VALUES('11','普通门诊')----修改门诊挂号费用明细为空的数据明细------
UPDATE A SET A.IKS=B.IKSBM,A.CKS=B.CKSMC,A.IYS=B.IYSBM,A.CYS=B.CKSMC FROM YXHIS2018..TBMZFYMXGH201801 A ,YXHIS2018..TBMZGHMX201801 B WHERE A.CKS='' AND A.CMZH=B.CMZH--------
SELECT CONVERT(VARCHAR(10),DGH,23) SJ,COUNT(*) SL FROM (
SELECT * FROM YXHIS2017..VTBMZGHMX2017
UNION ALL
SELECT * FROM YXHIS2018..VTBMZGHMX2018
) A WHERE BTH=0 GROUP BY CONVERT(VARCHAR(10),DGH,23) HAVING(COUNT(*))>700 ORDER BY CONVERT(VARCHAR(10),DGH,23)select CCZYGH 工号,CMC 姓名,CKSMC 科室名称 from yxhis..TBZDMZYS where cmc in('刘新','袁斌','赵杰聘','李怀斌','高国谦','孙倩','郭耀强','录海斌','汤兵祥','刘新','于洪涛','陈鲁琦','张东铭','刘媛','梁庆正','周志强','胡滨','张予婉','朱宝菊','王芳','王倩','朱峰','赵伟举','余三洋','王仕良','毕巧莲','吴丽','陈红亮','孙延玲','秦石成','刘春','关明智','张长江') AND IBM!='301210'
----------------------------------------------------------
select creator,to_char(createtime,'YYYY-MM-DD')时间,count(*) from ip_advicelog group by creator ,to_char(createtime,'YYYY-MM-DD') ORDER BY to_char(createtime,'YYYY-MM-DD')-------------------------------统计入院证信息-----
select CBZRMC,LEFT(CONVERT(varchar(100), DRYSJ, 23),7) YF,COUNT(CZYH) RS INTO #A From ysgzz..TBRYZXX where CZYH IN (select czyh From (
select *From yxhis..VTBZYBR
union all
select *From yxhis..TBZYBRwjz
union all
select *From yxhis2017..TBZYBR2017
union all
select *From yxhis2018..TBZYBR2018 ) a ) AND CMZKS='疼痛科门诊' AND DRYSJ>='2017-01-01' GROUP BY CBZRMC,LEFT(CONVERT(varchar(100), DRYSJ, 23),7)ORDER BY CBZRMC DESC,LEFT(CONVERT(varchar(100), DRYSJ, 23),7)select *from yxhis..tbzdks
SELECT *fROM #A
------------------------------------------列转行------------------------------------------------------
declare @sql varchar(8000)select @sql =isnull(@sql+',','')+' SUM(case YF when '''+YF+''' then RS else 0 end) ['+YF+']'
from(select distinct YF from #a)as a 
set @sql='select CBZRMC,'+@sql+' from #a group by CBZRMC'
exec(@sql)
---------------------------------------药库开单科室为空的情况SQL处理脚本------------------------------------------------------
SELECT * FROM YXHIS2018..TBFYMX201803 A,YXHIS2018..TBZYCFHZ201803 B WHERE SUBSTRING(CDJH,3,12)=B.CCFH AND CSFXM LIKE '%联网划价%' AND B.CKDKSMC=''
UPDATE B SET B.CKDKSBM=A.CKDKSBM ,B.CKDKSMC=A.CKDKSMC FROM YXHIS2018..TBFYMX201803 A,YXHIS2018..TBZYCFHZ201803 B WHERE SUBSTRING(CDJH,3,12)=B.CCFH AND CSFXM LIKE '%联网划价%' AND B.CKDKSMC=''.-------查询门诊以及住院医技科室PACS登记HIS未收费的情况------------------------
SELECT * FROM YSGZZ..VTBMZJCSQDXXWZX WHERE ISFZT=0 AND IZXZT=1 
SELECT * FROM YSGZZ..VTBMZJCSQDXXWZX WHERE ISFZT=0 AND IZXZT<>3 AND IZXZT<>4
SELECT * FROM YSGZZ..VTBZYJCSQDXXWZX WHERE ISFZT=0 AND IZXZT=2 -------在院病人按月份科室进行统计------------
SELECT CZYKS ,CONVERT(VARCHAR(7),DRYSJ,23),COUNT(*) FROM YXHIS..VTBZYBR WHERE BDD=0 GROUP BY CZYKS,CONVERT(VARCHAR(7),DRYSJ,23) ORDER BY CONVERT(VARCHAR(7),DRYSJ,23)-------截取时间段函数---------------------------------------
datediff(day,drysj,DCYSJ)-------转换时间段函数---------------------------------------
CONVERT(VARCHAR(7),字段名,23)-------护士取消临时药品找不到问题------
Select * from TBZYYZYPBQ104 where CZYH='18041092' and CYZH in (0001750593,0001750594) 
update TBZYYZYPBQ104 set IZT=0,dzx='' where CZYH='18041092' and CYZH in (0001750593,0001750594) -------------检验科扫条码时出现已执行无法扫描的情况--------
SELECT * FROM YSGZZ_INTF..TBJYSQXM WHERE CTMH='201801548931'
UPDATE YSGZZ_INTF..TBJYSQXM SET ISQZT=NULL WHERE CTMH='201801548931'
SELECT * FROM YXLIS2018..TBCZYRZ05 WHERE CEvent LIKE '%201801548931%' ---操作记录表.---------------------------------
--读取库中的所有表名
select * from yxhis..sysobjects where xtype='u'
union all
select * from ysgzz..sysobjects where xtype='u'--读取指定表的所有列名
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='tbicxx')------------------------------加字段----------------------------
Use ySGZZ
GOif not exists (select * from syscolumns where id = object_id('TBZYBRINDEX') and name = 'CYSR')
 alter table TBZYBRINDEX add CYSR varchar(30)
GO----------------------------web版程序地址---
SELECT * FROM AHISTER..TBSYSNO----------------------------病案上报费用问题查询-------
SELECT * FROM YXHIS..TBZDYLXM WHERE CBATJ2011='310' AND CBATJXL<>'' AND CBATJXL<>'311'SELECT * FROM YXHIS..TBZDYLXM WHERE CBATJXL='311' AND CBATJ2011<>'310' 
----------------------------SQLSERVER 查看当前连接数------------------------
　　Select * from sys.dm_exec_connections --此命令可以看到有多少人在连
　　Select * from sys.dm_exec_sessions --此命令可以看到有多少会话，一个连接可以有多个会话-----------------------------日报语句---------------------------
 SELECT CZYBQ 病区,CZYKS 科室,COUNT(*)在院人数 FROM YXHIS..VTBZYBR WHERE BDD=0 GROUP BY CZYBQ,CZYKS ORDER BY CZYBQ DESC SELECT CZYKS 科室,COUNT(*)在院人数 FROM YXHIS..VTBZYBR WHERE BDD=0 GROUP BY CZYKS order by COUNT(*) desc
----------------------------出现序列号的排序--------------------------------
SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 0)) AS ROWNUM,* FROM YXHIS2018..TBMZGHMX201801 --不排序
SELECT ROW_NUMBER()OVER(ORDER BY DCSNY DESC) AS ROWNUM,* FROM YXHIS2018..TBMZGHMX201801 --排序
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。