質問内容
質問を評価する
(0ポイント)
|
sqlするツール探してます。
下記のようなSQLを作ってますが、頭がパニックになってきます。
今までcse使ってましたが、ネストがおかしくなりやめました。
SELECT
hdrplan.outstockdate,
hdrplan.base_to,
hdrplan.base_from,
hdrplan.modelsum,
dtldeliver.modeldelicount,
reciept.recieptcount,
hdrplan.taku
FROM
(
SELECT
hdrbase.outstockdate,
hdrbase.base_to,
hdrbase.base_from,
hdrbase.modelsum,
hdrbase.taku
FROM
(SELECT
hdr.outstockdate,
hdr.base_to,
hdr.base_from,
sum(hdr.modelcount_plan) as modelsum
,hdr.taku
FROM
(select control_id,outstockdate,base_to,base_from,modelcount_plan,orderid,
(case
when base_to ='ddd' then orderid
else '1'
end) taku
from control_hdr) hdr
GROUP BY hdr.outstockdate,hdr.base_to,hdr.base_from,hdr.taku) hdrbase
) hdrplan
left join
(
SELECT
hdr.outstockdate,
hdr.base_to,
hdr.base_from,
count(modeldelicountDtl.modelid) as modeldelicount,
hdr.taku
FROM
(SELECT
*
FROM
control_dtl
WHERE
instockdate is not null
) modeldelicountDtl
right join
(select control_id,outstockdate,base_to,base_from,modelcount_plan,orderid,
(case
when base_to ='ddd' then orderid
else '1'
end) taku
from control_hdr) hdr
on (hdr.control_id=modeldelicountDtl.control_id)
GROUP BY hdr.outstockdate,hdr.base_to,hdr.base_from,hdr.taku
) dtldeliver
on (hdrplan.outstockdate=dtldeliver.outstockdate and hdrplan.base_to=dtldeliver.base_to and hdrplan.base_from=dtldeliver.base_from and hdrplan.taku=dtldeliver.taku)
left join
(
SELECT
hdr.outstockdate,
hdr.base_to,
hdr.base_from,
count(modeldelicountDtl.modelid) as recieptcount,
hdr.taku
FROM
(SELECT
*
FROM
control_dtl
WHERE
RECIEPTDATE is not null
) modeldelicountDtl
right join
(select control_id,outstockdate,base_to,base_from,modelcount_plan,orderid,
(case
when base_to ='ddd' then orderid
else '1'
end) taku
from control_hdr) hdr
on (hdr.control_id=modeldelicountDtl.control_id)
GROUP BY hdr.outstockdate,hdr.base_to,hdr.base_from,hdr.taku
) reciept
on (hdrplan.outstockdate=reciept.outstockdate and hdrplan.base_to=reciept.base_to and hdrplan.base_from=reciept.base_from and hdrplan.taku=reciept.taku)
|
