根据3个分组,相同数据显示为空,算出3个小计,最后算出总计create table T_Goods
(
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
) insert into T_Goods values('A01','上衣','红',0,'S',10);
insert into T_Goods values('A01','上衣','红',0,'M',20);
insert into T_Goods values('A01','上衣','白',0,'L',30);
insert into T_Goods values('A01','下衣','红',0,'S',10);
insert into T_Goods values('A01','下衣','白',0,'M',20);
insert into T_Goods values('A01','下衣','黑',0,'L',30);
insert into T_Goods values('A02','上衣','红',0,'S',5);
insert into T_Goods values('A02','上衣','白',0,'M',15);
insert into T_Goods values('A02','下衣','红',0,'S',5);
insert into T_Goods values('A02','下衣','白',0,'M',15);
(
GId varchar2(10) not null,
GName varchar2(20) not null,
GColour varchar2(10),
GWithin int,
GSize varchar2(10),
GNumber int
) insert into T_Goods values('A01','上衣','红',0,'S',10);
insert into T_Goods values('A01','上衣','红',0,'M',20);
insert into T_Goods values('A01','上衣','白',0,'L',30);
insert into T_Goods values('A01','下衣','红',0,'S',10);
insert into T_Goods values('A01','下衣','白',0,'M',20);
insert into T_Goods values('A01','下衣','黑',0,'L',30);
insert into T_Goods values('A02','上衣','红',0,'S',5);
insert into T_Goods values('A02','上衣','白',0,'M',15);
insert into T_Goods values('A02','下衣','红',0,'S',5);
insert into T_Goods values('A02','下衣','白',0,'M',15);
解决方案 »
- 视图的结构中NUMBER类型列DATA_SCALE和DATA_PRECISION的问题
- oracle 9i 不支持子查询中带order by 吗?
- 求在9i的存储过程中二维数组的完整例子及说明,在线等.table ,varrry ,recor都行.
- sql四舍五入问题
- 请问select sum(kk) as mm from table where mm<'88'这句sql对吗??
- 请教高手这个程序问题!
- 请大家比较一下DB2与ORACLE之间的区别,如果应用程序从DB2移植到Oracle的可行性,非常感谢!!
- 请问一个理论问题:应用程序中可以得到Oracle的错误码,这在数据库服务中是怎么实现的
- 查询多张表中不为空的表(江湖救急)
- oracle cmd乱码
- QQ群:“潍坊IT开发者俱乐部”:5778839;开放互助,欢迎高手加入。
- 求个sql语句的写法,关于lead over的
decode(row_number()
over(partition by gid order by gid, gname, gcolour,gsize),
'1',
gid,
'') gid1,
decode(row_number()
over(partition by gid,gname order by gid,gname, gcolour,gsize),
'1',
gname,
'') gname1,
decode(row_number()
over(partition by gid, gname,gcolour order by gid, gname, gcolour,gsize),
'1',
gcolour,
'') gcolour1,
case
when gid is not null and gname is null and gcolour is null then
'gid小计'
when gid is not null and gname is not null and gcolour is null then
'gname小计'
when gid is not null and gname is not null and gcolour is not null and
gsize is null then
'gcolour小计'
when gid is null and gname is null then
'总计'
else
gsize
end gsize1,
sum(gwithin) gwithin,
sum(gnumber) gnumber
from t_goods t
group by rollup(gid, gname, gcolour, (gwithin, gsize, gnumber))
order by gid, gname, gcolour, gsize
select decode(gid,lag(gid) over(order by gid),null,gid) gid,
decode(gname,lag(gname) over(order by gid,gname),null,gname) gname,
decode(gcolour,lag(gcolour) over(order by gid,gname,gcolour),null,gcolour) gcolour,
decode(gsize,null,null,sum(GWITHIN)) GWITHIN,
case
when gsize is null and gid is not null and gname is not null and gcolour is not null then 'gcolour小计'
when gcolour is null and gsize is null and gid is not null and gname is not null then 'gname小计'
when gsize is null and gid is not null and gname is null and gcolour is null then 'gid小计'
when gsize is null and gid is null and gname is null and gcolour is null then '总计'
else gsize end gsize,
sum(GNUMBER) GNUMBER
from T_Goods
group by rollup(gid,gname,gcolour,gsize)
where gid = 'A01' and ...
group by rollup(gid,gname,gcolour,gsize)
--创建视图 将查询结果插入视图
create or replace view view_test as
select decode(gid,lag(gid) over(order by gid),null,gid) gid,
decode(gname,lag(gname) over(order by gid,gname),null,gname) gname,
decode(gcolour,lag(gcolour) over(order by gid,gname,gcolour),null,gcolour) gcolour,
decode(gsize,null,null,sum(GWITHIN)) GWITHIN,
case
when gsize is null and gid is not null and gname is not null and gcolour is not null then 'gcolour小计'
when gcolour is null and gsize is null and gid is not null and gname is not null then 'gname小计'
when gsize is null and gid is not null and gname is null and gcolour is null then 'gid小计'
when gsize is null and gid is null and gname is null and gcolour is null then '总计'
else gsize end gsize,
sum(GNUMBER) GNUMBER
from T_Goods
group by rollup(gid,gname,gcolour,gsize);
--访问视图
select *
from view_test
where ...
create or replace view view_test as
select gid g_id, --方便查询使用
decode(gid,lag(gid) over(order by gid),null,gid) gid,
......select gid,gname,gcolour,gwithin,gsize,gnumber
from view_test
where g_id = 'A01'
我换视图改这段代码为什么报 ORA-12704:字符集不匹配我的代码
select decode(PurSheetguid,lag(PurSheetguid) over(order by PurSheetguid),null,PurSheetguid) PurSheetguid,
decode(sheetid,lag(sheetid) over(order by PurSheetguid,sheetid),null,sheetid) sheetid,
decode(abbrev,lag(abbrev) over(order by PurSheetguid,sheetid,abbrev),null,abbrev) abbrev,
decode(sizedesc,null,null,sum(longdesc)) longdesc,
case
when sizedesc is null and PurSheetguid is not null and sheetid is not null and abbrev is not null then 'gcolour小计'
when abbrev is null and sizedesc is null and PurSheetguid is not null and sheetid is not null then 'gname小计'
when sizedesc is null and PurSheetguid is not null and sheetid is null and abbrev is null then 'gid小计'
when sizedesc is null and PurSheetguid is null and sheetid is null and abbrev is null then '总计'
else sizedesc end sizedesc,
sum(qty) qty
from rr810102
group by rollup(PurSheetguid,sheetid,abbrev,sizedesc)我的视图
create or replace view rr810102 as
selectrownum num,
(select sheetid from PurSheet where guid=a.pursheetid) PurSheetguid,
a.sheetid,e.abbrev,a.createddate,c.goodsid,c.colorid,c.longid,d.goodsno,
(select colorcode from color where id = c.colorid) colorcode,
nvl((select colordesc from goodscolordesc where goodsid = c.goodsid and colorid = c.colorid),(select colordesc from color where id = c.colorid)) colordesc,
(select longdesc from dictlong where id = c.longid) longdesc,
g.id gid,g.SIZEDESC,
(case when g.FILEDNAME='S1' then c.s1
when g.FILEDNAME='S2' then c.s2 when g.FILEDNAME='S3' then c.s3 when g.FILEDNAME='S4' then c.s4
when g.FILEDNAME='S5' then c.s5 when g.FILEDNAME='S6' then c.s6 when g.FILEDNAME='S7' then c.s7
when g.FILEDNAME='S8' then c.s8 when g.FILEDNAME='S9' then c.s9 when g.FILEDNAME='S10' then c.s10
when g.FILEDNAME='S11' then c.s11 when g.FILEDNAME='S12' then c.s12 when g.FILEDNAME='S13' then c.s13
when g.FILEDNAME='S14' then c.s14 when g.FILEDNAME='S15' then c.s15 when g.FILEDNAME='S16' then c.s16
when g.FILEDNAME='S17' then c.s17 when g.FILEDNAME='S18' then c.s18 when g.FILEDNAME='S19' then c.s19
when g.FILEDNAME='S20' then c.s20 when g.FILEDNAME='S21' then c.s21 when g.FILEDNAME='S22' then c.s22
when g.FILEDNAME='S23' then c.s23 when g.FILEDNAME='S24' then c.s24 when g.FILEDNAME='S25' then c.s25
when g.FILEDNAME='S26' then c.s26 when g.FILEDNAME='S27' then c.s27 when g.FILEDNAME='S28' then c.s28
when g.FILEDNAME='S29' then c.s29 when g.FILEDNAME='S30' then c.s30 when g.FILEDNAME='S31' then c.s31
when g.FILEDNAME='S32' then c.s32 when g.FILEDNAME='S33' then c.s33 when g.FILEDNAME='S34' then c.s34
when g.FILEDNAME='S35' then c.s35 when g.FILEDNAME='S36' then c.s36 when g.FILEDNAME='S37' then c.s37
when g.FILEDNAME='S38' then c.s38 when g.FILEDNAME='S39' then c.s39 when g.FILEDNAME='S40' then c.s40
when g.FILEDNAME='S41' then c.s41 when g.FILEDNAME='S42' then c.s42 when g.FILEDNAME='S43' then c.s43
when g.FILEDNAME='S44' then c.s44 when g.FILEDNAME='S45' then c.s45 when g.FILEDNAME='S46' then c.s46
when g.FILEDNAME='S47' then c.s47 when g.FILEDNAME='S48' then c.s48 when g.FILEDNAME='S49' then c.s49
else c.s50 end) qty
from STOCKINSHEET a,
STOCKINSHEETGOODS b,
STOCKINSHEETDETAIL c,
goods d,
channel e,
sizecategory g
where a.guid = b.guid
and a.guid = c.guid
and b.code = c.code(+)
and b.goodsid = d.goodsid(+)
and a.channelid = e.channelid(+)
and d.sizecategoryid=g.sizecategoryid
order by goodsno;字段有num,pursheetguid,sheetid,abbrev,createddate ,goodsid ,colorid ,longid goodsno,colorcode ,colordesc,longdesc ,gid ,sizedesc,qty根据pursheetguid,sheetid,abbrev,goodsno分组算出各qty小计
帮帮忙,马上结贴
查询视图中longdesc和qty是不是都为数字
decode(sizedesc,null,null,sum(longdesc)) longdesc是不是sizedesc和sum()冲突了如果都正确的话 那使用排除法 将查询条件中一个一个去掉 看是哪个点出错 再具体解决
num,pursheetguid,sheetid,abbrev,createddate ,goodsid ,colorid ,longid goodsno,colorcode ,colordesc,longdesc ,gid ,sizedesc,qty
longdesc和qty是数字型
decode(sizedesc,null,null,sum(longdesc)) longdesc, else sizedesc end sizedesc,
sum(qty) qty
from rr810102
group by rollup(PurSheetguid,sheetid,abbrev,sizedesc)
这2段,不知道如何改了
to_char转换一下看看
decode(gsize,null,null,to_char(sum(GWITHIN))) GWITHIN,
else to_char(sizedesc) end sizedesc,