别名并加入sql [英] alias name and join in sql

问题描述

出什么问题了?

what is wrong??

SELECT A.Code , A.Name, A.Value - isnull(B.Value,0) - isnull(C.Value,0) as COLLL
FROM (SELECT [Code],[Name],Sum([Value]) as Value FROM [zamzam].[dbo].[B_Delivery_Stuff] WHERE [Type]=1 AND [Code] IN(SELECT [Code] FROM [zamzam].[dbo].[S_Stuff] WHERE ID_TypeStuff=4)
Group by Code ,Name) A
left join (SELECT [Code],Sum([Value]) as Value
  FROM [zamzam].[dbo].[B_Delivery_Stuff] B WHERE [type]=2 AND Code IN(SELECT [Code]
  FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) B on A.Code = B.Code
  left join (SELECT [Code]
      ,Sum([Value]) as Value
  FROM [zamzam].[dbo].[B_StuffProduct] C WHERE Code IN(SELECT [Code]
  FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) C on A.Code = C.Code having COLLL<>0

推荐答案

尝试一下.

Try this.

SELECT A.Code , A.Name, A.Value - isnull(B.Value,0) - isnull(C.Value,0) as COLLL
FROM (SELECT [Code],[Name],Sum([Value]) as Value FROM [zamzam].[dbo].[B_Delivery_Stuff] WHERE [Type]=1 AND [Code] IN(SELECT [Code] FROM [zamzam].[dbo].[S_Stuff] WHERE ID_TypeStuff=4)
Group by Code ,Name) A
left join (SELECT [Code],Sum([Value]) as Value
  FROM [zamzam].[dbo].[B_Delivery_Stuff] B WHERE [type]=2 AND Code IN(SELECT [Code]
  FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) B on A.Code = B.Code
  left join (SELECT [Code]
      ,Sum([Value]) as Value
  FROM [zamzam].[dbo].[B_StuffProduct] C WHERE Code IN(SELECT [Code]
  FROM [zamzam].[dbo].[S_Stuff] where ID_TypeStuff=4)  Group by Code) C on A.Code = C.Code having A.Value - isnull(B.Value,0) - isnull(C.Value,0) <>0

关键字"Having"必须位于带有组"的假体单元内按"或求和"功能之一.由于它不在聚合函数或GROUP BY子句中，因此它所处的位置无效.如果要使其成为"WHERE"子句，只需将"HAVING"替换为"WHERE".

The "Having" keyword must go inside a perenthesis unit with either a "Group By" or one of the "Sum" functions. The position that it is at is invalid because it is not contained in either an aggregate function or the GROUP BY clause. If you intended to make it a "WHERE" clause, simply replace "HAVING" with "WHERE".

这篇关于别名并加入sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！