SqlServer 常用命令说明

友恒通有限公司

目 录

第一篇 Sql常用命令的使用 .......................................................................................................... 2

第一节 4条常用命令 .............................................................................................................. 2

一、select 命令 ............................................................................................................... 2 二、delete 命令 .............................................................................................................. 5 三、update 命令 ............................................................................................................ 6 四、insert命令(表必须存在) ......................................................................................... 7 第二节 函数 ............................................................................................................................. 8

一、聚合函数 ................................................................................................................... 8 二、系统函数 ................................................................................................................... 9 三、游标函数 ................................................................................................................. 11 四、日期时间函数 ......................................................................................................... 11 五、数学函数 ................................................................................................................. 12 六、字符串函数 ............................................................................................................. 12 第三节 SET ............................................................................................................................. 13 第四节 控制流语言 ............................................................................................................... 13 第五节 常用系统存储过程 ................................................................................................... 15 第六节 表、存储过程的创建 ............................................................................................... 16 第二篇 如何制作外挂报表 ........................................................................................................... 18

第一节 编写相关的存储过程 ............................................................................................... 18

一、存储过程的标准格式 ............................................................................................. 18 二、报表书写的常见问题 ............................................................................................. 19 三、常用报表的取数方法 ............................................................................................. 26 第二节 制作报表模板 ........................................................................................................... 31

一、页面设置 ................................................................................................................. 32 二、快速制表法 ............................................................................................................. 32 三、手工制表法 ............................................................................................................. 32 四、其他 ......................................................................................................................... 36 第三节 报表外挂 ................................................................................................................... 36

一、关于外挂报表的使用授权 ..................................................................................... 36 二、关于参数提示设置 ................................................................................................. 37 三、关于参数类型设置 ................................................................................................. 37

第三篇 远程控制 ......................................................................................................................... 39

第一节 pcAnywhere的安装 ............................................................................................... 39 第二节 pcAnywhere的设置 ............................................................................................... 41

第一部分 通过局域网对远程计算机的控制 ............................................................... 42 第二部分 通过宽频ADSL对远程计算机的控制 ...................................................... 48 第三部分 通过MODEN对远程计算机的控制 ......................................................... 49 第三节 QQ的远程协助功能 ............................................................................................... 52

第 1 页 共 53 页

友恒通有限公司

第一篇 Sql常用命令的使用

第一节 4条常用命令 一、select 命令

虽然 SELECT 语句的完整语法较复杂，但是其主要的子句可归纳如下： SELECT [DISTINCT] [TOP n] select_list [INTO new_table_name] FROM table_list

[WHERE search_conditions]

[GROUP BY [ALL] group_by_list] [HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

1、select_list描述结果集的列。它是一个逗号分隔的表达式列表。每个表达式同时定义格式（数据类型和大小）和结果集列的数据来源。每个选择列表表达式通常是对从中获取数据的源表或视图的列的引用，但也可能是其它表达式，例如常量或 Transact-SQL 函数。在选择列表中使用 * 表达式指定返回源表中的所有列。

select hzxm,zje-yhje zje,substring(jsrq,1,8) rq ,case when jszt=1 then ”中途结帐” else “出院结帐” end 类型,* from ZY_BRJSK

where jsrq between ‘20050101’ and ‘2005011024’ and ybjszt=2 and jlzt in (0,1,2)

2、 在系统中，可能有多个对象带有相同的名称。例如，ZY_BRJSK 和 ZY_BRSYK都指定了syxh等列。若要解析多义性并且指定 syxh为 ZY_BRJSK 所有，请至少使用用户 ID 来限定表的名称，如：ZY_BRJSK.syxh。

或者使用别名，如table_name AS table alias和table_name table_alias

如：

select a.*

from ZY_BRSYK as a,ZY_BRJSK b where a.syxh=b.syxh

and b.jsrq between ‘20050101’ and ‘2005011024’ and ybjszt=2 and jlzt in (0,1,2)

3、以 @local_variable = expression 形式的变量赋值，如果 SELECT 语句返回多个值，

第 2 页 共 53 页

友恒通有限公司

则将返回的最后一个值赋给变量。如果 SELECT 语句没有返回行，变量将保留当前值。 declare @syxh int select @syxh=syxh from ZY_BRJSK where xh=@xh

4、 DISTINCT 关键字可从结果集中除去重复的行。 select ksdm,count(distinct blh) rs from VW_MZBRJSK

where sfrq between '20031201' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) group by ksdm

5、TOP 关键字指定返回结果集的前 n 行。TOP n [PERCENT]，返回前n%的记录 select top 10 ghdwmc 供货单位,sum(jjje) as 进价金额 from YK_YPRKZD

where jzrq between '20040101' and '2004011024' and jzbz in (1,2) and gzbz=0 group by ghdwmc

order by 进价金额 desc

注：如果查询包含 ORDER BY 子句，将输出由 ORDER BY 子句排序的前 n 行（或前百分之 n 行）

6、INTO new_table_name

