时间戳

首页 > 解决方案 > 工会条款用法

sql - 工会条款用法

问题描述

我正在开发一个关于 Oracle 11g 的项目，以便为缓慢移动的项目提供输出。我有 2 个不同的代码来提取数据，它们是 1. 销售额少于 5 的商品 2. 最新交货日期，最新交货数量。我正在考虑使用UNION将这两个代码合并为一个，任何人都可以帮助我。我试过了，但出现错误“查询块的结果列数不正确”。

标签： sqloracleunion

解决方案

如果看这部分：

select  e.str_no,decode(o.ord_send,0,'DC',3,'DD') del, e.goo_no AS goo_no,o.goo_na, 
sum(qty) totsale, (select qty from mkfstomi where goo_no = e.goo_no and str_no = e.str_no) sto

from mkfecrti e, mkfgoomi o , mkfgosmi s
where e.goo_no = o.goo_no and e.goo_no = s.goo_no and e.str_no = s.str_no
and e.str_no = 'TBS2' 
and e.deal_code = '00'  
and nvl(o.stop_pur,'N') <> 'Y' and nvl(s.stop_pur,'N') <> 'Y'--AND OSUNT_NA = 'UNIT'
and o.goo_status <> '9' 
and e.tdate between date '2019-10-01' and date '2019-10-31'
having sum(qty) < 5
group by e.str_no, e.goo_no,o.goo_na ,decode(o.ord_send,0,'DC',3,'DD')

union all

SELECT distinct m.str_no,m.del,  m.goo_no AS Item_Code , m.goo_na, 0 totsale, 0 sto,d2.qty AS Latest_delivery_quantity ,m.max_date AS Latest_delivery

FROM (SELECT j.str_no, k.goo_no,decode(o.ord_send,0,'DC',3,'DD') del, MAX(k.kdate) AS max_date, o.goo_na
FROM MKFTTOTI k, MKFTTO01 h, mkfgoomi o, mkfstrmi j
where k.seq_no=h.seq_no
and k.goo_no=o.goo_no
and k.str_no=j.str_no
and h.tto_code='2F'
and k.qty > 0
and j.str_no='TBS2'
and j.close_date is NULL
and nvl(o.stop_pur,'N') <> 'Y'
GROUP BY k.goo_no,o.goo_na, j.str_no,decode(o.ord_send,0,'DC',3,'DD')) m
JOIN MKFTTOTI d2 ON d2.goo_no = m.goo_no AND d2.kdate = m.max_date and d2.qty > 0

第一部分缺少 d2.qty AS Latest_delivery_quantity ,m.max_date AS Latest_delivery。这意味着您正在做一个联合，其中两个 aprt 的列数不同。

在下面试试这个，它应该可以工作：

select str_no, del, item_code, goo_na, sum(totsale), sum(sto),  sum(Latest_delivery_quantity), Latest_delivery
from 
(select  e.str_no,decode(o.ord_send,0,'DC',3,'DD') del, e.goo_no AS goo_no,o.goo_na, 
sum(qty) totsale, (select qty from mkfstomi where goo_no = e.goo_no and str_no = e.str_no) sto, null as Latest_delivery_quantity ,null AS Latest_delivery
from mkfecrti e, mkfgoomi o , mkfgosmi s
where e.goo_no = o.goo_no and e.goo_no = s.goo_no and e.str_no = s.str_no
and e.str_no = 'TBS2' 
and e.deal_code = '00'  
and nvl(o.stop_pur,'N') <> 'Y' and nvl(s.stop_pur,'N') <> 'Y'--AND OSUNT_NA = 'UNIT'
and o.goo_status <> '9' 
and e.tdate between date '2019-10-01' and date '2019-10-31'
having sum(qty) < 5
group by e.str_no, e.goo_no,o.goo_na ,decode(o.ord_send,0,'DC',3,'DD')
union all
SELECT distinct m.str_no,m.del,  m.goo_no AS Item_Code , m.goo_na, 0 totsale, 0 sto,d2.qty AS Latest_delivery_quantity ,m.max_date AS Latest_delivery
FROM (SELECT j.str_no, k.goo_no,decode(o.ord_send,0,'DC',3,'DD') del, MAX(k.kdate) AS max_date, o.goo_na
FROM MKFTTOTI k, MKFTTO01 h, mkfgoomi o, mkfstrmi j
where k.seq_no=h.seq_no
and k.goo_no=o.goo_no
and k.str_no=j.str_no
and h.tto_code='2F'
and k.qty > 0
and j.str_no='TBS2'
and j.close_date is NULL
and nvl(o.stop_pur,'N') <> 'Y'
GROUP BY k.goo_no,o.goo_na, j.str_no,decode(o.ord_send,0,'DC',3,'DD')) m
JOIN MKFTTOTI d2 ON d2.goo_no = m.goo_no AND d2.kdate = m.max_date and d2.qty > 0
)
group by str_no, del,item_code, goo_na
;

推荐阅读