两个表T1,T2T1(A,B)
A B
11 12
22 23T2(A,C)
A C
11 1
11 2
22 3
22 4把T2中A相同的数据加起来结果,显示如下:A B C
11 12 3
22 23 7
A B
11 12
22 23T2(A,C)
A C
11 1
11 2
22 3
22 4把T2中A相同的数据加起来结果,显示如下:A B C
11 12 3
22 23 7
From T1
inner join T2 On T1.A=T2.A
Group By T1.A,T1.B
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
create table [T1]([A] int,[B] int)
insert [T1]
select 11,12 union all
select 22,23
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
create table [T2]([A] int,[C] int)
insert [T2]
select 11,1 union all
select 11,2 union all
select 22,3 union all
select 22,4select A.A,B,C from [T1] A,(select A,C=sum(C) from t2 group by A) B where A.A=B.A
/*
A B C
----------- ----------- -----------
11 12 3
22 23 7(所影响的行数为 2 行)*/
drop table T1,t2
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-08 23:34:07
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([A] int,[B] int)
insert [T1]
select 11,12 union all
select 22,23
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([A] int,[C] int)
insert [T2]
select 11,1 union all
select 11,2 union all
select 22,3 union all
select 22,4
--------------开始查询--------------------------
select
t1.a,t1.b,t2.c
from
t1
join
(select a,sum(c) as c from t2 group by a)t2
on
t1.a=t2.a
----------------结果----------------------------
/*a b c
----------- ----------- -----------
11 12 3
22 23 7(2 行受影响)
*/
From T1
inner join
(
Select A,C=Sum(C)
from T2
Group By A
) T2 On T1.A=T2.A
from t1 inner join t2 on t1.a=t2.a
group by t1.a,t1.b
select t1.* , isnull((select sum(c) from t2 where t2.a = t1.a),0) c from t1
create table t1
(
A int,
B int
)
gocreate table t2
(
A int,
C int
)
goinsert into t1
select 11,12 union all
select 22,23
goinsert into t2
select 11, 1 union all
select 11, 2 union all
select 22, 3 union all
select 22, 4
goselect t1.A,t1.B,sum(t2.C)as C FROM t1 left join
t2 on t1.A=t2.A group by t1.A,t1.B
where连接 和 innere join 内连接 有什么区别和联系,