指定使用结果集来创建新表。new_table_name 指定新表的名称。

创建一个使用 IDENTITY 属性的新列(select into )，注意列名的4种写法

select identity(int,100,1) as 序号,blh \病历号\病人姓名,性别=sex into #temp from ZY_BRSYK

where ryrq between '20040220' and '2005022124'

7、 From: 指定在 DELETE、SELECT 和 UPDATE 语句中使用的表、视图、派生表和连接表。 如果表或视图存在于同一服务器的其它数据库中，应按格式 database.owner.object_name如果表或视图存在于本地服务器之外的一台链接的服务器上，应按以下格式使用由四部分组成的名称：linked_servername.database.owner.object_name

与其他服务器的连接：sp_addlinkedserver 其他服务器名。

视图提示只能用在 SELECT 语句中，而不能用于 UPDATE、DELETE 或 INSERT 语句。 Where 中 *= , =*的使用

8、WHERE 子句是一个筛选，它定义了源表中的行要满足 SELECT 语句的要求所必须达到的条件。只有符合条件的行才向结果集提供数据。不符合条件的行中的数据不会被使用。 1）Between ?and ?.的使用

第 3 页 共 53 页

友恒通有限公司

Where zje between 0 and 500 [0,500] 等同于zje>=0 and zje <=500 Where zje not between 0 and 500 >500 or <0

2）常用子查询的语句

? ?

WHERE expression [NOT] IN (subquery) WHERE [NOT] EXISTS (subquery)

Select * from ZY_BRJSK

where jsrq between '20031201' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) and ybdm in (select ybdm from YY_YBFLK where pzlx='12') 或：

Select *

from ZY_BRJSK a

where jsrq between '20031201' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) and exists (select 1 from YY_YBFLK b where a.ybdm=b.ybdm and pzlx='12')

3） Like /not like ,通配符：%、_、[]、[^] Select * from YK_YPRKZD

Where rkrq like '20050101%'

9、GROUP BY 子句和 ALL 关键字

只有在 SELECT 语句还包括 WHERE 子句时，ALL 关键字才有意义。

列出所有进过货的单位：

select ghdwmc 供货单位,sum(jjje) as 进价金额 from YK_YPRKZD

where jzrq between '20040101' and '2004011024' and jzbz in (1,2) and gzbz=0 group by all ghdwmc 列出满足条件的单位：

select ghdwmc 供货单位,sum(jjje) as 进价金额 from YK_YPRKZD

where jzrq between '20040101' and '2004011024' and jzbz in (1,2) and gzbz=0 group by ghdwmc

10、使用 HAVING 子句选择行

WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。 GROUP BY 子句用来分组 WHERE 子句的输出。 HAVING 子句从中间结果集对行进行筛选。

带聚合函数的 HAVING 子句 select hzxm,sum(zje)

第 4 页 共 53 页

友恒通有限公司

from ZY_BRJSK

where jsrq between '20050101' and '2005011024' and ybjszt=2 and jlzt in (0,1,2) group by hzxm

having sum(zje)>10000

不带聚合函数的 HAVING 子句 SELECT hzxm FROM ZY_BRJSK

where jsrq between '20050101' and '2005011024' and ybjszt=2 and jlzt in (0,1,2) GROUP BY hzxm

HAVING hzxm LIKE '张%'

11、order by ?. [ ASC | DESC ]

对结果集排序。ASC 和 DESC 关键字用于指定行是按升序还是按降序排序。使用union时，只能对最后结果排序。

12、union

UNION 运算符使您得以将两个或多个 SELECT 语句的结果组合成一个结果集。使用 UNION 组合的结果集都必须具有相同的结构。而且它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。

UNION 运算符从结果集中删除重复的行。如果使用 ALL 关键字，那么结果中将包含所有行并且将不删除重复的行。

Select hzxm,sum(zje) zje from ZY_BRJSK

where jsrq between '200312101' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) group by hzxm union

Select '合计',sum(zje) from ZY_BRJSK

where jsrq between '200312101' and '2003121024' and ybjszt=2 and jlzt in (0,1,2) order by sum(zje)

二、delete 命令

DELETE 语法的简化形式为： DELETE table_or_view [FROM table_sources ] [WHERE search_condition ]

1、删除全部行

Delete #temp = TRUNCATE TABLE #temp

如果要删除在表中的所有行，则 TRUNCATE TABLE 比 DELETE 快。DELETE 以物理

第 5 页 共 53 页

友恒通有限公司

方式一次删除一行，并在事务日志中记录每个删除的行。TRUNCATE TABLE 则释放所有与表关联的页。因此，TRUNCATE TABLE 比 DELETE 快且需要的事务日志空间更少。

注意drop table 与delete 的区别

2、 在行集上使用delete delete from #temp where …

3、在游标的当前行上使用 DELETE

下例显示在名为 cs_dxmdm 的游标上所做的删除。它只影响当前从游标提取的单行。

DELETE FROM #dxmdm

WHERE CURRENT OF cs_dxmdm

4、据与关联表、子查询上使用delete delete ZY_BRSYK

