此结构如下三表
s1
A
1
2
3s2
B C D
1 10 p
1 20 p
2 30 qs3
E F G
2 100 q
1 200 p
3 300 p其中 s1 s2 s3表中的 A B E字段关联 D G 为查询条件
我现在要查询当D=p G=p 时想得出以下的结果
A C D F G
1 30 p 200 p
2
3 300 p
s1
A
1
2
3s2
B C D
1 10 p
1 20 p
2 30 qs3
E F G
2 100 q
1 200 p
3 300 p其中 s1 s2 s3表中的 A B E字段关联 D G 为查询条件
我现在要查询当D=p G=p 时想得出以下的结果
A C D F G
1 30 p 200 p
2
3 300 p
-- Author :SQL77(只为思齐老)
-- Date :2010-01-13 21:47:38
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[s1]
if object_id('[s1]') is not null drop table [s1]
go
create table [s1]([A] int)
insert [s1]
select 1 union all
select 2 union all
select 3
--> 测试数据:[s2]
if object_id('[s2]') is not null drop table [s2]
go
create table [s2]([B] int,[C] int,[D] varchar(1))
insert [s2]
select 1,10,'p' union all
select 1,20,'p' union all
select 2,30,'q'
--> 测试数据:[s3]
if object_id('[s3]') is not null drop table [s3]
go
create table [s3]([E] int,[F] int,[G] varchar(1))
insert [s3]
select 2,100,'q' union all
select 1,200,'p' union all
select 3,300,'p'
--------------开始查询-------------------------
SELECT A.A,
SUM(DISTINCT B.C)C,
ISNULL(D,'')D,
SUM(DISTINCT C.F)F,C.G
FROM S1 A
LEFT JOIN S2 B ON A.A=B.B AND B.D='P'
LEFT JOIN S3 C ON A.A=C.E AND C.G='P'GROUP BY A.A,B.D,C.G
----------------结果----------------------------
/* (所影响的行数为 3 行)
(所影响的行数为 3 行)
(所影响的行数为 3 行)A C D F G
----------- ----------- ---- ----------- ----
1 30 p 200 p
2 NULL NULL NULL
3 NULL 300 p(所影响的行数为 3 行)警告: 聚合或其它 SET 操作消除了空值。*/
a.a,isnull(b.c,'') as c,isnull(b.d,,'') as d,isnull(c.f,'') as f,isnull(c.g,'') as g
from
s1 a
left join
(select sum(c) as c,D,b from s2 group by d,b)b
on
a.a=b.b
left join
s3 c
on
a.a=c.e
if object_id('[s1]') is not null drop table [s1]
go
create table [s1]([A] int)
insert [s1]
select 1 union all
select 2 union all
select 3
--> 测试数据:[s2]
if object_id('[s2]') is not null drop table [s2]
go
create table [s2]([B] int,[C] int,[D] varchar(1))
insert [s2]
select 1,10,'p' union all
select 1,20,'p' union all
select 2,30,'q'
--> 测试数据:[s3]
if object_id('[s3]') is not null drop table [s3]
go
create table [s3]([E] int,[F] int,[G] varchar(1))
insert [s3]
select 2,100,'q' union all
select 1,200,'p' union all
select 3,300,'p'select t.A,
r.C,r.D,
h.F,h.G
from s1 t left join
(select B,sum(C) as C,D from s2 group by B,D) r
on t.A=r.B and r.D = 'p' left join
s3 h
on t.A=h.E and h.G='p'
----------------------
1 30 p 200 p
2 NULL NULL NULL NULL
3 NULL NULL 300 p
if object_id('[s1]') is not null drop table [s1]
go
create table [s1]([A] int)
insert [s1]
select 1 union all
select 2 union all
select 3
--> 测试数据:[s2]
if object_id('[s2]') is not null drop table [s2]
go
create table [s2]([B] int,[C] int,[D] varchar(1))
insert [s2]
select 1,10,'p' union all
select 1,20,'p' union all
select 2,30,'q'
--> 测试数据:[s3]
if object_id('[s3]') is not null drop table [s3]
go
create table [s3]([E] int,[F] int,[G] varchar(1))
insert [s3]
select 2,100,'q' union all
select 1,200,'p' union all
select 3,300,'p'select t.A,
r.C,r.D,
h.F,h.G
from s1 t left join
(select B,sum(C) as C,D from s2 group by B,D) r
on t.A=r.B and r.D = 'p' left join
s3 h
on t.A=h.E and h.G='p'
----------------------
1 30 p 200 p
2 NULL NULL NULL NULL
3 NULL NULL 300 p