select
sum(quan) sum_quan,null sum_hsl,
null sum_qsf,sum(mtquan*quan/100) sum_sfl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='1' and t1.month <='03' and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
临时表A select
sum(quan) quan,null hsl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='04' and t1.month <='06'and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
临时表B
欲返回表A 表B所有字段和行,怎么写连接或者子查询
说的不是很清楚希望高手们说说思路。
sum(quan) sum_quan,null sum_hsl,
null sum_qsf,sum(mtquan*quan/100) sum_sfl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='1' and t1.month <='03' and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
临时表A select
sum(quan) quan,null hsl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='04' and t1.month <='06'and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
临时表B
欲返回表A 表B所有字段和行,怎么写连接或者子查询
说的不是很清楚希望高手们说说思路。
from
(
select
sum(quan) sum_quan,null sum_hsl,
null sum_qsf,sum(mtquan*quan/100) sum_sfl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='1' and t1.month <='03' and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
) temp_a a
natural join
(
select
sum(quan) quan,null hsl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='04' and t1.month <='06'and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
) temp_b b
from(
select
sum(quan) sum_quan,null sum_hsl,
null sum_qsf,sum(mtquan*quan/100) sum_sfl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='1' and t1.month <='03' and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
)a
natural join --这里是自然连接,自动将表A与表B的记录匹配在一起
select
from(
select
sum(quan) quan,null hsl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='04' and t1.month <='06'and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
)b
from(
select
sum(quan) sum_quan,null sum_hsl,
null sum_qsf,sum(mtquan*quan/100) sum_sfl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='1' and t1.month <='03' and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
)a
full join --应该用全连接啊,这是返回所有行的
select
from(
select
sum(quan) quan,null hsl,
from zk_cq_siftproduct t1 inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id where t1.year='2008' and t1.month >='04' and t1.month <='06'and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype
)b
on a.bd_coal_id=b.bd_coal_id
SUM (mtquan * quan / 100) sum_sfl
FROM zk_cq_siftproduct t1 INNER JOIN zk_cq_siftproduct_b t2 ON t1.cq_siftproduct_id =
t2.cq_siftproduct_id
WHERE t1.YEAR = '2008'
AND t1.MONTH BETWEEN '01' AND '03'
AND NVL (t1.dr, 0) = 0
AND NVL (t2.dr, 0) = 0
UNION ALL
SELECT SUM (quan) quan, NULL hsl, NULL, NULL
FROM zk_cq_siftproduct t1 INNER JOIN zk_cq_siftproduct_b t2 ON t1.cq_siftproduct_id =
t2.cq_siftproduct_id
WHERE t1.YEAR = '2008'
AND t1.MONTH BETWEEN '04' AND '06'
AND NVL (t1.dr, 0) = 0
AND NVL (t2.dr, 0) = 0
GROUP BY bd_coal_id, outtype;
同时你的LZ的SQL有问题(你编译过了吗??) FROM 前面还有","(逗号)存在呢!SELECT A.SUM_QUAN, A.SUM_HSL, A.SUM_QSF, A.SUM_SFL, B.QUAN, B.HSL
FROM (SELECT BD_COAL_ID,
OUTTYPE,
SUM(QUAN) SUM_QUAN,
NULL SUM_HSL,
NULL SUM_QSF,
SUM(MTQUAN * QUAN / 100) SUM_SFL
FROM ZK_CQ_SIFTPRODUCT T1
INNER JOIN ZK_CQ_SIFTPRODUCT_B T2 ON T1.CQ_SIFTPRODUCT_ID =
T2.CQ_SIFTPRODUCT_ID
WHERE T1.YEAR = '2008'
AND T1.MONTH >= '1'
AND T1.MONTH <= '03'
AND NVL(T1.DR, 0) = 0
AND NVL(T2.DR, 0) = 0
GROUP BY BD_COAL_ID, OUTTYPE) A
FULL JOIN --应该用全连接啊,这是返回所有行的
SELECT FROM (SELECT BD_COAL_ID, OUTTYPE, SUM(QUAN) QUAN, NULL HSL
FROM ZK_CQ_SIFTPRODUCT T1
INNER JOIN ZK_CQ_SIFTPRODUCT_B T2 ON T1.CQ_SIFTPRODUCT_ID =
T2.CQ_SIFTPRODUCT_ID
WHERE T1.YEAR = '2008'
AND T1.MONTH >= '04'
AND T1.MONTH <= '06'
AND NVL(T1.DR, 0) = 0
AND NVL(T2.DR, 0) = 0
GROUP BY BD_COAL_ID, OUTTYPE) B ON A.BD_COAL_ID =
B.BD_COAL_ID
AND A.OUTTYPE = B.OUTTYPE
,sum(case when t1.month >='01' and t1.month <='03' then mtquan*quan/100) else 0 end) sum_sfl
from zk_cq_siftproduct t1
inner join zk_cq_siftproduct_b t2 on t1.cq_siftproduct_id=t2.cq_siftproduct_id
where t1.year='2008' and t1.month >='01' and t1.month <='06'
and nvl(t1.dr,0)=0 and nvl(t2.dr,0)=0
group by bd_coal_id,outtype