有两个表
表p1
产品号
X1
X2
C1
C2
V1表p2
产品号 编号 月份 销售额
X1 1001 8 24
X2 1001 8 5
X3 1001 8 53
C1 1002 8 35
C2 1002 8 16
V1 1003 8 66
B1 1004 8 53
X1 1001 9 1
X2 1001 9 55
X3 1001 9 81
C1 1002 9
C2 1002 9 89
V1 1003 9 32
B1 1004 9 39
X1 1001 10 39
X2 1001 10 11
X3 1001 10 85
C1 1002 10 91
C2 1002 10 98
V1 1003 10 44
B1 1004 10 53想实现查询结果表
产品号 编号 同编号10月销售总额 同编号近三月平均销售额
X1 1001 135 39.3
X2 1001 135 39.3
C1 1002 189 65.8
C2 1002 189 65.8
V1 1003 44 47.3先用如下方法:
select a.产品号,a.编号,b. from (select p1.产品号,p2.编号 from p1 left join p2 on (p1.产品号=p2.产品号) a
left join
(select 编号,sum(case when 月份=10 then coalesce(销售额,0) else 0 end) as 同编号10月销售总额,
round(avg(销售额)) as 同编号近三月平均销售额 from p2
where 月份 in (8,9,10)) b
on a.编号=b.编号执行脚本跑出来没有数据,单独执行表a 和 表b 是有数据的,关联起来执行就没有数据,不知道问题出在哪里,求高手指点指点,谢谢!
表p1
产品号
X1
X2
C1
C2
V1表p2
产品号 编号 月份 销售额
X1 1001 8 24
X2 1001 8 5
X3 1001 8 53
C1 1002 8 35
C2 1002 8 16
V1 1003 8 66
B1 1004 8 53
X1 1001 9 1
X2 1001 9 55
X3 1001 9 81
C1 1002 9
C2 1002 9 89
V1 1003 9 32
B1 1004 9 39
X1 1001 10 39
X2 1001 10 11
X3 1001 10 85
C1 1002 10 91
C2 1002 10 98
V1 1003 10 44
B1 1004 10 53想实现查询结果表
产品号 编号 同编号10月销售总额 同编号近三月平均销售额
X1 1001 135 39.3
X2 1001 135 39.3
C1 1002 189 65.8
C2 1002 189 65.8
V1 1003 44 47.3先用如下方法:
select a.产品号,a.编号,b. from (select p1.产品号,p2.编号 from p1 left join p2 on (p1.产品号=p2.产品号) a
left join
(select 编号,sum(case when 月份=10 then coalesce(销售额,0) else 0 end) as 同编号10月销售总额,
round(avg(销售额)) as 同编号近三月平均销售额 from p2
where 月份 in (8,9,10)) b
on a.编号=b.编号执行脚本跑出来没有数据,单独执行表a 和 表b 是有数据的,关联起来执行就没有数据,不知道问题出在哪里,求高手指点指点,谢谢!
select a.产品号,a.编号,b.同编号10月销售总额,b. 同编号近三月平均销售额
from (select p1.产品号,p2.编号 from p1 left join p2 on (p1.产品号=p2.产品号) a
left join
(select 编号,sum(case when 月份=10 then coalesce(销售额,0) else 0 end) as 同编号10月销售总额,
round(avg(销售额)) as 同编号近三月平均销售额 from p2
where 月份 in (8,9,10)) b
on a.编号=b.编号
select 'X2','1001','8','5' from dual union all
select 'X3','1001','8','53' from dual union all
select 'C1','1002','8','35' from dual union all
select 'C2','1002','8','16' from dual union all
select 'V1','1003','8','66' from dual union all
select 'B1','1004','8','53' from dual union all
select 'X1','1001','9','1' from dual union all
select 'X2','1001','9','55' from dual union all
select 'X3','1001','9','81' from dual union all
select 'C1','1002','9','' from dual union all
select 'C2','1002','9','89' from dual union all
select 'V1','1003','9','32' from dual union all
select 'B1','1004','9','39' from dual union all
select 'X1','1001','10','39' from dual union all
select 'X2','1001','10','11' from dual union all
select 'X3','1001','10','85' from dual union all
select 'C1','1002','10','91' from dual union all
select 'C2','1002','10','98' from dual union all
select 'V1','1003','10','44' from dual union all
select 'B1','1004','10','53' from dual)
select n,i,sum(q),avg(q) from (
select name n ,id i ,nvl( qian,0) q from t1 where Yue in ('8','9','10')
)
group by n,i;
N I SUM(Q) AVG(Q)
-- ---- ---------- ----------
B1 1004 145 48.3333333
C1 1002 126 42
C2 1002 203 67.6666666
V1 1003 142 47.3333333
X1 1001 64 21.3333333
X2 1001 71 23.6666666
X3 1001 219 73
如果两个查询本身都有数据,那么看看两边的编号是否能对上,a结果集里的p2对应的记录是否都被 月份 in (8,9,10)过滤掉了
高手,求指点一下错在哪里,我反复检查了几遍,感觉语句没错的说!
两边编号不是全部对的上,有部分是对的上,我用单独取几个数验证过,例如取产品号为X1的,执行 a 这部分的语句,能出来X1的数据,执行 b 这部分的语句,也能出来X1的数据,但是 a 这部分和 b 这部分关联起来,就全为0了,很疑惑为什么会这样?
逻辑错误:近3个月平均销售额,如果用avg,除非每条记录都是一整个月的总和,否则这么求出来不是月平均销售额,而是每次销售的平均金额。 改成 sum(销售额)/count(distinct 月份) 比较合理
关于上面提的没有数据的错误。检查编号字段中是否存在空格之类的空字符,以至于用等号无法匹配
from (select distinct p1.product_no, p2.sequ_no
from p1
inner join p2
on p1.product_no = p2.product_no) a
left join (select sequ_no, sum(sale_amount) amount
from p2
where mon = '10'
group by sequ_no) b
on a.sequ_no = b.sequ_no
left join (select sequ_no, round(avg(sale_amount), 1) avge
from p2
where mon in ('8', '9', '10')
and p2.sale_amount != 0
group by sequ_no) c
on a.sequ_no = c.sequ_no
结果虽然出来了,但是整体看来数据意义不大