成本分析报表 ---主页面（sql语句备份）

一.未修改前备份：第一版

SELECT *
FROM
(
SELECT
u.ProductDate,
u.PlanId as remark3,
u.MaterialCode,
p.MaterialName,
p.remark5 as Dunlilunchengben,

u.Ok_Num,
u.No_Num,
u.CompleteNum,
IFNULL(t.InferiorProduct_Num,0) as InferiorProduct_Num,
u.Ok_Num+u.No_Num-IFNULL(t.InferiorProduct_Num,0) as HeGeNum,
p.MaterialPrice,
u.Ok_Num+u.No_Num-IFNULL(t.InferiorProduct_Num,0) * p.MaterialPrice as ProductPrice,
sum(w.remark1) as remark1,
--         CONVERT(SUM(((w.MaterialPrice * w.Remark1)/(p.MaterialWeight*u.CompleteNum/1000))),DECIMAL(15,2)) as remark2,
CONVERT(IFNULL((p.MaterialWeight*IFNULL(u.CompleteNum,0)/1000),0),DECIMAL(18,2)) as remark5
FROM
(
--           第一个    u 是获取产品编码，日期，完成数量，查询的浇筑记录表
SELECT
r.PlanId,
r.ProductId as MaterialCode,
DATE_FORMAT(r.ProductDate,'%Y-%m-%d') as ProductDate,
Sum(r.Total_Ok_Num) as Ok_Num,
Sum(r.Total_No_Num) as No_Num,
Sum(r.Total_Ok_Num+r.Total_No_Num) as CompleteNum
from
bus_plandetail r
<where>
    <if test="startTime!=null and startTime!=''">
        AND  DATE_FORMAT(r.ProductDate,'%Y-%m-%d') &gt;= #{startTime}
    </if>
    <if test="endTime!=null and endTime!=''">
        AND  DATE_FORMAT(r.ProductDate,'%Y-%m-%d') &lt;= #{endTime}
    </if>
</where>

GROUP BY
r.ProductId,
r.PlanId
--              r.ProductDate
--           第一个    u 到这里结束
) u
LEFT JOIN
(
--              第二个 t开始
SELECT
s.PlanId,
COUNT(s.Pk_Id)  as InferiorProduct_Num ,
s.MaterialCode,
s.ProductDate
FROM
bus_imperfection_record s
<where>
    <if test="startTime!=null and startTime!=''">
        AND  DATE_FORMAT(s.ProductDate,'%Y-%m-%d') &gt;= #{startTime}
    </if>
    <if test="endTime!=null and endTime!=''">
        AND  DATE_FORMAT(s.ProductDate,'%Y-%m-%d') &lt;= #{endTime}
    </if>
</where>
group by
s.materialCode,
s.PlanId
--              第二个 t 结束
) t
on u.MaterialCode = t.MaterialCode and u.PlanId = t.PlanId
JOIN
(
--              第三个 w开始
select
bf.Remark3,
DATE_FORMAT(bf.OperateDate,'%Y-%m-%d') as OperateDate,
CONVERT((SUM(bf.Weight/(CASE bm.MaterialUnit WHEN 'g' THEN 1000 ELSE 1 END ))),DECIMAL(10,2)) AS remark1,
bf.MaterialId,
bf.ProductId,
bm.MaterialPrice,
bm.MaterialUnit,
bm.MaterialName
FROM base_material bm
inner join bus_feedingdetail bf
on bm.MaterialId = bf.MaterialId
<where>
    <if test="startTime!=null and startTime!=''">
        AND  DATE_FORMAT(bf.OperateDate,'%Y-%m-%d') &gt;= #{startTime}
    </if>
    <if test="endTime!=null and endTime!=''">
        AND  DATE_FORMAT(bf.OperateDate,'%Y-%m-%d') &lt;= #{endTime}
    </if>
</where>



GROUP BY bf.Remark3,bf.ProductId,bm.MaterialPrice,bm.MaterialName,bm.MaterialUnit
ORDER BY bf.Remark3 DESC
--              第三个 w结束

) w
on u.MaterialCode = w.ProductId and u.PlanId = w.Remark3
JOIN
base_material p
on w.ProductId = p.MaterialId
JOIN base_material bmg
ON w.ProductId = bmg.MaterialId
JOIN bus_plandetail bpd2
on w.ProductId = bpd2.ProductId AND w.Remark3 = bpd2.PlanId

GROUP BY
u.MaterialCode,
p.MaterialName,
u.PlanId,
--         u.ProductDate,
u.Ok_Num,
u.No_Num,
u.CompleteNum,
InferiorProduct_Num,
HeGeNum,
p.MaterialPrice,
ProductPrice

) f
LEFT JOIN
--              这里是一个新的
(
SELECT p.PlanId,
CONVERT(IFNULL((SUM(p.remark1)/SUM(p.Remark2)),0),DECIMAL(18,2)) as remark6,
CONVERT(IFNULL((SUM(p.Dunshijichengben)/SUM(p.Remark2)),0),DECIMAL(18,2)) as remark2
FROM
(SELECT
--         d.ProductDate,
d.PlanId,
d.ProductId,
-- ok+no 生产总量
d.CompleteNum,
-- 添加原料实际消耗成本
CONVERT((d.materialnumweight * d.remark5),DECIMAL(15,2)) as remark1,
-- 当天产成品吨数 materialnumweight
d.materialnumweight as remark2,
d.remark5,
CONVERT((sum(d.materialnumweight * d.remark5) )/(sum(d.materialnumweight)),DECIMAL(18,2)) as Dunlilunchengben,
--         (sum(d.materialnumweight * d.remark5) )/(sum(r.materialnumweight))  as Dunlilunchengben,
d.Dunshijichengben as Dunshijichengben
FROM
--     4.从3表中关联计划明细表，获取完成数量ok-no
(SELECT
c.ProductDate,
c.PlanId,
c.ProductId,
c.MaterialName,
c.MaterialWeight,
(IFNULL(bpd.Total_Ok_Num,0)+IFNULL(bpd.Total_No_Num,0)) AS CompleteNum,
CONVERT(((IFNULL(bpd.Total_Ok_Num,0)+IFNULL(bpd.Total_No_Num,0)) * c.MaterialWeight )/1000,DECIMAL(15,2))  as materialnumweight,
IFNULL(c.remark5,0) as remark5,
sum(IFNULL(c.Dunshijichengben,0)) as Dunshijichengben
FROM
--    3.从下面这张表中获取ProductData,remark5(产成品价格)，ProductId,MaterialName,MaterialPrice,MaterialWeight,Dunshijichengben原料添加的总和
( SELECT b.ProductDate,b.PlanId,b.ProductId,bm.MaterialName,bm.MaterialPrice,bm.Remark5,SUM(IFNULL(b.Dunshijichengben,0)) as Dunshijichengben,
bm.MaterialWeight
FROM
-- 1.首先查询计划表，获取当天的计划信息
(SELECT bp.ProductDate,bp.PlanId,a.MaterialId,a.ProductId,a.MaterialName,a.MaterialUnit,a.MaterialPrice,a.Weight,
CONVERT(a.Weight * a.MaterialPrice/ (CASE a.MaterialUnit WHEN 'g' THEN 1000 ELSE 1 END ),DECIMAL(15,2)) as Dunshijichengben,
a.remark5
FROM bus_plan bp
JOIN
--           2.join这张表用来查询添加原料的消耗成本
(
SELECT
f.MaterialId,
f.ProductId,
--         left(f.OperateDate,10) as ProductDate,
m.MaterialName,
m.MaterialUnit,
m.MaterialPrice,
m.remark5,
CONVERT((SUM(f.Weight/(CASE m.MaterialUnit WHEN 'g' THEN 1000 ELSE 1 END ))),DECIMAL(10,2)) as Weight,
f.Remark3
from
bus_feedingdetail f
LEFT JOIN
base_material m
on
f.MaterialId = m.MaterialId
<where>
    <if test="startTime!=null and startTime!=''">
        and left(f.OperateDate,10) &gt;= #{startTime}
    </if>
    <if test="endTime!=null and endTime!=''">
        and left(f.OperateDate,10) &lt;= #{endTime}
    </if>
</where>
AND ISNULL(f.Remark3)=0
and LENGTH(trim(f.Remark3))>0
GROUP BY
f.Remark3,
m.MaterialId,
f.ProductId,
m.MaterialName,
m.MaterialUnit,
m.MaterialPrice,
m.remark5
ORDER BY left(f.OperateDate,10) DESC
) a
ON bp.PlanId = a.Remark3
<where>
    <if test="startTime!=null and startTime!=''">
        bp.ProductDate &gt;= #{startTime}
    </if>
    <if test="endTime!=null and endTime!=''">
        AND bp.ProductDate &lt;= #{endTime}
    </if>
</where>

ORDER BY bp.ProductDate DESC ) b
JOIN base_material bm
ON b.ProductId = bm.MaterialId
GROUP BY b.PlanId,b.ProductId
ORDER BY b.ProductDate DESC ) c
JOIN
bus_plandetail bpd
on c.PlanId = bpd.PlanId
and c.ProductId = bpd.ProductId
GROUP BY c.PlanId,c.ProductId) d
GROUP BY d.PlanId,d.ProductId ) p
GROUP BY p.PlanId
) g
on f.remark3 = g.PlanId
ORDER BY ProductDate DESC

修改：第二版