您所在位置：主题：[原创]SQL语句之左连接语病求教!急
wkl1973
[专家分：400] 发布于 2009-05-21 11:32:00
下面这个SQL语句中我该如何把第三个表(蓝色字体)嵌入左连接语句中.请帮助!!!
select tbItem_index_object.NO_Two as 物品编号,tbItem_index_object.UName as 物品名称,tbItem_index_object.Uxing_Hao as 物品型号,tbItem_index_object.UJi_Liang as 计量单位,tbItem_LuKu_DATA.Uji_Hua_Jia as 计划价,tbItem_index_object.UChuQiNumber as 期初数量,tbItem_index_object.UChuQiValue as 期初金额,tbItem_index_object.Usolid_Accept as 实际库存,SUM(tbItem_LuKu_DATA.UShi_Shou) AS 月收入量,
SUM(tbItem_LuKu_DATA.UShi_Shou * tbItem_LuKu_DATA.Uji_Hua_Jia) as 收入金额,
SUM(tbItem_ChuKu_object.Ushu_liang) as 月消耗量,
SUM(tbItem_ChuKu_object.Ushu_liang * tbItem_ChuKu_object.Uji_Hua_Jia) as 消耗金额,
[color=0000FF]SUM(tbBaoShen.UShi_Shou) as 报损数量,SUM(tbBaoShen.UShi_Shou * tbBaoShen.Udan_Jia) AS 报损金额[/color],
备注=CASE
WHEN tbItem_index_object.Usolid_Accept<100 THEN '库存不足'
WHEN tbItem_index_object.Usolid_Accept>1000 THEN '库存超标'
ELSE '----'
END
FROM
(tbItem_index_object LEFT JOIN tbItem_LuKu_DATA ON tbItem_index_object.NO_Two=tbItem_LuKu_DATA.NO_Two AND
tbItem_LuKu_DATA.UDate between '2009-3-25' and '2009-4-25') LEFT JOIN tbItem_ChuKu_object
ON tbItem_index_object.NO_Two=tbItem_ChuKu_object.NO_Two AND tbItem_ChuKu_object.UDate between '2009-3-25' and '2009-4-25'
[color=0000FF]LEFT JOIN tbBaoShen
ON tbItem_index_object.NO_Two=tbBaoShen.NO_Two AND tbBaoShen.UDate between '2009-3-25' and '2009-4-25'[/color] GROUP BY tbItem_index_object.NO_Two,tbItem_index_object.UName,tbItem_index_object.Uxing_Hao,tbItem_index_object.UJi_Liang,
tbItem_LuKu_DATA.Uji_Hua_Jia,tbItem_index_object.UChuQiNumber,tbItem_index_object.UChuQiValue,tbItem_index_object.Usolid_Accept[em10]
select tbItem_index_object.NO_Two as 物品编号,tbItem_index_object.UName as 物品名称,tbItem_index_object.Uxing_Hao as 物品型号,tbItem_index_object.UJi_Liang as 计量单位,tbItem_LuKu_DATA.Uji_Hua_Jia as 计划价,tbItem_index_object.UChuQiNumber as 期初数量,tbItem_index_object.UChuQiValue as 期初金额,tbItem_index_object.Usolid_Accept as 实际库存,SUM(tbItem_LuKu_DATA.UShi_Shou) AS 月收入量,
SUM(tbItem_LuKu_DATA.UShi_Shou * tbItem_LuKu_DATA.Uji_Hua_Jia) as 收入金额,
SUM(tbItem_ChuKu_object.Ushu_liang) as 月消耗量,
SUM(tbItem_ChuKu_object.Ushu_liang * tbItem_ChuKu_object.Uji_Hua_Jia) as 消耗金额,
[color=0000FF]SUM(tbBaoShen.UShi_Shou) as 报损数量,SUM(tbBaoShen.UShi_Shou * tbBaoShen.Udan_Jia) AS 报损金额[/color],
备注=CASE
WHEN tbItem_index_object.Usolid_Accept<100 THEN '库存不足'
WHEN tbItem_index_object.Usolid_Accept>1000 THEN '库存超标'
ELSE '----'
END
FROM
(tbItem_index_object LEFT JOIN tbItem_LuKu_DATA ON tbItem_index_object.NO_Two=tbItem_LuKu_DATA.NO_Two AND
tbItem_LuKu_DATA.UDate between '2009-3-25' and '2009-4-25') LEFT JOIN tbItem_ChuKu_object
ON tbItem_index_object.NO_Two=tbItem_ChuKu_object.NO_Two AND tbItem_ChuKu_object.UDate between '2009-3-25' and '2009-4-25'
[color=0000FF]LEFT JOIN tbBaoShen
ON tbItem_index_object.NO_Two=tbBaoShen.NO_Two AND tbBaoShen.UDate between '2009-3-25' and '2009-4-25'[/color] GROUP BY tbItem_index_object.NO_Two,tbItem_index_object.UName,tbItem_index_object.Uxing_Hao,tbItem_index_object.UJi_Liang,
tbItem_LuKu_DATA.Uji_Hua_Jia,tbItem_index_object.UChuQiNumber,tbItem_index_object.UChuQiValue,tbItem_index_object.Usolid_Accept[em10]