?? SELECT 行号,分数,平均分=(select avg(分数) from a where 行号=aa.行号) from a aa left join b bb on aaa.tid=bbb.mid
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-14 16:58:40 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[表格]') IS NOT NULL DROP TABLE [表格] GO CREATE TABLE [表格]([tid] INT,[行号] INT) INSERT [表格] SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,2 UNION ALL SELECT 4,2 UNION ALL SELECT 5,3 UNION ALL SELECT 6,3 GO --SELECT * FROM [tb]--> 生成测试数据表:成绩IF OBJECT_ID('[成绩]') IS NOT NULL DROP TABLE [成绩] GO CREATE TABLE [成绩]([mid] INT,[分数] INT) INSERT [成绩] SELECT 1,2 UNION ALL SELECT 2,2 UNION ALL SELECT 3,1 UNION ALL SELECT 4,3 UNION ALL SELECT 5,4 UNION ALL SELECT 6,2 GO --SELECT * FROM [成绩]-->SQL查询如下: select b.*,平均分=(select AVG([分数]) from [表格] c join [成绩] d on c.tid=d.mid where [行号]=a.[行号]) from [表格] a join [成绩] b on a.tid=b.mid /* mid 分数 平均分 ----------- ----------- ----------- 1 2 2 2 2 2 3 1 2 4 3 2 5 4 3 6 2 3(6 行受影响) */
select a.[行号], b.[分数], avg(b.[分数]) over (partition by a.[行号]) [平均分] from [表格] a inner join [成绩] b on a.tid=b.mid
select 行号,分数,平均分=(select avg(分数) from a where 行号=t.行号) from a t left join b on t.tid=b.mid
使用窗口函数 或者 APPLY
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1 GO CREATE TABLE tb1([tid] INT,[行号] INT) INSERT tb1 SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,2 UNION ALL SELECT 4,2 UNION ALL SELECT 5,3 UNION ALL SELECT 6,3 GO --> 生成测试数据表:成绩IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2 GO CREATE TABLE tb2([mid] INT,[分数] INT) INSERT tb2 SELECT 1,2 UNION ALL SELECT 2,2 UNION ALL SELECT 3,1 UNION ALL SELECT 4,3 UNION ALL SELECT 5,4 UNION ALL SELECT 6,2 GO SELECT a.tid,a.[行号], 平均分=(SELECT avg([分数]) FROM tb2 WHERE tb2.mid=a.tid) FROM tb1 a
SELECT 行号,分数,平均分=(select avg(分数) from a where 行号=aa.行号)
from a aa left join b bb
on aaa.tid=bbb.mid
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-14 16:58:40
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[表格]') IS NOT NULL
DROP TABLE [表格]
GO
CREATE TABLE [表格]([tid] INT,[行号] INT)
INSERT [表格]
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,3
GO
--SELECT * FROM [tb]--> 生成测试数据表:成绩IF OBJECT_ID('[成绩]') IS NOT NULL
DROP TABLE [成绩]
GO
CREATE TABLE [成绩]([mid] INT,[分数] INT)
INSERT [成绩]
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,4 UNION ALL
SELECT 6,2
GO
--SELECT * FROM [成绩]-->SQL查询如下:
select b.*,平均分=(select AVG([分数]) from [表格] c join [成绩] d on c.tid=d.mid where [行号]=a.[行号])
from [表格] a
join [成绩] b
on a.tid=b.mid
/*
mid 分数 平均分
----------- ----------- -----------
1 2 2
2 2 2
3 1 2
4 3 2
5 4 3
6 2 3(6 行受影响)
*/
select a.[行号], b.[分数],
avg(b.[分数]) over (partition by a.[行号]) [平均分]
from [表格] a inner join [成绩] b on a.tid=b.mid
行号,分数,平均分=(select avg(分数) from a where 行号=t.行号)
from
a t left join b
on
t.tid=b.mid
DROP TABLE tb1
GO
CREATE TABLE tb1([tid] INT,[行号] INT)
INSERT tb1
SELECT 1,1 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,2 UNION ALL
SELECT 4,2 UNION ALL
SELECT 5,3 UNION ALL
SELECT 6,3
GO
--> 生成测试数据表:成绩IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2([mid] INT,[分数] INT)
INSERT tb2
SELECT 1,2 UNION ALL
SELECT 2,2 UNION ALL
SELECT 3,1 UNION ALL
SELECT 4,3 UNION ALL
SELECT 5,4 UNION ALL
SELECT 6,2
GO
SELECT a.tid,a.[行号],
平均分=(SELECT avg([分数]) FROM tb2 WHERE tb2.mid=a.tid)
FROM tb1 a