我现在有一个药品数据表。药品代码(md_no),药品名称(detail),有效到期日(end_date),在库数量(md_number)S01 2010-07-01 10
S01 2010-09-30 20
S02 2010-05-30 5
那我要查询出的数据就是S01 药品在库数量 30(10+20),S02药品在库数量 5.请问这个语句怎么写?select sum(t.md_number),med_no,detail from md_medicine t
where t.med_no='S01'它提示非分组函数,所以我再加group by med_no,detail,但也搜索不出S01药品30的数量。请问语句怎么写?在线等。
S01 2010-09-30 20
S02 2010-05-30 5
那我要查询出的数据就是S01 药品在库数量 30(10+20),S02药品在库数量 5.请问这个语句怎么写?select sum(t.md_number),med_no,detail from md_medicine t
where t.med_no='S01'它提示非分组函数,所以我再加group by med_no,detail,但也搜索不出S01药品30的数量。请问语句怎么写?在线等。
解决方案 »
- VC中向Oracle插入数据问题
- 不显示删除回复显示所有回复显示星级回复显示得分回复 关于dbms_session.set_context无权限调用的问题,解决此问题50元RMB
- 数据库其它表都能操作,只有一张表不能进行操作了,以前是可以的。数据库又不能重起!!!
- 如何从oracle92转到oracle817
- JDBC访问oracle,为什么这样的语句不行:select t.*, '常量字符串' from my_table t?
- 这个sql 怎么写
- 请问各位高手如何获得longrow字段的长度,判断是否为空!!!在线线等待!急!
- 数据库oracle中文问题
- 导入操作问题
- 希望各位高手赐教!!!!希望接触过的朋友谈谈看法!!关于工厂中实时数据采集的方法??
- 在线求教基础问题
- 请教:redhat linux as 4成功安装oracle10g后,无法启动?
select sum(t.md_number),med_no,detail from md_medicine t
where t.med_no='S01'
group by med_no,detail
那只能这样写
select sum(t.md_number),med_no from md_medicine t
where t.med_no='S01'
group by med_no
我现在的语句是如下select rtrim(t.code) code,
rtrim(t.detail) ||
decode(sum(tt.md_number),
null,
'',
',余' || sum(tt.md_number) || '(' || rtrim(tt.unity) || ')') detail
from md_code t, md_medicine tt where ((rtrim(t.b_class) = 'STU') or
((rtrim(t.b_class) = 'MTU' or rtrim(t.b_class) = 'STU') and
rtrim(t.code) = ltrim('STU')))
and rtrim(t.code) = rtrim(tt.med_no(+))
group by t.code,t.detail,tt.md_number,tt.unity其中,我的药品名字是从md_code这个表关连出的,在md_code表中b_class(大分类)为MTU和STU的是我要找的药,总之WHERE条件中写的就是找出我要的药品名称的过程。
md_medicine表是存放药品代码和有效期,在库数量,单位等数据的。不知有没有表达明白。
现在我用这个语句会搜索出,
S01 药品A,余10(盒)
S01 药品A,余17(盒)
S02 药品B,余3(付)
而我想要的是S01 药品A,余17(盒),
where t.med_no='S01'
rtrim(t.detail) ||
decode(sum(tt.md_number),
null,
'',
',余' || sum(tt.md_number) || '(' || rtrim(tt.unity) || ')') detail
from md_code t, md_medicine tt where ((rtrim(t.b_class) = 'STU') or
((rtrim(t.b_class) = 'MTU' or rtrim(t.b_class) = 'STU') and
rtrim(t.code) = ltrim('STU')))
and rtrim(t.code) = rtrim(tt.med_no(+))
group by t.code,t.detail,tt.md_number,tt.unity
晕倒,你把红色的去掉,应该就可以了
rtrim(ta.detail) ||
nvl(sum(ta.md_number),
',余' || sum(ta.md_number) || '(' || rtrim(ta.unity) || ')'
) as detail
from
(select rtrim(t.code) as code,
rtrim(t.detail) as detail,
sum(tt.md_number) as md_number,
rtrim(tt.unity) as unity
from md_code t, md_medicine tt
where ((rtrim(t.b_class) = 'STU') or
((rtrim(t.b_class) = 'MTU' or rtrim(t.b_class) = 'STU') and
rtrim(t.code) = ltrim('STU')))
and rtrim(t.code) = rtrim(tt.med_no(+))
group by t.code,t.detail,tt.md_number,tt.unity) as ta
group by ta.code
rtrim(t.detail) as detail,
sum(tt.md_number) as md_number,
rtrim(tt.unity) as unity
from md_code t, md_medicine tt
where ((rtrim(t.b_class) = 'STU ')
or ((rtrim(t.b_class) = 'MTU '
or rtrim(t.b_class) = 'STU ') and
rtrim(t.code) = ltrim( 'STU ')))
and rtrim(t.code) = rtrim(tt.med_no(+))
group by t.code,t.detail,tt.unity你说这个不行,我想知道这个得到什么结果?就上面这个语句...
rtrim(t.detail) ||
decode(sum(tt.unity),
null, '',
',余' || sum(tt.md_number) || '(' || rtrim(tt.unity) || ')') detail
from md_code t, md_medicine tt where ( (rtrim(t.b_class) = 'STU') or
( (rtrim(t.b_class) = 'MTU' or rtrim(t.b_class) = 'STU') and rtrim(t.code) = ltrim('STU') )
)
and rtrim(t.code) = rtrim(tt.med_no(+))
group by t.code,t.detail,tt.unity
select sum(t.md_number),med_no,detail from md_medicine t
group by med_no,detail
having t.med_no='S01'
detail,
sum(md_number) md_number
from emp_SanGuo
group by rollup (md_no)