表一
列A B C D
S11 S12 S13 10
S21 S22 S23 20
S31 S32 S33 30
表二
列E F
S11 AA
S11 AA
S21 BB
S21 BB
S31 AA
需下面的结果
(1)
S11 S12 S13 10 AA
S21 S22 S23 20 BB
S31 S32 S33 30 AA
(2)
40 AA
20 BB
列A B C D
S11 S12 S13 10
S21 S22 S23 20
S31 S32 S33 30
表二
列E F
S11 AA
S11 AA
S21 BB
S21 BB
S31 AA
需下面的结果
(1)
S11 S12 S13 10 AA
S21 S22 S23 20 BB
S31 S32 S33 30 AA
(2)
40 AA
20 BB
(select e,min(f) f from tb2 group by e) b
where a.a = b.e
(select e,min(f) f from tb2 group by e) b
where a.a = b.eselect sum(d) d , f from
(
select a.d,b.f from tb1 a,tb2 b where a.a = b.e
) t
group by f
select distinct a.*,b.f from table1 a left join table2 b
on a.colA = b.colE
(2)
select a.colF,sum(b.colD)
from table2 a left join table1 b on a.colE = b.colA
group by a.colF
Select Distinct A.*, B.F From 表一 A Inner Join 表二 B On A.A = B.E--(2)
Select SUM(A.D) As D, B.F From A Inner Join (Select Distinct * From 表二) B On A.A = B.E Group By B.F
insert into tb1 values('S11', 'S12', 'S13', 10 )
insert into tb1 values('S21', 'S22', 'S23', 20 )
insert into tb1 values('S31', 'S32', 'S33', 30 )
create table tb2(E varchar(10),F varchar(10))
insert into tb2 values('S11', 'AA')
insert into tb2 values('S11', 'AA')
insert into tb2 values('S21', 'BB')
insert into tb2 values('S21', 'BB')
insert into tb2 values('S31', 'AA')
goselect a.*,b.f from tb1 a,
(select e,min(f) f from tb2 group by e) b
where a.a = b.eselect sum(d) d , f from
(
select a.*,b.f from tb1 a,
(select e,min(f) f from tb2 group by e) b
where a.a = b.e
) t
group by fdrop table tb1,tb2/*
A B C D f
---------- ---------- ---------- ----------- ----------
S11 S12 S13 10 AA
S21 S22 S23 20 BB
S31 S32 S33 30 AA(所影响的行数为 3 行)d f
----------- ----------
40 AA
20 BB(所影响的行数为 2 行)
*/
select a.colF,sum(b.colD)
from table2 a left join table1 b on a.colE = b.colA
group by a.colF------------
這種沒有先去掉重復,就統計的,是有問題的。
(a varchar(50),b varchar(50),c varchar(50),d int)
insert into tempa select 's11','s12','s13',10
union all select 's21','s22','s23',20
union all select 's31','s32','s33',30create table TempB
(e varchar(50),f varchar(50))
insert into TempB select 's11','aa'
union all select 's11','aa'
union all select 's21','bb'
union all select 's21','bb'
union all select 's31','aa'select distinct i.a,i.b,i.c,i.d,j.f from tempa i,tempb j where i.a=j.e
(a varchar(50),b varchar(50),c varchar(50),d int)
insert into a select 's11','s12','s13',10
union all select 's21','s22','s23',20
union all select 's31','s32','s33',30create table b
(e varchar(50),f varchar(50))
insert into b select 's11','aa'
union all select 's11','aa'
union all select 's21','bb'
union all select 's21','bb'
union all select 's31','aa'drop table a1:select t1.e,t2.*,t1.f
from (select distinct e,f from b)t1 left join a t2 on t1.e=t2.a
e a b c d f s11 s11 s12 s13 10 aa
s21 s21 s22 s23 20 bb
s31 s31 s32 s33 30 aa(3 row(s) affected)2:
select t1.f,sum(t2.d)as d from (select distinct e,f from b)t1 left join a t2 on t1.e=t2.a group by t1.ff d
-------------------------------------------------- -----------
aa 40
bb 20(2 row(s) affected)
insert into @a
select 'S11','S12','S13',10
union all select 'S21','S22','S23',20
union all select 'S31','S32','S33',30declare @b table (E varchar(50),F varchar(50))
insert into @b
select 'S11','AA'
union all select 'S11','AA'
union all select 'S21','BB'
union all select 'S21','BB'
union all select 'S31','AA'--1
select distinct a.*,b.F from @a a, @b b where a.A = b.E
/*
S11 S12 S13 10 AA
S21 S22 S23 20 BB
S31 S32 S33 30 AA
*/--2
select b.F, sum(a.D) from @a a, (select distinct * from @b) b where a.A = b.E group by b.F
/*
AA 40
BB 20
*/
表一
列A B C D
S11 S12 S13 10
S21 S22 S23 20
S31 S32 S33 30
S42 S43 40
S52 S53 50表二
列E F
S11 AA
S11 AA
S21 BB
S21 BB
S31 AA需下面的结果
(1)
S11 S12 S13 10 AA
S21 S22 S23 20 BB
S31 S32 S33 30 AA
S42 S43 40
(2)
40 AA
20 BB
90
Select Distinct A.*, B.F From 表一 A Left Join 表二 B On A.A = B.E--(2)
Select SUM(A.D) As D, B.F From A Left Join (Select Distinct * From 表二) B On A.A = B.E Group By B.F