如何在程序中加入4表？ [英] How do I join 4 table in procedure ?

问题描述

嗨...

i希望在程序中加入3个表...

组中的对接错误！？



此查询：

  USE  [AnbarDB]  GO  
  / *   *****对象：StoredProcedure [dbo]。[Kardex]脚本日期：03/09/2015 10:52:57 ****** /  
  SET   ANSI_NULLS   ON  
  GO  
  SET   QUOTED_IDENTIFIER   ON  
  GO  
  ALTER  程序 [dbo]。[Kardex] 
  @ Kala   nvarchar （ 50 ）
 
 
  AS  
  BEGIN  
 选择 
 dbo.TAnbar.Anbar  as  anbar，
 dbo.TAnbar.Kalatype  as  noekala，
 dbo.TKala.Cod  as  cod，
 dbo.TKala.Kala < span class =code-keyword> as  kala，
 dbo.TKala.Vahed  as  vahed，
 dbo.TKala。 Toz  as  tozi，
 dbo.TKala.Mojodi  as  mojodi，
 dbo。 TKala.Pic  as  pict，
 COUNT（dbo.TBuy.Kala） as  tedadkala，
 SUM（dbo.TBuy.Andaze） as  andaz，
 SUM（dbo.TBuy.Kol） as  kalakol，
 COUNT（dbo.TSel.Kala） as  tedadkala2，
 SUM（dbo.TSel.Andaze） as  andaz2，
 SUM（ dbo.TSel.Kol） as  kalakol2 
 来自 dbo.TAnbar  inner   join  dbo.TKala  on  dbo.TAnbar.Anbar = dbo。 TKala.Anbar 
  inner   join  dbo.TBuy  on  dbo.TKala.Kala = dbo.TBuy.Kala 
  inner   join  dbo.TSel   dbo.TKala.Kala = dbo.TSel.Kala 
 其中 dbo。 TKala.Kala =  @ Kala  
  group   by  dbo.TKala.Kala 
 结束 

和此错误：

消息 8120 ，等级 16 ，状态 1 ，程序Kardex，行 7  
列' < span class =code-string> dbo.TAnbar.Anbar'  无效  选择列表，因为 未包含 聚合函数或GROUP BY子句。

和此表：



pic [ ^ ]

解决方案

GROUP BY子句中的任何内容也必须出现在SELECT子句中。



有几种方法可以使用子查询等重新排列，但下面是使用CTE的解决方案（通用表格式 [ ^ ]）

  - 从TBuy获取分组数据到CTE T1 ... 
使用T1（Kala，tedadkala，andaz，kalakol）
 AS 
（
 SELECT Kala ，Count（Kala），SUM（Andaze），SUM（Kol）
来自TBuy GROUP BY Kala 
），
 
  - 从TSel获取分组数据到CTE T2。 .. 
 T2（Kala，tedadkala2，andaz2，kalakol2）
 AS 
（
 SELECT Kala，Count（Kala），SUM（Andaze），SUM（Kol）
来自TSel GROUP BY Kala 
）
  - 在整体查询中使用CTE 
选择
 dbo.TAnbar.Anbar作为anbar，
 dbo.TAnbar.Kalatype如noekala，
 dbo.TKala.Cod为鳕鱼，
 dbo.TKala.Kala为kala，
 dbo.TKala.Vahed为vahed，
 dbo.TKala.Toz as tozi， 
 dbo.TKala.Mojodi为mojodi，
 dbo.TKala.Pic为pict，
 tedadkala，
 andaz，
 kalakol，
 tedadkala2，
 andaz2，
 kalakol2 
 
来自dbo.TAnbar 
内部联接dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbar 
内部联接T1 on T1.Kala = TKala.Kala 
内部连接T2对TKala.Kala = T2.Kala 
其中dbo.TKala.Kala = @kala 
 --NB NO group by here 

非常感谢...

非常多！

和亲爱的CHill60 ......

hi ...
i want join 3 table in procedure ...
butt error in group by !?

this query :

USE [AnbarDB]GO
/****** Object:  StoredProcedure [dbo].[Kardex]    Script Date: 03/09/2015 10:52:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Kardex]
@Kala nvarchar(50)
 
 
AS
BEGIN
select       
       dbo.TAnbar.Anbar as anbar,
       dbo.TAnbar.Kalatype as noekala,
       dbo.TKala.Cod as cod,
       dbo.TKala.Kala as kala,
       dbo.TKala.Vahed as vahed,
       dbo.TKala.Toz as tozi,
       dbo.TKala.Mojodi as mojodi,
       dbo.TKala.Pic as pict,
       COUNT(dbo.TBuy.Kala) as tedadkala,
       SUM(dbo.TBuy.Andaze) as andaz,
       SUM(dbo.TBuy.Kol) as kalakol,
       COUNT(dbo.TSel.Kala) as tedadkala2,
       SUM(dbo.TSel.Andaze) as andaz2,
       SUM(dbo.TSel.Kol) as kalakol2       
       From dbo.TAnbar inner join dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbar
       inner join dbo.TBuy on dbo.TKala.Kala = dbo.TBuy.Kala
       inner join dbo.TSel on dbo.TKala.Kala = dbo.TSel.Kala
       where  dbo.TKala.Kala = @Kala
       group by dbo.TKala.Kala
END

and this error :

Msg 8120, Level 16, State 1, Procedure Kardex, Line 7
Column 'dbo.TAnbar.Anbar' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

and this tables :

pic[^]

解决方案

Anything in the GROUP BY clause must also appear in the SELECT clause.

There are several ways of rearranging this with sub-queries etc but below is a solution using CTEs (Common Table Expressions[^])

-- Get the grouped data from TBuy into CTE T1...
With T1(Kala, tedadkala, andaz, kalakol) 
AS
(
    SELECT Kala, Count(Kala), SUM(Andaze), SUM(Kol)
    from TBuy GROUP BY Kala
),

-- Get the grouped data from TSel into CTE T2...
T2(Kala, tedadkala2, andaz2, kalakol2)
AS
(
    SELECT Kala, Count(Kala), SUM(Andaze), SUM(Kol)
    from TSel GROUP BY Kala
)
-- Use the CTEs in the overall query
select       
       dbo.TAnbar.Anbar as anbar,
       dbo.TAnbar.Kalatype as noekala,
       dbo.TKala.Cod as cod,
       dbo.TKala.Kala as kala,
       dbo.TKala.Vahed as vahed,
       dbo.TKala.Toz as tozi,
       dbo.TKala.Mojodi as mojodi,
       dbo.TKala.Pic as pict,
	tedadkala,
    andaz,
    kalakol,
    tedadkala2,
    andaz2,
    kalakol2 
 
From dbo.TAnbar 
inner join dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbar
inner join T1 on T1.Kala = TKala.Kala
inner join T2 on TKala.Kala = T2.Kala
where  dbo.TKala.Kala = @kala
--NB NO group by here

thanks guys ...
very much !
and dear CHill60 ...

这篇关于如何在程序中加入4表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！