db知识点...

一.

select top 5 ypdm from hospital group by ypdm order by sum(ypsl) desc

select top 10 ysdm from hospital where ypdm in(select top 5 ypdm from

hospital group by ypdm order by sum(ypsl) desc)

医生对病人看病开药，每开出一种药都有数量。对应如下关系：

医生工作量(ysdm,ypdm,ypsl).

ysdm：医生代码，

ypdm：药品代码，

ypsl：药品数量

函数依赖：(ysdm,ypdm)->ypsl;主码：(ysdm,ypdm)

求sql语句：

1、用药数量最多的前五名药品代码；

2、在用药数量最多的前五名药品里，列出每一种药用量最多的前十名医生代码。

二.格式化日期

select * from convert(varchar(10),getdate(),112)  20070430
select * from convert(varchar(10),getdate(),120) 2007-04-30
select convert(varchar(10),getdate(),108) 11:23:00

三.让触发器根据使用者需要，随时处于运行或停止状态?

alter table trig_example disable trigger trig1

四.
select a.xx,b.xx from a left join b on a.id = b.id where a.id = 1

select a.xx,b.xx from a left join b on a.id = b.id and a.id = 1

select a1.xx,b.xx from (select a.xx from a where a.id = 1) a1 left join

b on a1.id = b.id

请问以上三个语句哪个效率高?

五.

select ps_name,max(time),top 5 tlevel from b left join a on a.ps_id =

b.ps_id order by tlevel desc

???

六.读写文本文件的存储过程或者函数  

--将某个目录上的Excel表,导入到数据库中

--将所有的Excel文件放到一个目录中,假设为c:\test\,然后用下面的方法来做

create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree 'c:\test',1,1
declare tb cursor for select fn='c:\test'+fname from #t
where isf=1 and fname like '%.xls'  --取.xls文件(EXCEL)
declare @fn varchar(8000)
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
--下面是查询语句,需要根据你的情况改为插入语句
    --插入已有的表用:insert into 表 selct * from ...
    --创建表用:select * into 表 from ...
set @fn='select * from
OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''',全部客户$)'
exec(@fn)
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t

把文本文件导入库中么？
导入文本的格式如下

EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword'