表ss
列 hw 货位
dw 堆位
cx 层数
pm 品名
gg 规格要得出的结果
hw dw co(某堆位层数为1的总和)zs(某堆位的总和) pm gg(该品名和规格是某堆位最高层货物的品名和规格)
我是分3个sql语句写的
下面是第一个,得出 结果是 hw(货位)dw(堆位)co(该堆位底层总和)zs(该堆位总和)select hw dw sum(co)as co,sum(zs) as zs from
((select hw,dw,count(*) as co,0 as zs from ss,dual where cx=1 group by hw,dw)
union
(select hw,dw,0 as co,count(*) as zs from ss,dual group by hw,dw))
group by hw,dw
然后根据 上面的货位和堆位 写出第二个sql语句,得到某堆位最高层数 (注:2,3条语句都是在第一条语句的for循环里写的)
select max(cx) as cx from ss where hw=:hw and dw=:dw
最后再根据最高层数和货位堆位得到 品名和规格
select pm,gg from ss where hw=:hw and cx=:cx and dw=:dw rownum=1有sql高手能把以上3个sql语句合并成1条吗???? 望高手们能帮帮忙谢谢!!!
列 hw 货位
dw 堆位
cx 层数
pm 品名
gg 规格要得出的结果
hw dw co(某堆位层数为1的总和)zs(某堆位的总和) pm gg(该品名和规格是某堆位最高层货物的品名和规格)
我是分3个sql语句写的
下面是第一个,得出 结果是 hw(货位)dw(堆位)co(该堆位底层总和)zs(该堆位总和)select hw dw sum(co)as co,sum(zs) as zs from
((select hw,dw,count(*) as co,0 as zs from ss,dual where cx=1 group by hw,dw)
union
(select hw,dw,0 as co,count(*) as zs from ss,dual group by hw,dw))
group by hw,dw
然后根据 上面的货位和堆位 写出第二个sql语句,得到某堆位最高层数 (注:2,3条语句都是在第一条语句的for循环里写的)
select max(cx) as cx from ss where hw=:hw and dw=:dw
最后再根据最高层数和货位堆位得到 品名和规格
select pm,gg from ss where hw=:hw and cx=:cx and dw=:dw rownum=1有sql高手能把以上3个sql语句合并成1条吗???? 望高手们能帮帮忙谢谢!!!
解决方案 »
- 关于sys登录sysoper权限不足的问题
- oracle游标 抓狂问题
- 请问一个十分困难的sql
- ORACLE中一个表的主键被删除或者一个没有主键的表加了主键,能否查出是哪个用户操作的?
- Create table 出现ORA-01536:超出表空间的空间限量。但是标空间足够大!
- 小弟写的一个触发器,可是总提示我表名.列无效.请各们帮忙看一下错在哪儿了.
- 9i里的convert()函数
- 关于数据库查询的问题
- 如何在linux下运行oracle客户端的程序?
- rman报错RMAN-03009: failure of Control File and SPFILE
- Oracle连接问题
- 如何使千万级数据量的查询快速
t1.dw,
(select count(1)
from ss t2
where t2.dw = t1.dw
and t2.cx = 1) co,
(select count(1) from ss t3 where t3.dw = t1.dw) zs,
(select max(pm)
from ss t4
where t4.hw = t1.hw
and t4.dw = t1.dw) pm,
(select max(pm)
from ss t5
where t5.hw = t1.hw
and t5.dw = t1.dw) gg
from ss t1
hw dw cx pm gg
a1 2 1 螺纹 16
a1 2 1 螺纹 16
a1 2 1 螺纹 16
a1 2 1 螺纹 16
a1 2 2 螺纹 14
a1 2 2 螺纹 14
a2 1 1 螺纹 18
a2 1 1 螺纹 18下面是我要得到的结果
货位(hw) 堆位(dw) 底层数 总件数 品名(pm) 规格(gg)
a1 2 4 6 螺纹 14
a2 1 2 2 螺纹 18ss表里记录的是每件货物的信息,总和就是某货位堆位里所有货物件数的总和,不知道我这样说下清楚不
select 'a1' hw, 2 dw, 1 cx, '螺纹' pm, 16 gg from dual union
select 'a1' , 2 , 1 , '螺纹' , 16 from dual union
select 'a1' , 2 , 1 , '螺纹' , 16 from dual union
select 'a1' , 2 , 1 , '螺纹' , 16 from dual union
select 'a1' , 2 , 2 , '螺纹' , 14 from dual union
select 'a1' , 2 , 2 , '螺纹' , 14 from dual union
select 'a2' , 1 , 1 , '螺纹' , 18 from dual union
select 'a2' , 1 , 1 , '螺纹' , 18 from dual )
select hw "货位",dw "堆位", sum(decode(cx,1,1,0)) "底层数",max(pm_) "品名",max(gg_) "规格" from
(
select hw, dw, cx,
last_value(pm) over (partition by hw,dw order by hw,dw,cx range between unbounded preceding and unbounded following ) pm_,
last_value(gg) over (partition by hw,dw order by hw,dw,cx range between unbounded preceding and unbounded following ) gg_
from tab_test1
)n group by hw,dw
t1.dw,
(select count(1)
from ss t2
where t2.dw = t1.dw
and t2.cx = 1) co,
(select count(1) from ss t3 where t3.dw = t1.dw) zs,
(select max(pm)
from ss t4
where t4.hw = t1.hw
and t4.dw = t1.dw) pm,
(select max(pm)
from ss t5
where t5.hw = t1.hw
and t5.dw = t1.dw) gg
from ss t1