有两个表
表A:字段1,字段2,字段3
A 11 12
B 21 22
C 31 32
表B:字段a,字段b,字段c
A 1 E
A 2 F
B 1 F
B 2 E
C 1 E
C 2 F
求一个查询,得到表(字段4为表B中字段b值等于1的,字段5为表B中字段b值等于2的)
字段1,字段2,字段3,字段4,字段5
A 11 12 E F
B 21 22 F E
C 31 32 E F
表A:字段1,字段2,字段3
A 11 12
B 21 22
C 31 32
表B:字段a,字段b,字段c
A 1 E
A 2 F
B 1 F
B 2 E
C 1 E
C 2 F
求一个查询,得到表(字段4为表B中字段b值等于1的,字段5为表B中字段b值等于2的)
字段1,字段2,字段3,字段4,字段5
A 11 12 E F
B 21 22 F E
C 31 32 E F
from 表A
inner join 表B on 表B.字段a=表A.字段1
表A.*,
(select 表B.字段c from 表A ,表B where 表B.字段a=表A.字段1and 表B.字段b=1),(select 表B.字段c from 表A ,表B where 表B.字段a=表A.字段1and 表B.字段b=2)
from 表A,表B
表A.*,
(select 表B.字段c from 表A ,表B where 表B.字段a=表A.字段1and 表B.字段b=1) 字段4 ,
(select 表B.字段c from 表A ,表B where 表B.字段a=表A.字段1and 表B.字段b=2) 字段5
from 表A,表B
A 11 12 E
A 11 12 F
B 21 22 F
B 21 22 E
C 31 32 E
C 31 32 F
有点差异啊,我想得到的是
字段1,字段2,字段3,字段4,字段5
A 11 12 E F
B 21 22 F E
C 31 32 E F
A 11 12
B 21 22
C 31 32select c.字段1 字段1,c.字段2 字段2,c.字段3 字段3,d.字段c 字段4,d.字段c 字段5 from 表A c,(select a.字段a,a.字段c,b.a.字段c from 表B a,表B b where a.字段a=b.字段a and a.字段b<b.字段b) d where c.字段1=d.字段a
insert into A values('A' , 11 , 12)
insert into A values('B',21,22)
insert into A values('C',31,32)
create table B (cola varchar(10),colb int,colc varchar(10))
insert into B values('A', 1, 'E')
insert into B values('A', 2, 'F')
insert into B values('B', 1, 'F')
insert into B values('B', 2, 'E')
insert into B values('C', 1, 'E')
insert into B values('C', 2, 'F')
go
select t1.* , t2.col5 from
(select A.* , B.colc col4 from A,B where A.col1 = B.cola and B.colb = 1) t1,
(select A.* , B.colc col5 from A,B where A.col1 = B.cola and B.colb = 2) t2
where t1.col1 = t2.col1
/*
col1 col2 col3 col4 col5
---------- ----------- ----------- ---------- ----------
A 11 12 E F
B 21 22 F E
C 31 32 E F
(所影响的行数为 3 行)
*/select t1.col1,t1.col2,t1.col3,isnull(t1.col4,'') col4 , isnull(t2.col5,'') col5 from
(select A.* , B.colc col4 from A,B where A.col1 = B.cola and B.colb = 1) t1
full join
(select A.* , B.colc col5 from A,B where A.col1 = B.cola and B.colb = 2) t2
on t1.col1 = t2.col1
/*
col1 col2 col3 col4 col5
---------- ----------- ----------- ---------- ----------
A 11 12 E F
B 21 22 F E
C 31 32 E F
(所影响的行数为 3 行)
*/
drop table A,B
insert @a
select 'a',11,12
union all
select 'b',21,22
union all
select 'c',31,32declare @b table(name1 varchar(20),name2 int,name3 varchar(20))
insert @b
select 'a',1,'e'
union all
select 'a',2,'f'
union all
select 'b',1,'f'
union all
select 'b',2,'e'
union all
select 'c',1,'e'
union all
select 'c',2,'f'select * from @a c,(select a.name1,a.name3,b.name3 as name4 from @b a ,@b b where a.name1=b.name1 and a.name2<b.name2) d
where c.name1=d.name1
/*
name1 name2 name3 name1 name3 name4
-------------------- ----------- ----------- -------------------- -------------------- --------------------
a 11 12 a e f
b 21 22 b f e
c 31 32 c e f(所影响的行数为 3 行)
*/