就比如现有表 A: id ,value1 1 '1' 1 '2' B: id ,value2 1, '3' c: id ,value3 1, '4'想得到值为: id,value1,value2,value3 1 '1' '3' '4' 1 '2 ' '3' '4'
select a.*,b.* from mzsf_data a,mzcfzjl_data b where a.id=b.id and exists (select c.xm from ghxx c where a.id=c.id)
select a.*,b.* from mzsf_data a,mzcfzjl_data b where a.id=b.id and exists (select c.id from ghxx c where a.id=c.id) and exists (select c.id from ghxx c where b.id=c.id)
SQL> select * from a; ID VALUE1 --------------------------------------- -------------------- 1 1 1 2SQL> select * from b; ID VALUE2 --------------------------------------- -------------------- 1 3SQL> select * from c; ID VALUE3 --------------------------------------- -------------------- 1 4SQL> SQL> select a.id,a.value1,b.value2,c.value3 from a cross join b,c; ID VALUE1 VALUE2 VALUE3 --------------------------------------- -------------------- -------------------- -------------------- 1 1 3 4 1 2 3 4
用子查询的写法,先连接其中两表,得出的数据集再和第三个表相连 select t.*,c.* from (select a.*,b.* from a,b where b.id = b.id)t,c where t.id=c.id
SQL> with a as (select 1 id,'1' value from dual 2 union 3 select 1 id,'2' value from dual 4 ), 5 b as (select 1 id,'3' value from dual), 6 c as (select 1 id,'4' value from dual) 7 select a.id,a.value,b.value value1,c.value value2 from a,b,c 8 where a.id=b.id and a.id=c.id 9 /
ID VALUE VALUE1 VALUE2 ---------- ----- ------ ------ 1 1 3 4 1 2 3 4
SQL>
倒,发出的帖子不能修改的啊 上面有点笔误: elect t.*,c.* from (select a.*,b.* from a,b where a.id = b.id)t,c where t.id=c.id
select t.*,c.* from (select a.*,b.* from a,b where b.id = b.id)t,c where t.id=c.id 不能明确定义列。我想过了
select a.*,b.*,c.* from a,b,c where a.id=b.id and b.id=c.id 有什么不对呢? SQL> with a as (select 1 id,'1' value from dual 2 union 3 select 1 id,'2' value from dual 4 ), 5 b as (select 1 id,'3' value from dual), 6 c as (select 1 id,'4' value from dual) 7 select a.id,a.value,b.value value1,c.value value2 from a,b,c 8 where a.id=b.id and b.id=c.id 9 /
ID VALUE VALUE1 VALUE2 ---------- ----- ------ ------ 1 1 3 4 1 2 3 4这样很对呀,跟你在4楼的要求没有什么不符的。
但是如果value的值不止是1,2,3,4. 可能到1000了呢,那应该怎么写呢
select a.*,b.*,c.* from a inner join b using(id) inner join c using(id)
注意一下关键字,using得不到想要的结果, 就用 on(条件1 and (条件2 or 条件3)……), 我经常要连接七、八张表 有够变态的
A: id ,value1
1 '1'
1 '2'
B: id ,value2
1, '3'
c: id ,value3
1, '4'想得到值为:
id,value1,value2,value3
1 '1' '3' '4'
1 '2 ' '3' '4'
and
exists
(select c.xm from ghxx c where a.id=c.id)
select a.*,b.* from mzsf_data a,mzcfzjl_data b where a.id=b.id
and
exists
(select c.id from ghxx c where a.id=c.id)
and
exists
(select c.id from ghxx c where b.id=c.id)
SQL> select * from a; ID VALUE1
--------------------------------------- --------------------
1 1
1 2SQL> select * from b; ID VALUE2
--------------------------------------- --------------------
1 3SQL> select * from c; ID VALUE3
--------------------------------------- --------------------
1 4SQL>
SQL> select a.id,a.value1,b.value2,c.value3 from a cross join b,c; ID VALUE1 VALUE2 VALUE3
--------------------------------------- -------------------- -------------------- --------------------
1 1 3 4
1 2 3 4
select t.*,c.* from (select a.*,b.* from a,b where b.id = b.id)t,c
where t.id=c.id
2 union
3 select 1 id,'2' value from dual
4 ),
5 b as (select 1 id,'3' value from dual),
6 c as (select 1 id,'4' value from dual)
7 select a.id,a.value,b.value value1,c.value value2 from a,b,c
8 where a.id=b.id and a.id=c.id
9 /
ID VALUE VALUE1 VALUE2
---------- ----- ------ ------
1 1 3 4
1 2 3 4
SQL>
上面有点笔误:
elect t.*,c.* from (select a.*,b.* from a,b where a.id = b.id)t,c
where t.id=c.id
where t.id=c.id
不能明确定义列。我想过了
select a.*,b.*,c.* from a,b,c where a.id=b.id and b.id=c.id 有什么不对呢?
SQL> with a as (select 1 id,'1' value from dual
2 union
3 select 1 id,'2' value from dual
4 ),
5 b as (select 1 id,'3' value from dual),
6 c as (select 1 id,'4' value from dual)
7 select a.id,a.value,b.value value1,c.value value2 from a,b,c
8 where a.id=b.id and b.id=c.id
9 /
ID VALUE VALUE1 VALUE2
---------- ----- ------ ------
1 1 3 4
1 2 3 4这样很对呀,跟你在4楼的要求没有什么不符的。
但是如果value的值不止是1,2,3,4.
可能到1000了呢,那应该怎么写呢
inner join b using(id)
inner join c using(id)
就用 on(条件1 and (条件2 or 条件3)……),
我经常要连接七、八张表
有够变态的