SELECT a.ID,o.UID as
OrderDocUID,o.OrderStyleID,a.StyleDocTreeID,a.BOMType,o.OrderDocType,
a.Code,a.IsCheckMaterial,a.IsCalculation,a.IsCheckPrice,a.IsPurchase,a.IsReceive,
a.IsStockIn,a.IsFollowDoc,a.IsAssign,a.IsAssistantMaterial,a.IsMainMaterial,a.FilingUp,a.IsIntact,
a.IsSubmit,a.SubmitDate,a.CheckState,a.CreateDate,a.CreateUserID,a.LastModifiedDate,a.ReferenceDate,
tp.Name as TechnicPerson,ac.Name as CreateUserName,Cast(null as Image) as StyleImage,
a.LastModifiedUserID,a.IsBreakDown,a.BreakDownDate,a.HasHanged,a.Re,am.Name as LastModifiedUserName ,
o.DocCode as OrderCode,o.Code as StyleCode ,o.Name as StyleName,o.SessionType,cs.CustomName FROM
OrderStyleBOM as a INNER JOIN view_OrderStyleExtend_ALL o ON a.StyleDocTreeID = o.StyleDocTreeID
LEFT JOIN view_GoodsType vg on vg.ID = o.GoodsType
LEFT JOIN AC_User tp ON o.TechnicPerson = tp.ID
LEFT JOIN AC_User ac ON a.CreateUserID = ac.ID
LEFT JOIN AC_User am ON a.LastModifiedUserID = am.ID
LEFT JOIN Custom cs ON cs.ID = o.CustomID
WHERE o.[DocCode] LIKE '%120392%' and a.BOMType = 0 刚开始接触ERP 很早以前学过一段时间SQL 忘得差不多了
求 注释
1.select 后面的那些 a.xxx 表示的是什么 是不是 a的属性?a又是什么 表吗?o.xxx o是什么
2.left join 我看到有些不是说列出所有的 然后再进行选择 没有的显示Null 吗?
为什么最后显示的 没有那些 null的
xxx表示字段名 a是表的别名 O 也是别名2.left join 我看到有些不是说列出所有的 然后再进行选择 没有的显示Null 吗?
为什么最后显示的 没有那些 null的
自己去看看LEFT JOIN 的意思
o.UID AS OrderDocUID ,
o.OrderStyleID ,
a.StyleDocTreeID ,
a.BOMType ,
o.OrderDocType ,
a.Code ,
a.IsCheckMaterial ,
a.IsCalculation ,
a.IsCheckPrice ,
a.IsPurchase ,
a.IsReceive ,
a.IsStockIn ,
a.IsFollowDoc ,
a.IsAssign ,
a.IsAssistantMaterial ,
a.IsMainMaterial ,
a.FilingUp ,
a.IsIntact ,
a.IsSubmit ,
a.SubmitDate ,
a.CheckState ,
a.CreateDate ,
a.CreateUserID ,
a.LastModifiedDate ,
a.ReferenceDate ,
tp.Name AS TechnicPerson ,
ac.Name AS CreateUserName ,
CAST(NULL AS IMAGE) AS StyleImage ,
a.LastModifiedUserID ,
a.IsBreakDown ,
a.BreakDownDate ,
a.HasHanged ,
a.Re ,
am.Name AS LastModifiedUserName ,
o.DocCode AS OrderCode ,
o.Code AS StyleCode ,
o.Name AS StyleName ,
o.SessionType ,
cs.CustomName
FROM OrderStyleBOM AS a --a代表OrderStyleBOM
INNER JOIN view_OrderStyleExtend_ALL o ON a.StyleDocTreeID = o.StyleDocTreeID --o代表view_OrderStyleExtend_ALL
LEFT JOIN view_GoodsType vg ON vg.ID = o.GoodsType --left join 参考 http://msdn.microsoft.com/en-us/library/aa213228(v=sql.80).aspx
LEFT JOIN AC_User tp ON o.TechnicPerson = tp.ID
LEFT JOIN AC_User ac ON a.CreateUserID = ac.ID
LEFT JOIN AC_User am ON a.LastModifiedUserID = am.ID
LEFT JOIN Custom cs ON cs.ID = o.CustomID
WHERE o.[DocCode] LIKE '%120392%'
AND a.BOMType = 0
a.xxx表示的字段,a是表OrderStyleBOM的别名,o.xxx o是表view_OrderStyleExtend_ALL的别名
2、left join 我看到有些不是说列出所有的 然后再进行选择 没有的显示Null 吗
left jion表示左连接
INNER JOIN view_OrderStyleExtend_ALL o ON a.StyleDocTreeID = o.StyleDocTreeID --o代表view_OrderStyleExtend_ALL ??重命名?定义?
LEFT JOIN view_GoodsType vg ON vg.ID = o.GoodsType --left join 这里是不是 类似把view_GoodsType 命名成 vg ?下同
LEFT JOIN AC_User tp ON o.TechnicPerson = tp.ID
LEFT JOIN AC_User ac ON a.CreateUserID = ac.ID
LEFT JOIN AC_User am ON a.LastModifiedUserID = am.ID
LEFT JOIN Custom cs ON cs.ID = o.CustomID是不是 join 后面如果跟上两个字段 是不是类似 as 啊?
很小白的问题。。请指教 谢谢