hur.cn - 华软网

 热门搜索

一道考试题,众数据库开发高手,作答,重赏!

  作者:未知    来源:网络    更新时间:2017/9/29
表结构和数据如下:
/*药品目录*/
create table bjcyyb_yp01
(
  ypdm varchar2(20) not null, /*药品代码*/
  ypmc varchar2(100) not null, /*药品名称*/
  ypgg varchar2(40) not null, /*药品规格*/
  ypfl varchar2(2) not null /*药品分类 01 西药 02 中成药 03 草药 */
) tablespace smhis_workspace;
create unique index i_bjcyyb_yp01 on bjcyyb_yp01 (ypdm,ypgg) tablespace smhis_indexspace;
药品代码 药品名称 药品规格 药品分类
001 青霉素 0.5g*1 01
001 青霉素 1.0g*1 01
002 阿斯匹林 0.5g*1 01
003 阿莫西林 0.25g*1 01
004 牛黄降压片 0.5g*20 02

/*药品台账*/
create table bjcyyb_yp28
(
  ypdm varchar2(20) not null, /*药品代码*/
  ypmc varchar2(100) not null, /*药品名称*/
  ypgg varchar2(40) not null, /*药品规格*/
  ypfl varchar(2) not null, /*药品分类 01 西药 02 中成药 03 草药 */
  ysrq date null, /*有效日期*/
  hjsl number(8) not null, /*合计数量*/
  dw varchar2(20) null /*单位*/
) tablespace smhis_workspace;
create unique index i_bjcyyb_yp28 on bjcyyb_yp28(ypdm,ypgg,ysrq) tablespace smhis_indexspace;
药品代码 药品名称 药品规格 药品分类 有效日期 合计数量 单位
001 青霉素 0.5g*1 01 2005/10/01 100 支
001 青霉素 0.5g*1 01 2005/12/01 200 支
001 青霉素 1.0g*1 01 2005/10/20 200 支
002 阿斯匹林 0.5g*1 01 2006/01/01 300 片
004 牛黄降压片 0.5g*20 02 2006/01/01 300 片

根据已知表结构,请用oracle和sql server两种语法分别写出名为v_ypkcsl_oracle和v_ypkcsl_slqserver的视图。要求该视图可以统计出药品目录中每一种不同规格的药品不同有效日期下的库存数量。该视图统计出的结果如下:(视图中字段名与表头一致)


ypdm ypmc ypgg ypfl res
001 青霉素 0.5g*1 01 有效日期为:2005/10/01的库存为100支
001 青霉素 0.5g*1 01 有效日期为:2005/12/01的库存为200支
001 青霉素 1.0g*1 01 有效日期为:2005/10/20的库存为200支
002 阿斯匹林 0.5g*1 01 有效日期为:2006/01/01的库存为300片
003 阿莫西林 0.25g*1 01 库存为0
004 牛黄降压片0.5g*20 02 有效日期为:2006/01/01的库存为300片

------华软网友回答------
路过看看。。。心有余而力不足。。。帮忙顶把
------华软网友回复------
sql server:
select ypdm,ypmc,ypgg,ypfl,'有效日期为:'+convert(char(10),ysrq,112)+'库存为'+sum(hjsl)+dw from bjcyyb_yp28 group by ypdm,ypmc,ypgg,ypfl,ysrq,dw

oracle把'+'改为'||',convert的函数我忘了
------华软网友回复------
不难啊,我写SQL Server的

SELECT ypdm, ypmc, ypgg,ypfl,'有效日期为:' + ysrq + '的库存为' + Cast(SUM(hjsl) AS varchar) + dw AS res
FROM bjcyyb_yp28
GROUP BY ypgg, ysrq

------华软网友回复------
建个数据窗口,看看语法,稍微改一下就好了
------华软网友回复------
库存应该不能为负数吧?

SELECT ypdm, ypmc, ypgg,ypfl,case when hjsl 〉0 then  '有效日期为:' + ysrq + '的库存为' + Cast(SUM(hjsl) AS varchar) + dw else ‘库存为0’end  AS res
FROM bjcyyb_yp28
GROUP BY ypdm, ypmc, ypgg,ypfl,dw
------华软网友回复------
无ORA环境,写SQL语法