from ZY_BRSYK a,ZYB_BRYJK b where a.syxh=b.syxh and a.brzt=9

三、update 命令

简单格式：

update table_name set 列=表达式 [FROM table_sources] [WHERE search_condition]

1、 使用简单的update

UPDATE YF_YFZKC SET djsl = 0

2、 把 WHERE 子句和 UPDATE 语句一起使用

UPDATE YF_YFZKC SET jxje=0

Where abs(jxje)>1000000

3、通过 UPDATE 语句使用来自另一个表的信息 update #temp

set ksmc =b.name

from #temp a,YY_KSBMK b where a.ksdm=b.id

第 6 页 共 53 页

友恒通有限公司

4、 在游标的当前行上使用update

update #dxmdm set zje=@zje

WHERE CURRENT OF cs_dxmdm

四、insert命令(表必须存在)

所提供的数据值必须与列的列表匹配。数据值的数目必须与列数相同，每个数据值的数据类型、精度和小数位数也必须与相应的列匹配。 1、使用 INSERT...SELECT 插入多行

insert #mzybtemp (hzxm,pzh,jzks,jzrq,jzcs,zje,ybdm,sjh,zzbz,sfzh,zddm,bjqk) select hzxm,convert(varchar(17),substring(cardno,1,10)),ksdm,

substring(sfrq,1,8),1,zje-zfyje-yhje,ybdm,sjh,substring(zhbz,1,1), substring(sfzh,1,18),zddm,substring(zhbz,2,1) from VW_MZBRJSK a (nolock)

where sfrq between @ksrq and @jssj and ybjszt=2 and ghsfbz in (0,1)

and substring(zhbz,4,1)='0' and substring(zhbz,12,1)='0' and exists(select 1 from YY_YBFLK b (nolock)

where b.ybdm=a.ybdm and b.pzlx=10)

2、 使用 INSERT...Values 插入一行。

如果没有指定列的列表，指定值的顺序必须与表或视图中的列顺序一致。 insert into #temp(syxh,jsxh) values (@syxh,@jsxh)

3、SET IDENTITY_INSERT 表 ON|OFF 允许将显式值插入表的标识列中

如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40)) GO

-- Inserting values into products table.

INSERT INTO products (product) VALUES ('screwdriver') INSERT INTO products (product) VALUES ('hammer') INSERT INTO products (product) VALUES ('saw') INSERT INTO products (product) VALUES ('shovel') GO

--删除第三行 DELETE products

第 7 页 共 53 页

友恒通有限公司

WHERE product = 'saw' GO

-- 试图插入id=3的记录,将报错

INSERT INTO products (id, product) VALUES(3, 'garden shovel') GO

-- SET IDENTITY_INSERT to ON时,能插入id=3的记录. SET IDENTITY_INSERT products ON GO

INSERT INTO products (id, product) VALUES(3, 'garden shovel'). GO

SET IDENTITY_INSERT products OFF GO

第二节 函数 一、聚合函数

SUM、AVG、COUNT、MAX 和 MIN 忽略空值，而 COUNT(*) 不忽略。

1、 count()

COUNT(*) 返回组中项目的数量。它对每行分别进行计数，包括含有空值null的行。

COUNT(ALL expression)=count(expression) 对组中的每一行都计算 expression 并返回非空值的数量。

COUNT(DISTINCT expression) 对组中的每一行都计算 expression 并返回唯一非空值的数量。

select count(*) 总行数,

count(zlf_pt) 有值的行数, count(all zlf_pt) 有值的行数,

count(distinct zlf_pt) 不重复的行数 from YY_KSBMK

总行数 有值的行数 有值的行数 不重复的行数 ----------- ----------- ----------- ----------- 205 61 61 3

2、 sum()

sum(ALL expression)=sum(expression) 对所有的非空的值求和 sum(DISTINCT expression) 返回唯一非空值的和 例:

create table #temp (aa int,bb money) insert #temp values(1,null)

第 8 页 共 53 页

友恒通有限公司

insert #temp values(2,2) insert #temp values(3,2) insert #temp values(4,3)

select sum(bb),sum(all bb),sum(distinct bb) from #temp

7.0000 7.0000 5.0000

3、 avg()

avg(ALL expression)=avg(expression) 对所有的非空的值求平均值 avg(DISTINCT expression) 返回唯一非空值的平均值

create table #temp(aa int) insert #temp values (null) insert #temp values (20) insert #temp values (30) insert #temp values (30)

select avg(aa) from #temp --26 select avg(distinct aa) from #temp --25 4、 max()、min()

二、系统函数

1、@@IDENTITY

@@IDENTITY 中包含此语句产生的最后的标识值。若此语句没有影响任何有标识列的表，则 @@IDENTITY 返回 NULL。若插入了多个行，则会产生多个标识值，@@IDENTITY 返回最后产生的标识值。

insert into SF_MZCFK(jssjh, hjxh, czyh, lrrq, patid, hzxm, ybdm, zje,zfyje,yhje,zfje)

