语句
select t1.scrq,substr(T2.CNAME,0,instr(T2.CNAME,'注')-1) 站名,T1.BBH,T1.RBCKSL,T1.RYDL,T1.BCKYL,T1.GXYL
from dmba01 T1,SJZD T2 where TO_CHAR(SCRQ,'YYYYMMDD')='20100504' AND T1.ZM='ZS01' AND T1.ZM=T2.DM
结果为
2010-5-4 杏十五一 1
2010-5-4 杏十五一 2 7586 38520 14.20 14.10
2010-5-4 杏十五一 3
2010-5-4 杏十五一 4 想得到结果2# 1# 3# 4# 2010-05-04 杏十五一 7586 38520 14.20 14.10
就是把除了scrq、CNAME、BBH不空的记录中BBH(3个)合并成一个字段查出来
请问怎么写语句
select t1.scrq,substr(T2.CNAME,0,instr(T2.CNAME,'注')-1) 站名,T1.BBH,T1.RBCKSL,T1.RYDL,T1.BCKYL,T1.GXYL
from dmba01 T1,SJZD T2 where TO_CHAR(SCRQ,'YYYYMMDD')='20100504' AND T1.ZM='ZS01' AND T1.ZM=T2.DM
结果为
2010-5-4 杏十五一 1
2010-5-4 杏十五一 2 7586 38520 14.20 14.10
2010-5-4 杏十五一 3
2010-5-4 杏十五一 4 想得到结果2# 1# 3# 4# 2010-05-04 杏十五一 7586 38520 14.20 14.10
就是把除了scrq、CNAME、BBH不空的记录中BBH(3个)合并成一个字段查出来
请问怎么写语句
(
CM VARCHAR2(10) not null,
KM VARCHAR2(10) not null,
ZM VARCHAR2(10) not null,
SCRQ DATE not null,
BGG VARCHAR2(2) not null,
BBH VARCHAR2(2) not null,
LSSZLX VARCHAR2(1) not null,
BCKYL NUMBER(4,2),
BRKYL NUMBER(4,2),
GXYL NUMBER(4,2),
BGYC NUMBER(4,2),
LL NUMBER(4,1),
DL NUMBER(4),
DY NUMBER(5),
GLYS NUMBER(3,2),
JKSW NUMBER(4,2),
CKSW NUMBER(4,2),
WC NUMBER(4,2),
RBCKSL NUMBER(7),
RYDL NUMBER(7),
DJXL NUMBER(5,2),
YXSS NUMBER(6,2),
BX NUMBER(5,2),
BSDH NUMBER(6,4),
LJYXSS NUMBER(9,2),
RYXZSL NUMBER(7),
YXYS VARCHAR2(1),
SBZK VARCHAR2(1)
)
tablespace KY01
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
grant select on DMBA01 to PUBLIC;prompt Disabling triggers for DMBA01...
alter table DMBA01 disable all triggers;
prompt Deleting DMBA01...
delete from DMBA01;
commit;
prompt Loading DMBA01...
insert into DMBA01 (CM, KM, ZM, SCRQ, BGG, BBH, LSSZLX, BCKYL, BRKYL, GXYL, BGYC, LL, DL, DY, GLYS, JKSW, CKSW, WC, RBCKSL, RYDL, DJXL, YXSS, BX, BSDH, LJYXSS, RYXZSL, YXYS, SBZK)
values ('CY05', 'KY01', 'ZS01', to_date('04-05-2010', 'dd-mm-yyyy'), '04', '1', '2', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 37424, null, null, '2');
insert into DMBA01 (CM, KM, ZM, SCRQ, BGG, BBH, LSSZLX, BCKYL, BRKYL, GXYL, BGYC, LL, DL, DY, GLYS, JKSW, CKSW, WC, RBCKSL, RYDL, DJXL, YXSS, BX, BSDH, LJYXSS, RYXZSL, YXYS, SBZK)
values ('CY05', 'KY01', 'ZS01', to_date('04-05-2010', 'dd-mm-yyyy'), '04', '2', '2', 14.2, .05, 14.1, .1, 316.1, 167, 6400, .87, 29, 30.5, 1.5, 7586, 38520, 95, 24, 74.67, 5.08, 20215, null, null, '1');
insert into DMBA01 (CM, KM, ZM, SCRQ, BGG, BBH, LSSZLX, BCKYL, BRKYL, GXYL, BGYC, LL, DL, DY, GLYS, JKSW, CKSW, WC, RBCKSL, RYDL, DJXL, YXSS, BX, BSDH, LJYXSS, RYXZSL, YXYS, SBZK)
values ('CY05', 'KY01', 'ZS01', to_date('04-05-2010', 'dd-mm-yyyy'), '04', '3', '2', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 32028, null, null, '2');
insert into DMBA01 (CM, KM, ZM, SCRQ, BGG, BBH, LSSZLX, BCKYL, BRKYL, GXYL, BGYC, LL, DL, DY, GLYS, JKSW, CKSW, WC, RBCKSL, RYDL, DJXL, YXSS, BX, BSDH, LJYXSS, RYXZSL, YXYS, SBZK)
values ('CY05', 'KY01', 'ZS01', to_date('04-05-2010', 'dd-mm-yyyy'), '04', '4', '2', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 19558, null, null, '2');
commit;
(
DM VARCHAR2(10) not null,
LB VARCHAR2(1) not null,
DNAME VARCHAR2(80) not null,
CNAME VARCHAR2(80) not null
)
insert into SJZD (DM, LB, DNAME, CNAME)
values ('ZS01', 'Z', '杏十五一注水站', '杏十五一注水站');
commit;
SELECT t1.scrq,
SUBSTR(T2.CNAME,0,instr(T2.CNAME,'注')-1) 站名,
wm_concat(T1.BBH),
SUM(T1.RBCKSL),
SUM(T1.RYDL),
SUM(T1.BCKYL),
SUM(T1.GXYL)
FROM dmba01 T1,
SJZD T2
WHERE SCRQ=DATE'2010-05-04'
AND T1.ZM ='ZS01'
AND T1.ZM =T2.DM
GROUP BY t1.scrq,
SUBSTR(T2.CNAME,0,instr(T2.CNAME,'注')-1)
#就是连接上的请问wm_concat函数在oracle9里没有,请问还有别的办法吗
使用wm_concat要有权限的 oracle9i以上都可以使用
WITH tmp AS
(SELECT t1.scrq,
SUBSTR(T2.CNAME,0,instr(T2.CNAME,'注')-1) 站名,
T1.BBH,
T1.RBCKSL,
T1.RYDL,
T1.BCKYL,
T1.GXYL
FROM dmba01 T1,
SJZD T2
WHERE SCRQ=DATE'2010-05-04'
AND T1.ZM ='ZS01'
AND T1.ZM =T2.DM
)
SELECT scrq,
站名,
SUBSTR(MAX(sys_connect_by_path(bbh,',')),2)bbh,
SUM(rbcksl),
SUM(rydl),
SUM(bckyl),
SUM(gxyl)
FROM
(SELECT tmp.*,row_number()OVER(PARTITION BY 站名,scrq ORDER BY bbh)rn FROM tmp
)
START WITH rn=1
CONNECT BY rn=PRIOR rn+1
GROUP BY 站名,
scrq
2# 1# 3# 4# 2010-05-04 杏十五一 7586 38520 14.20 14.10
是空的记录的BBH合并成一个字段(字段b),同字段a,scrq,zm...查出来
(SELECT t1.scrq,
SUBSTR(T2.CNAME,0,instr(T2.CNAME,'注')-1) 站名,
T1.BBH,
T1.RBCKSL,
T1.RYDL,
T1.BCKYL,
T1.GXYL,
CASE WHEN t1.rbcksl IS NULL AND
t1.rydl is null and t1.bckyl is null and t1.gxyl is null then 1 else 0 end flag
FROM dmba01 T1,
SJZD T2
WHERE SCRQ=DATE'2010-05-04'
AND T1.ZM ='ZS01'
AND T1.ZM =T2.DM
)
SELECT scrq,
站名,
SUBSTR(MAX(decode(flag,0,sys_connect_by_path(bbh,','))),2)A,
SUBSTR(MAX(decode(flag,1,sys_connect_by_path(bbh,','))),2)b,
SUM(rbcksl),
SUM(rydl),
SUM(bckyl),
SUM(gxyl)
FROM
(SELECT tmp.*,row_number()OVER(PARTITION BY 站名,scrq,flag ORDER BY bbh)rn FROM tmp
)
START WITH rn=1
CONNECT BY rn=PRIOR rn+1
AND 站名=PRIOR 站名
AND scrq=PRIOR scrq
and flag=prior flag
GROUP BY 站名,
scrq
oracle QQ群:54775466
欢迎爱好者 一起入群探讨