形如
select a.name,b.id from
(select * from table1) a
left join
(select * from table2) b
on
a.id = b.id然后报错
table a doesn't exist.
select a.name,b.id from
(select * from table1) a
left join
(select * from table2) b
on
a.id = b.id然后报错
table a doesn't exist.
sun.CLITCLS_N ,
sun.CFOOD_C ,
sun.CFOOD_N ,
sun.x itemCount,
case when sun.cfood_c is not null then 123 end tableCount,
case when sun.cfood_c is not null then round(sun.x/123,4)*100 end ctr,
case when sun.cfood_c is null
then round(sun.x/(
select sum(case when cfood_c is not null then sun.x else 0 end) from sun
),4)*100
end gdp,
p.item_price salePrice,
money.stockPrice,
case when p.item_price is null then null
when p.item_price = 0 then null
else round((p.item_price-money.stockPrice)/p.item_price,4)
end stockGdp,
money.benchPrice
case when p.item_price is null then null
when p.item_price = 0 then null
else round((p.item_price-money.benchPrice)/p.item_price,4) benchGdp
from
(
select
a.clitcls_c ,
a.CLITCLS_N ,
trim(a.CFOOD_C) CFOOD_C,
c.item_name ,
sum(a.NQTY) x
from
D_T_FOOD_BILLS A
inner join
d_t_food_bill b
on
a.CBILL_C = b.CBILL_C
inner join
D_T2_ITEM_META c
on
a.cfood_c = c.item_id
where
b.DTBILLTIME >= startDate
and
b.DTBILLTIME <= endDate
and
c.item_name not like '%测试%'
and
a.NQTY != 0
group by a.clitcls_c,trim(a.CFOOD_C) with rollup)sun
left join
(
select
t.THERAPY_ID ,
sum(round(t.ITEM_GROSS_COUNT*p.item_price,4)) stockPrice,
sum(round(t.ITEM_GROSS_COUNT*p2.item_price,4)) benchPrice
from
D_T2_ITEM_META m
left join
d_t2_therapy t
on m.ITEM_ID = t.THERAPY_ID
left join
D_T2_ITEM_PRICE p
on t.ITEM_ID = p.ITEM_ID
and p.IS_CURRENT = 1
and p.PRICE_TYPE = 'BENCHMARK'
left join
D_T2_ITEM_PRICE p2
on t.ITEM_ID = p2.ITEM_ID
and p2.IS_CURRENT = 1
and p2.PRICE_TYPE = 'PURCHASE'
where m.ITEM_TYPE = 'PRODUCT'
group by t.THERAPY_ID
)money
on sun.CFOOD_C = money.THERAPY_ID
left join
D_T2_ITEM_PRICE p
on sun.CFOOD_C = p.item_id
where p.price_type = 'SALE'
and sun.x > 0
order by
sun.clitcls_c desc
then round(sun.x/(
select sum(case when cfood_c is not null then sun.x else 0 end) from sun
),4)*100
end gdp,应该是这个case when 里嵌套的的 sun 不存在,在查询的时候还没生成sun, 把下面的关联表再套一层试试
table1
left join
table2
on
table1.id = table2.id