select @sjh, @hjxh, @czyh, @now, patid, hzxm, @ybdm, @zjecf,@zfyjecf,@yhjecf,@zfje from #brxxk

if @@error<>0 or @@rowcount=0 begin

select \保存收费处方出错！\ return end

select @xhtemp=@@identity

insert into SF_CFMXK(cfxh, cd_idm, gg_idm, dxmdm, ypmc, ypdm, ypdw, dwxs, ykxs, ypfj, ylsj, ypsl, ts, cfts, zfdj, yhdj)

select @xhtemp, idm, gg_idm, dxmdm, ypmc, xxmdm, ypdw, dwxs, ykxs, ypfj, ylsj, fysl, 1, cfts, zfdj, yhdj

from #sfmx where cfxh=@cfxh

第 9 页 共 53 页

友恒通有限公司

if @@error<>0 begin

select \保存收费处方明细出错！\ return end

2、 @@ERROR

Transact-SQL 语句的执行时，如果语句执行成功，则 @@ERROR 设置为 0。若出现一个错误，则返回一条错误信息。@@ERROR 返回此错误信息代码，直到另一条 Transact-SQL 语句被执行。 update SF_MZCFK set jlzt=0, lrrq=(case when @jsrq='' then @now else @jsrq end), czyh=@czyh where jssjh=@sjh if @@error<>0 begin select \更新门诊处方信息出错！\ return end

8、 @@ROWCOUNT

返回受上一语句影响的行数, 直到另一条 Transact-SQL 语句被执行。 任何不返回行的语句将这一变量设置为 0 . Select @pzlx=pzlx

from YY_YBFLK (nolock) where ybdm=@ybdm

if @@rowcount=0 or @@error<>0 begin

select \患者费用类别不正确！\ return end

9、 CAST 和 CONVERT

CAST ( expression AS data_type )

CONVERT (data_type[(length)], expression [, style])

将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。

第 10 页 共 53 页

友恒通有限公司

三、游标函数

@@FETCH_STATUS

@@FETCH_STATUS返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。0表示FETCH 语句成功。

declare @sqlstr varchar(8000), @sqlstr4 varchar(8000), @sqlsum varchar(8000), @sqltitle varchar(8000), @dxmdm ut_kmdm, @dxmmc ut_mc16

declare cs_sfdxm cursor for select id,name from YY_SFDXMK for read only

select @sqlstr='create table #temp(ksdm ut_ksdm not null,ksmc ut_mc32 not null,' select @sqltitle='',@sqlstr4='',@sqlsum=''

open cs_sfdxm

fetch cs_sfdxm into @dxmdm,@dxmmc while @@fetch_status=0 begin select @sqlstr=@sqlstr+'id'+@dxmdm+' numeric(14,2) default 0,' select @sqlstr4=@sqlstr4+'id'+@dxmdm+'+' select @sqltitle=@sqltitle+'id'+@dxmdm+' \ select @sqlsum=@sqlsum+'sum(id'+@dxmdm+'),' fetch cs_sfdxm into @dxmdm,@dxmmc end

close cs_sfdxm

deallocate cs_sfdxm

四、日期时间函数

1、getdate()

返回当前系统日期和时间

select convert(char(30),getdate(),102) select convert(char(30),getdate(),111) select convert(char(30),getdate(),112) select convert(char(30),getdate(),120) select convert(char(30),getdate(),108)

2005.02.28 2005/02/28 20050228 2005-02-28 21:44:36 20:45:23

2、DATEADD ( datepart , number, date )

第 11 页 共 53 页

友恒通有限公司

select dateadd(day,5,'20050110')

2005-01-15 00:00:00.000

3、DATEDIFF ( datepart , startdate , enddate )

select datediff(day,'20050102','20050110')

8

五、数学函数

SELECT ROUND(150.75, 0) SELECT ROUND(150.75, 0, 1) Select Abs(-23.23)

151.00

150.00—最后的1(即非0将截断表达式) 23.23

六、字符串函数

1、SUBSTRING ( expression , start , length)

select substring(sfrq,1,4)+'年'

+substring(sfrq,5,2)+'月' +substring(sfrq,7,2)+'日' from VW_MZBRJSK

where sfrq between '20050101' and '2005010124' and ybjszt=2 and jlzt in (0,1,2)

2、LEFT ( character_expression , integer_expression )

=substring(character_expression ,1, integer_expression )

3、ltrim、rtrim

4、len返回给定字符串表达式的字符（而不是字节）个数，其中不包含尾随空格。

select len(hzxm), datalength(rtrim(hzxm)),hzxm from ZY_BRSYK 2 3 3 2

4 6 6 4

吴莹 周长生 蔡丽君 钱慧

5、CHARINDEX ( expression1 , expression2)

返回字符串中指定表达式的起始位置, 如果在 expression2 内没有找到 expression1，则 CHARINDEX 返回 0。

select a.sjh,a.blh,b.ysdm,a.ybdm,a.zje from VW_MZBRJSK a(nolock)