create table bjcyyb_yp01
(
  ypdm varchar(20) not null, /*药品代码*/
  ypmc varchar(100) not null, /*药品名称*/
  ypgg varchar(40) not null, /*药品规格*/
  ypfl varchar(2) not null /*药品分类 01 西药 02 中成药 03 草药 */
) ;

create unique index i_bjcyyb_yp01 on bjcyyb_yp01(ypdm,ypgg) ;



insert into bjcyyb_yp01
select '001' ,'青霉素', '0.5g*1' ,'01'
union all
select '001' ,'青霉素', '1.0g*1' ,'01'
union all
select '002', '阿斯匹林' ,'0.5g*1' ,'01'
union all
select '003', '阿莫西林' ,'0.25g*1' ,'01'
union all
select '004' ,'牛黄降压片', '0.5g*20' ,'02'
;


create table bjcyyb_yp28
(
  ypdm varchar(20) not null, /*药品代码*/
  ypmc varchar(100) not null, /*药品名称*/
  ypgg varchar(40) not null, /*药品规格*/
  ypfl varchar(2) not null, /*药品分类 01 西药 02 中成药 03 草药 */
  ysrq date null, /*有效日期*/
  hjsl numeric(8,0) not null, /*合计数量*/
  dw varchar(20) null /*单位*/
)  ;
create unique index i_bjcyyb_yp28 on bjcyyb_yp28(ypdm,ypgg,ysrq)  ;



insert into bjcyyb_yp28
 select '001', '青霉素' ,'0.5g*1','01' , '2005/10/01'  , 100 ,'支'
union all
 select '001', '青霉素' ,'0.5g*1', '01' ,'2005/12/01', 200 ,'支'
union all
 select '001' ,'青霉素' ,'1.0g*1' ,'01' ,'2005/10/20', 200 ,'支'
union all 
select '002' ,'阿斯匹林' ,'0.5g*1' ,'01' ,'2006/01/01', 300 ,'片'
union all 
select '004' ,'牛黄降压片', '0.5g*20' ,'02', '2006/01/01' ,300 ,'片'
;



select a.ypdm as ypdm, a.ypmc as ypmc , a.ypgg as ypgg ,a.ypfl as ypfl, 
case  isnull(b.hjsl,0) when 0 then 
'库存为0'
else
'有效日期为:' + convert(char(10),b.ysrq,103) + '的库存为' + convert(varchar(10),b.hjsl)  + dw 
end as ref
from bjcyyb_yp01 a, bjcyyb_yp28 b 
where a.ypdm *= b.ypdm and a.ypmc *=b.ypmc and a.ypgg *= b.ypgg and a.ypfl *= b.ypfl;

//测试结果
ypdm ypmc ypgg ypfl res
001 青霉素 0.5g*1 01 有效日期为:2005/10/01的库存为100支
001 青霉素 0.5g*1 01 有效日期为:2005/12/01的库存为200支
001 青霉素 1.0g*1 01 有效日期为:2005/10/20的库存为200支
002 阿斯匹林 0.5g*1 01 有效日期为:2006/01/01的库存为300片
003 阿莫西林 0.25g*1 01 库存为0
004 牛黄降压片0.5g*20 02 有效日期为:2006/01/01的库存为300片

------华软网友回复------
总数量的统计sum

然后group by 主表主键
------华软网友回复------
学识浅薄,但是愿意细心学习!帮顶!
------华软网友回复------
select a.ypdm as ypdm, a.ypmc as ypmc , a.ypgg as ypgg ,a.ypfl as ypfl, 
case  isnull(b.hjsl,0) when 0 then 
'库存为0'
else
'有效日期为:' + convert(char(10),b.ysrq,103) + '的库存为' + convert(varchar(10),b.hjsl)  + dw 
end as res
from bjcyyb_yp01 a, bjcyyb_yp28 b 
where a.ypdm *= b.ypdm and a.ypmc *=b.ypmc and a.ypgg *= b.ypgg and a.ypfl *= b.ypfl;


狗狗,不是 case... end as ref , 是 res!      
华软声明:本内容来自网络,如有侵犯您版权请来信指出,本站立即删除。