如何在程序中加入4表？

本文介绍了如何在程序中加入4表？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 嗨... 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 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 列' 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 ONGOSET QUOTED_IDENTIFIER ONGOALTER Procedure [dbo].[Kardex]@Kala nvarchar(50)ASBEGINselect 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.KalaENDand this error :Msg 8120, Level 16, State 1, Procedure Kardex, Line 7Column '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 queryselect 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, kalakol2From dbo.TAnbarinner join dbo.TKala on dbo.TAnbar.Anbar = dbo.TKala.Anbarinner join T1 on T1.Kala = TKala.Kalainner join T2 on TKala.Kala = T2.Kalawhere dbo.TKala.Kala = @kala--NB NO group by herethanks guys ...very much !and dear CHill60 ... 这篇关于如何在程序中加入4表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！