where a.sfrq between @ksrq and @jsrq+'24' and a.ybjszt=2

and exists(select 1 from YY_YBBBK b

第 12 页 共 53 页

友恒通有限公司

where charindex('\and b.id = '31') 第三节 SET

1、SET IDENTITY_INSERT table_name ON | OFF 允许将显式值插入表的标识列中。

2、SET NOCOUNT ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。当

SET NOCOUNT 为 OFF 时，返回计数。

3、SET ROWCOUNT { number | @number_var }

在返回指定的行数之后停止处理查询。 若要关闭该选项（以便返回所有的行），请将 SET ROWCOUNT 指定为 0，否则直到遇到SET ROWCOUNT命令为止。

如果行数的值较小，则 SET ROWCOUNT 替代 SELECT 语句 TOP 关键字。

第四节 控制流语言

1、 BEGIN 和 END 定义一系列一起执行的 Transact-SQL 语句。 if @@error<>0 begin rollback tran select @errmsg=\初始化新增药品的台帐时出错！\ return end

2、IF Boolean_expression

{ sql_statement | statement_block } [ ELSE

{ sql_statement | statement_block } ]

如果在 IF...ELSE 块的 IF 区和 ELSE 区都使用了 CREATE TABLE 语句或 SELECT INTO 语句，那么 CREATE TABLE 语句或 SELECT INTO 语句不能指向相同的表名。

3、GOTO 定义标签： label : 改变执行： GOTO label

第 13 页 共 53 页

友恒通有限公司

将执行流变更到标签处。跳过 GOTO 之后的 Transact-SQL 语句，在标签处继续处理。GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。GOTO 语句可嵌套使用。 不论是否使用 GOTO 语句，标签均可作为注释方法使用。

4、WHILE Boolean_expression

{ sql_statement | statement_block } [ BREAK ]

{ sql_statement | statement_block } [ CONTINUE ]

只要指定的条件为真，就重复执行语句。

可以使用 BREAK 和 CONTINUE 关键字或其他改变表达式值的方法在循环内部控制 WHILE 循环中语句的执行。

5、case函数

select sum(case when jlzt in(0,1) then 1 when jlzt in (2) then -1 else 0 end) 交易笔数 from VW_MZBRJSK

where sfrq between @ksrq and @jsrq+'24' and ybjszt=2 and jlzt in (0,1,2)

或：

select sum(case jlzt when 0 then 1 when 2 then 1 when 1 then -1 else 0 end) 结算笔数 from ZY_BRJSK

where jsrq between '20041201' and '2005030124' and ybjszt=2 and jlzt in (0,1,2)

6、/*...*/（注释）

--（注释）

7、DECLARE @local_variable

在批处理或过程的正文中用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值。游标变量可通过该语句声明，并且可用在其它与游标相关的语句中。所有变量在声明后均初始化为 NULL。

Declare @ksrq ut_rq8,@jsrq ut_rq8

Declare cs_dxmdm cursor for select id,name from YY_SFDXMK

8、 execute或exec

执行用户定义函数、系统过程、用户定义存储过程或扩展存储过程。

在执行存储过程时，如果语句是批处理中的第一个语句，则不一定要指定 EXECUTE 关键字。

exec usp_sf_bb_kssrtj '20050101','20050131' declare @errmsg varchar(50)

exec usp_yy_gxtz 1, 0, '4001', 0, '200110', @errmsg output exec('drop table '+@@tablename+')'

第 14 页 共 53 页

友恒通有限公司

第五节 常用系统存储过程

1、 sp_who

提供关于当前 Microsoft? SQL Server? 用户和进程的信息。

列 spid ecid 数据类型 smallint smallint 系统进程 ID。 与指定 SPID 相关联的给定线程的执行上下文 ID。 ECID = {0, 1, 2, 3, ...n}，其中 0 始终表示主或父线程，并且 {1, 2, 3, ...n} 表示子线程。 进程状态。 与特定进程相关联的登录名。 每个进程的主机或计算机名。 如果存在阻塞进程，则是该阻塞进程的系统进程 ID。否则该列为零。 进程使用的数据库。 为此进程执行的 SQL Server 命令（Transact-SQL 语句、SQL Server 内部引擎处理等）。 描述 status loginame hostname blk nchar(30) nchar(128) nchar(128) char(5) dbname cmd nchar(128) nchar(16) KILL 命令通常用于终止这样一些进程，它们锁住了其它重要进程，或者正在执行一个查询，而该查询正在使用必需的系统资源。

2、 sp_depends

显示有关数据库对象相关性的信息（例如，依赖表或视图的视图和过程，以及视图或过程所依赖的表和视图）。

sp_depends YY_KSBMK

3、 sp_help

报告有关数据库对象（sysobjects 表中列出的任何对象）、数据类型、用户定义数据类型的信息。

Sp_help YY_KSBMK 列出表结构、索引等 Sp_help ut_rq8 列出ut_rq8的数据类型定义

4、 sp_helpindex

sp_helpindex YY_KSBMK 列出YY_KSBMK的索引信息

5、 sp_helptext

显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。 sp_helptext usp_sf_sfcl

第 15 页 共 53 页

友恒通有限公司

6、 sp_password 原密码,新密码,’sa’ 修改sa的密码。如果密码为空，则输入null。

第六节 表、存储过程的创建

1、 create table

create table YK_YPCGZD (

xh ut_xh12 identity(1,1) not null, czyh ut_czyh not null, cjrq ut_rq16 not null, djh ut_sjh null , ksdm ut_ksdm not null, shry ut_czyh null , shrq ut_rq16 null , jlzt ut_bz not null, jzbz ut_bz not null,

memo ut_memo null , constraint PK_YK_YPCGZD primary key (xh) ) go

create index idx_djh on YK_YPCGZD(djh) create index idx_cjrq on YK_YPCGZD(cjrq)

--序号 --操作员 --创建日期 --单据号 --科室代码 --审核员 --审核日期

--记录状态 0录入1审核2作废 --记账标志 0:录入 1：记账 --memo

①IDENTITY

表示新列是标识列。当向表中添加新行时，自动将为该标识列提供一个唯一的、递增的值。标识列通常与 PRIMARY KEY 约束一起用作表的唯一行标识符。可以将 IDENTITY 属性指派给 tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0) 列。对于每个表只能创建一个标识列。不能对标识列使用绑定默认值和 DEFAULT 约束。必须同时指定种子和增量，或者二者都不指定。如果二者都未指定，则取默认值 (1,1)。

②关于自定义类型的说明：

execute sp_addtype ut_bz, 'smallint', null go

create default D_ut_bz as 0 go

execute sp_bindefault D_ut_bz, ut_bz go

execute sp_addtype ut_xh12, 'numeric(12,0)','NOT NULL' go

③CONSTRAINT

第 16 页 共 53 页

友恒通有限公司

是可选关键字，表示 PRIMARY KEY、NOT NULL、UNIQUE、FOREIGN KEY 或 CHECK 约束定义的开始。约束是特殊属性，用于强制数据完整性并可以为表及其列创建索引。

④PRIMARY KEY

是通过唯一索引对给定的一列或多列强制实体完整性的约束。对于每个表只能创建一个 PRIMARY KEY 约束。

⑤CLUSTERED | NONCLUSTERED

是表示为 PRIMARY KEY 或 UNIQUE 约束创建聚集或非聚集索引的关键字。PRIMARY KEY 约束默认为 CLUSTERED，UNIQUE 约束默认为 NONCLUSTERED。 在 CREATE TABLE 语句中只能为一个约束指定 CLUSTERED。

2、 drop table

3、 创建存储过程

CREATE PROC [ EDURE ] procedure_name

[ { @parameter data_type } [ = default ] [ OUTPUT ] ] [ ,...... ] AS

sql_statement

--创建临时存储过程 create proc #temp as

select * from YY_KSBMK

SQL Server 允许创建的存储过程引用尚不存在的对象。

4、 alter proc

更改已存在的存储过程

5、 drop proc

第 17 页 共 53 页

友恒通有限公司

第二篇 如何制作外挂报表

外挂报表的制作一般分三步进行：

第一节 编写相关的存储过程 一、存储过程的标准格式

例：挂号科室统计报表

if exists(select * from sysobjects where name='usp_gh_tybb_ghkstj') drop proc usp_gh_tybb_ghkstj go

create procedure usp_gh_tybb_ghkstj( @ksrq ut_rq8, @jsrq ut_rq8, @sflx ut_dm2 ) as

/**********

[版本号]4.0.0.0.0 [创建时间]2005.02.02 [作者]黄克华

[版权] Copyright ? 1998-2001上海金仕达-卫宁医疗信息技术有限公司 [描述] 挂号科室统计报表 [功能说明]

适合所有医院

增加参数类型“医保代码集”：select id \代码\名称\医保代码集\ 参数Z001:外挂报表金额部分是否包含优惠金额

注意修改时，必须同步修改usp_gh_tybb_ghfbtj(和usp_gh_tybb_ghystj),两者的区别仅在于一个是ksdm/ksmc(和ysdm/ysmc),一个是ybdm/ybsm

[参数说明]

@ksrq 开始日期 @jsrq 结束日期

@sflx 收费类型(-1全部，其他：取结帐模板) [返回值]

第 18 页 共 53 页

友恒通有限公司

[结果集、排序] 成功：结果集

错误：\错误信息\

[调用的sp]

exec usp_gh_tybb_ghkstj '20031201','20031210','-1' [调用实例]

**********/ set nocount on …………. return go

二、报表书写的常见问题

1）存在年表的情况，应该用视图不应该用表 错误写法： select *

from SF_BRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

正确写法： select *

from VW_MZBRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

2）使用聚合函数时，未包含在聚合函数中的列，必须包含再在 GROUP BY 子句中。 错误写法：

select ksdm,ysdm,sum(zje) zje from VW_MZBRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by ksdm

正确写法:

select ksdm,ysdm,sum(zje) zje from VW_MZBRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by ksdm,ysdm

第 19 页 共 53 页

友恒通有限公司

3）在分支中不能多次生成临时表 错误写法： If 取本地表

Select xh,zje Into #temp From SF_BRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) else

Select xh,zje Into #temp From SF_NBRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

正确写法：

create table #temp ( xh int,

zje ut_je14)

if 取本地表

Insert into #temp Select *

From SF_BRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) else

