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

[pinuo520]

表结构和数据如下：
/*药品目录*/
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片

[leiao0727]

路过看看。。。心有余而力不足。。。帮忙顶把

[hornbilltofy]

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的函数我忘了

[herrick]

不难啊，我写SQL Server的

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

[哇咔咔]

建个数据窗口，看看语法，稍微改一下就好了

[hygougou]

无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片

[hygougou]

总数量的统计sum

然后group by 主表主键

[princelily]

学识浅薄，但是愿意细心学习！帮顶！

[吹雪风]

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!

[冷箫轻笛]

库存应该不能为负数吧？

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