表A 字段A1 字段A2
A001 A21
A002 A22
A003 A23 表B 字段B1 字段B2
A001 AC1
A001 AC1
c001 AC1
A002 AC2
A002 AC2
C002 AC2表C 字段C1 字段C2
C001 C21
C002 C22
C003 C23 想得到表AC 字段A2 字段C2
A21 C21
A22 C22
A23 nill
表的关系 表A的字段A1 一对多 表B的字段B1
表c的字段c1 一对一 表B的字段B1 通过表B的字段B2值相等, 表A和表c发生关联.
表A没有关系的,字段c2值为NILL。例:A23----NILL
表A的行数、字段A2的值和表AC 行数、字段A2的值相同
A001 A21
A002 A22
A003 A23 表B 字段B1 字段B2
A001 AC1
A001 AC1
c001 AC1
A002 AC2
A002 AC2
C002 AC2表C 字段C1 字段C2
C001 C21
C002 C22
C003 C23 想得到表AC 字段A2 字段C2
A21 C21
A22 C22
A23 nill
表的关系 表A的字段A1 一对多 表B的字段B1
表c的字段c1 一对一 表B的字段B1 通过表B的字段B2值相等, 表A和表c发生关联.
表A没有关系的,字段c2值为NILL。例:A23----NILL
表A的行数、字段A2的值和表AC 行数、字段A2的值相同
解决方案 »
- 列转行的简单问题!
- 数据库中有时间字段,精确到秒。我的查询只到年月日,该如何写sql?多谢
- 权限设计的问题
- 判断条件随机取一定数量记录,如果数量不足再随机取前面的记录止到取满
- 请教下大家,存储过程中怎么做判断处理?
- VC++ SQL2000 数据库开发 视频教程
- "web助手向导"在那里??
- SqlServer2000的问题----非常急(再线等待!!!)
- 用ADO连接SQL Server 2000为什么会出现“Stack Overflow".
- 请教大家2个简单问题,安装SQL SERVER时生成的那些数据库是做什么用的?如何能根据.sql文件生成数据库?
- 這個SQL語句總是說SELECT附近錯誤?是不是SUM函裡面不嵌套SELECT語句?
- 在SQL Server 2005中能否写类似C#中的for循环语句?
(A1 varchar(10),A2 varchar(20))
insert into A
Select 'A001','A21' union all
Select 'A002','A22' union all
Select 'A003','A23'
Create Table C
(C1 varchar(10),C2 varchar(20))
insert into c
Select 'C001','C21' union all
Select 'C002','C22' union all
Select 'C003','C23'
Create Table B
(B1 varchar(10),B2 varchar(20))
insert into b
Select 'A001','AC1' union all
Select 'A001','AC1' union all
Select 'C001','AC1' union allSelect 'A002','AC2' union all
Select 'A002','AC2' union all
Select 'C002','AC2'
Select * From (Select A2,B2 From A,B Where A1=B1) AA,(Select B2,C2 From B,C Where B1=C1) BB Where aa.B2=BB.B2Select AA.A2,BB.C2 From
(Select Distinct A2,B2 From A Left join B ON A1=B1) AA
Left Join
(Select B2,C2 From B Inner join C On B1=C1) BB
On aa.B2=BB.B2
(Select Distinct A2,B2 From A Left join B ON A1=B1) AA
Left Join
(Select B2,C2 From B Inner join C On B1=C1) BB
On aa.B2=BB.B2
--A
drop table #A
create table #A
(A1 varchar(50),
A2 varchar(50)
)
insert into #A
select 'A001','A21' union all select 'A002','A22' union all select 'A003','A23'
select * from #A
--B
drop table #B
create table #B
(B1 varchar(50),
B2 varchar(50)
)
insert into #B
select 'A001','AC1' union all select 'A001','AC1' union all select 'c001','AC1' union all select 'A002','AC2' union all select 'A002','AC2' union all select 'C002','AC2'
select * from #B
--C
drop table #C
create table #C
(C1 varchar(50),
C2 varchar(50)
)
insert into #C
select 'C001','C21' union all select 'C002','C22' union all select 'C003','C23'
select * from #C
查询:
select t1.A2,isnull(t2.C2,'nill') C2 from
(select distinct #A.A2,#B.B2 from #A left join #B on #A.A1=#B.B1) t1
left join
(select distinct #C.C2,#B.B2 from #C left join #B on #C.C1=#B.B1 where B2<>'') t2
on t1.B2=t2.B2结果:
-----------------
A2 C2
A21 C21
A22 C22
A23 nill
[A1] varchar(20),
[A2] varchar(20)
)
CREATE TABLE [B] (
[B1] varchar(20),
[B2] varchar(20)
)
CREATE TABLE [C] (
[C1] varchar(20),
[C2] varchar(20)
)
INSERT INTO A
SELECT 'A001','A21' UNION ALL
SELECT 'A002','A22' UNION ALL
SELECT 'A003','A23'
INSERT INTO B
SELECT 'A001','AC1' UNION ALL
SELECT 'A001','AC1' UNION ALL
SELECT 'C001','AC1' UNION ALL
SELECT 'A002','AC2' UNION ALL
SELECT 'A002','AC2' UNION ALL
SELECT 'C002','AC2'
INSERT INTO C
SELECT 'C001','C21' UNION ALL
SELECT 'C002','C22' UNION ALL
SELECT 'C003','C23'SELECT AC.A2,BC.B2 FROM
(SELECT DISTINCT A.A2,B.B2 FROM A
LEFT OUTER JOIN B ON A.A1=B.B1) AC
LEFT OUTER JOIN
(
SELECT B.B2,C.C2 FROM B INNER JOIN C ON B.B1=C.C1
) BC ON AC.B2=BC.B2
drop table a
drop table b
drop table c
--结果
A2 B2
-------------------- --------------------
A21 AC1
A22 AC2
A23 NULL(3 行受影响)