insert into #temp Select *

From SF_NBRJSK

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

4）生成的临时表，应注意数据类型 错误写法：

Select substring(sfrq,1,8) rq,sum(zje) mz_zje,0 zy_zje Into #temp

From VW_MZBRJSK (nolock)

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by substring(sfrq,1,8)

insert into #temp

Select substring(sfrq,1,8),0 mz_zje,sum(zje) zy_zje from ZY_BRJSK (nolock)

where jsrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by substring(jsrq,1,8)

第 20 页 共 53 页

友恒通有限公司

正确写法：

Select substring(sfrq,1,8) rq,sum(zje) mz_zje,convert(numeric(14,2),0) zy_zje Into #temp

From VW_MZBRJSK (nolock)

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by substring(sfrq,1,8)

insert into #temp

Select substring(sfrq,1,8),convert(numeric(14,2),0) mz_zje,sum(zje) zy_zje from ZY_BRJSK (nolock)

where jsrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) group by substring(jsrq,1,8)

5）注意union的写法 错误写法：

insert into #temp select cardno,zje

from SF_BRJSK (nolock)

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) union

select cardno,zje

from SF_NBRJSK (nolock)

where jsrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

正确写法：

insert into #temp select cardno,zje

from SF_BRJSK (nolock)

where sfrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2) union all

select cardno,zje

from SF_NBRJSK (nolock)

where jsrq between @ksrq and @jsrq+’24’ and ybjszt=2 and jlzt in (0,1,2)

6）主从表不能同时使用聚合函数 错误写法：

select ybdm,sum(zyts) zyts,

sum(case when c.ypbz in (1,2,3) then xmje else 0 end) yf

from ZY_BRJSK a (nolock),ZY_BRJSMXK b (nolock),YY_SFDXMK c (nolock) where a.jsrq between @ksrq and @jsrq+’24’ and a.ybjszt=2 and a.jlzt in (0,1,2) and a.xh=b.jsxh and b.dxmdm=c.id

第 21 页 共 53 页

友恒通有限公司

group by ybdm

正确写法：

select * into #brjsk from ZY_BRJSK a (nolock)

where a.jsrq between @ksrq and @jsrq+’24’ and a.ybjszt=2 and a.jlzt in (0,1,2)

create index idx_brjsk_xh on #brjsk(xh)

select ybdm, convert(numeric(6,1),0) zyts,

sum(case when c.ypbz in (1,2,3) then xmje else 0 end) yf into #temp

from #brjsk a (nolock),ZY_BRJSMXK b (nolock),YY_SFDXMK c (nolock) where a.xh=b.jsxh and b.dxmdm=c.id group by ybdm

update #temp set zyts=b.zyts from #brjsk a,

(select ybdm ,sum(zyts) zyts from #brjsk

group by ybdm) b where a.ybdm=b.ybdm

select * from #temp

7）占比的写法 错误写法：

select ybdm,sum(case when c.ypbz in (1,2,3) then xmje else 0 end) yf ,

sum(xmje) zje into #temp

from ZY_BRJSK a (nolock),ZY_BRJSMXK b (nolock),YY_SFDXMK c (nolock)

where a.jsrq between @ksrq and @jsrq+'24' and a.ybjszt=2 and a.jlzt in (0,1,2) and a.xh=b.jsxh and b.dxmdm=c.id group by ybdm

select ybdm,(zje-yf)/zje*100 治疗费占比,yf/zje*100 药占比 from #temp

正确写法：

select ybdm,sum(case when c.ypbz in (1,2,3) then xmje else 0 end) yf ,

sum(xmje) zje into #temp

from ZY_BRJSK a (nolock),ZY_BRJSMXK b (nolock),YY_SFDXMK c (nolock)

where a.jsrq between @ksrq and @jsrq+'24' and a.ybjszt=2 and a.jlzt in (0,1,2) and a.xh=b.jsxh and b.dxmdm=c.id

第 22 页 共 53 页

友恒通有限公司

group by ybdm

select ybdm,

case when zje =0 then 0 else convert(numeric(6,2),(zje-yf)/zje*100) end 治疗占比, case when zje =0 then 0 else convert(numeric(6,2), yf/zje*100) end 药占比 from #temp

8）同一参数如何实现取全部或唯一值

以药房代码为例：(@yfdm='-1' or yfdm=@yfdm)

9）如何写动态报表

一般动态输出大项目、核算项目等才需要使用动态语句：exec(@sqlstr) ①@sqlstr 为字符类型 ②定义大项目、核算项目等 ???

declare cs_sfdxm cursor for

select id,name from YY_SFDXMK order by id for read only select @sqltitle='',@sqlstr4='', @sqltitle='',sqlsum=''

open cs_sfdxm

fetch cs_sfdxm into @dxmdm,@dxmmc while @@fetch_status=0 begin

--表结构

select @sqlstr=@sqlstr+'id'+@dxmdm+' numeric(12,2) default 0,' --大项目、核算项目的合计

select @sqlstr4=@sqlstr4+'id'+@dxmdm+'+' --输出中文名称

select @sqltitle=@sqltitle+'id'+@dxmdm+' \ --每栏求合计

select @sqlsum=@sqlsum+'sum(id'+@dxmdm+'),' fetch cs_sfdxm into @dxmdm,@dxmmc end

close cs_sfdxm

deallocate cs_sfdxm

③@sqlstr中不能使用转换函数，必须转换时只能用游标 ???

declare cs_dxmhz cursor

for select ksdm,xmdm,xmje from #dxmhztmp for read only

declare @ksdm ut_ksdm,@xmdm ut_kmdm,

@xmje numeric(12,2),@xmje1 varchar(16)

第 23 页 共 53 页

友恒通有限公司

open cs_dxmhz

fetch cs_dxmhz into @ksdm,@xmdm,@xmje while @@fetch_status=0 begin

select @xmje1=convert(varchar(16),@xmje)

exec('update '+@tablename+' set id'+@xmdm+'='+@xmje1

+' where ksdm=\

fetch cs_dxmhz into @ksdm,@xmdm,@xmje end

close cs_dxmhz

deallocate cs_dxmhz

10）报表输出的内容

临时表应该建立合适的索引 输出的栏目应该用中文 所有的参数必须都输出

保证小计/合计显示在合适的位置。建议最后按代码排序。

11）关于日期 错误写法一:

create proc usp_test

@ksrq ut_rq8, @jsrq ut_rq8 as

select @ksrq=@ksrq+\select *

from VW_MZBRJSK (nolock)

where sfrq between @ksrq and @jsrq and ybjszt=2 and jlzt in (0,1,2)

错误写法二:

create proc usp_test

@ksrq ut_rq16, @jsrq ut_rq16 as

select *

from VW_MZBRJSK (nolock)

where sfrq between @ksrq and @jsrq+\

正确写法:

create proc usp_test

@ksrq ut_rq8, @jsrq ut_rq8 as

第 24 页 共 53 页

友恒通有限公司

select *

from VW_MZBRJSK (nolock)

where sfrq between @ksrq and @jsrq+\

注意:

日期类型: ut_rq8 8位字符 格式：yyyymmdd

ut_rq16 16位字符 格式：yyyymmddhh:nn:ss

12）关于纵表转横表的写法

select a.sjh,a.cardno,a.hzxm,xjje,zpje,srje,yhje,zje,qkbz,qkje,

convert(numeric(12,2),sum(case when b.lx in ('01') then je else 0 end)) dnzhzf,

convert(numeric(12,2),sum(case when b.lx in ('02','04','07') then je else 0 end)) lnzhzf, convert(numeric(12,2),sum(case when b.lx in ('03','05','08') then je else 0 end)) xjzf1, convert(numeric(12,2),sum(case when b.lx in ('06') then je else 0 end)) tczf, convert(numeric(12,2),sum(case when b.lx in ('09') then je else 0 end)) fjzf

into #temp1

from VW_MZBRJSK a (nolock),VW_MZJEMXK b (nolock) where a.sfrq between '20031201' and '2003120124' and a.ybjszt=2

and a.jlzt in (0,1,2) and a.sjh=b.jssjh

group by a.sjh,a.cardno,a.hzxm,xjje,zpje,srje,yhje,zje,qkbz,qkje

select a.cardno,a.hzxm,

convert(numeric(12,2),sum(dnzhzf)) 当年账户支付, convert(numeric(12,2),sum(lnzhzf)) 历年账户支付, convert(numeric(12,2),sum(xjzf1)) 现金支付1, convert(numeric(12,2),sum(tczf)) 统筹支付, convert(numeric(12,2),sum(fjzf)) 附加支付, convert(numeric(12,2),sum(xjje)) 现金支付, convert(numeric(12,2),sum(zpje)) 支票支付,

convert(numeric(12,2),sum(case when qkbz=1 then qkje else 0 end)) 其他记帐, convert(numeric(12,2),sum(case when qkbz=2 then qkje else 0 end)) 欠款,

convert(numeric(12,2),sum(case when qkbz=3 then qkje else 0 end)) 充值卡支付, convert(numeric(12,2),sum(srje)) 舍入, convert(numeric(12,2),sum(yhje)) 优惠, convert(numeric(12,2),sum(zje)) 总金额 into #temp2 from #temp1 a

group by a.cardno,a.hzxm

注意：

1）纵表转横表的写法

2）要了解#temp1中为何加sjh

第 25 页 共 53 页

本文来源：https://www.bwwdw.com/article/xc52.html