我写的语句
SELECT table1.地区, table2.县市区,
tabble1.总宗数, tabble1.总面积,
tabble1.总收入, tabble1.总收益,
tabble2.出让宗数, tabble2.出让面积,
tabble2.出让收入, tabble2.出让收益,
table3.经营性用地宗数, table3.经营性用地面积,
table3.经营性用地收入, table3.经营性用地收益,
table1.批准日期
FROM table1,
tabble2,
tabble3
where tabble1.地区 = tabble2.地区
and tabble1.地区 = tabble3.地区
and tabble1.县市区 = tabble2.县市区
and tabble1.县市区 = tabble3.县市区
SELECT table1.地区, table2.县市区,
tabble1.总宗数, tabble1.总面积,
tabble1.总收入, tabble1.总收益,
tabble2.出让宗数, tabble2.出让面积,
tabble2.出让收入, tabble2.出让收益,
table3.经营性用地宗数, table3.经营性用地面积,
table3.经营性用地收入, table3.经营性用地收益,
table1.批准日期
FROM table1,
tabble2,
tabble3
where tabble1.地区 = tabble2.地区
and tabble1.地区 = tabble3.地区
and tabble1.县市区 = tabble2.县市区
and tabble1.县市区 = tabble3.县市区
查询结果变成了6*5*5条记录。对了,我这三个table是视图。会不会不能对视图联合查询啊?
FROM table1,
tabble2,
tabble3
where tabble1.地区 = tabble2.地区
and tabble2.地区 = tabble3.地区
and tabble1.县市区 = tabble2.县市区
and tabble2.县市区 = tabble3.县市区
我觉得你最好是改写成 join 的形式
select *
from table1 t1 join table2 t2
on (t1.地区 = t2.地区
and t1.县市区 = t2.县市区) join table3
on (t2.地区 = t3.地区
and t2.县市区 = t3.县市区)
--t1 有5 条记录,t2有4 条记录,t3有2 条记录,left join 为5条记录,join 为2条记录
--生成测试数据
create table t1(地区 varchar(10),县市区 varchar(10),字段1 varchar(10),字段2 varchar(10),日期 smalldatetime)
insert into t1
select 'a','a1','1a11','1a12','2004-12-15' union all
select 'b','b1','1b11','1b12','2004-12-15' union all
select 'c','c1','1c11','1c12','2004-12-15' union all
select 'd','d1','1d11','1d12','2004-12-15' union all
select 'e','e1','1e11','1e12','2004-12-15'
go
create table t2(地区 varchar(10),县市区 varchar(10),字段3 varchar(10),字段4 varchar(10),日期 smalldatetime)
insert into t2
select 'a','a1','2a11','2a12','2004-12-15' union all
select 'b','b1','2b11','2b12','2004-12-15' union all
select 'c','c1','2c11','2c12','2004-12-15' union all
select 'e','e1','2e11','2e12','2004-12-15'
gocreate table t3(地区 varchar(10),县市区 varchar(10),字段5 varchar(10),字段6 varchar(10),日期 smalldatetime)
insert into t3
select 'a','a1','3a11','3a12','2004-12-15' union all
select 'b','b1','3b11','3b12','2004-12-15' union all
select null,null,null,null,null
go
--测试
select t1.地区,t1.县市区 ,t1.字段1,t1.字段2,t2.字段3,t2.字段4,t3.字段5,t3.字段6,t1.日期
from t1 join t2
on (t1.地区=t2.地区
and t1.县市区=t2.县市区) join t3
on (t2.地区=t3.地区
and t2.县市区=t3.县市区)
--删除测试数据
drop table t1,t2,t3--测试结果
--1.left join
a a1 1a11 1a12 2a11 2a12 3a11 3a12 15/12/2004 0:00
b b1 1b11 1b12 2b11 2b12 3b11 3b12 15/12/2004 0:00
c c1 1c11 1c12 2c11 2c12 NULL NULL 15/12/2004 0:00
d d1 1d11 1d12 NULL NULL NULL NULL 15/12/2004 0:00
e e1 1e11 1d12 2e11 2e12 NULL NULL 15/12/2004 0:00
--2.joina a1 1a11 1a12 2a11 2a12 3a11 3a12 15/12/2004 0:00
b b1 1b11 1b12 2b11 2b12 3b11 3b12 15/12/2004 0:00
http://community.csdn.net/Expert/topic/3425/3425208.xml?temp=.9495813
的一样。但是我用 zjcxc(邹建) 写的语句改成我的内容,问题依旧。不知道问题出在哪儿啊?
是不是我的是视图,而你们说的是表,就会出现不一样的结果呢?
long111(小虫5254) 你有QQ么?我的QQ是 5421509
依旧!搞不懂了。
create table t1(地区 varchar(10),县市区 varchar(10),字段1 varchar(10),字段2 varchar(10),日期 smalldatetime)
insert into t1
select 'a','a1','1a11','1a12','2004-12-15' union all
select 'a','a1','1a14','1a13','2004-12-15' union all (这段是新加上的)
select 'b','b1','1b11','1b12','2004-12-15' union all
select 'c','c1','1c11','1c12','2004-12-15' union all
select 'd','d1','1d11','1d12','2004-12-15' union all
select 'e','e1','1e11','1e12','2004-12-15'
go
create table t2(地区 varchar(10),县市区 varchar(10),字段3 varchar(10),字段4 varchar(10),日期 smalldatetime)
insert into t2
select 'a','a1','2a11','2a12','2004-12-15' union all
select 'b','b1','2b11','2b12','2004-12-15' union all
select 'c','c1','2c11','2c12','2004-12-15' union all
select 'e','e1','2e11','2e12','2004-12-15'
gocreate table t3(地区 varchar(10),县市区 varchar(10),字段5 varchar(10),字段6 varchar(10),日期 smalldatetime)
insert into t3
select 'a','a1','3a11','3a12','2004-12-15' union all
select 'b','b1','3b11','3b12','2004-12-15' union all
select null,null,null,null,null
go
select * from t2--测试
select t1.地区,t1.县市区 ,t1.字段1,t1.字段2,t2.字段3,t2.字段4,t3.字段5,t3.字段6,t1.日期
from t1 left join t2
on (t1.地区=t2.地区
and t1.县市区=t2.县市区) left join t3
on (t2.地区=t3.地区
and t2.县市区=t3.县市区)
--删除测试数据
drop table t1,t2,t3--测试结果
--1.left join
a a1 1a11 1a12 2a11 2a12 3a11 3a12 2004-12-15 00:00:00
a a1 1a14 1a13 2a11 2a12 3a11 3a12 2004-12-15 00:00:00
b b1 1b11 1b12 2b11 2b12 3b11 3b12 2004-12-15 00:00:00
c c1 1c11 1c12 2c11 2c12 NULL NULL 2004-12-15 00:00:00
d d1 1d11 1d12 NULL NULL NULL NULL 2004-12-15 00:00:00
e e1 1e11 1e12 2e11 2e12 NULL NULL 2004-12-15 00:00:00--2.joina a1 1a11 1a12 2a11 2a12 3a11 3a12 2004-12-15 00:00:00
a a1 1a14 1a13 2a11 2a12 3a11 3a12 2004-12-15 00:00:00
b b1 1b11 1b12 2b11 2b12 3b11 3b12 2004-12-15 00:00:00
我的每个记录都有个UID号,永远都不会重复,能用哪个号么?
table1中有全部的UID号码,其中包括table2的号码,table2中的UID号又包括table3的号码。
呵呵,能用这个号码做么?
tabble1.总宗数, tabble1.总面积,
tabble1.总收入, tabble1.总收益,
tabble2.出让宗数, tabble2.出让面积,
tabble2.出让收入, tabble2.出让收益,
table3.经营性用地宗数, table3.经营性用地面积,
table3.经营性用地收入, table3.经营性用地收益,
table1.批准日期
FROM table1 left join table2 on table1.uid=table2.uid
left join table3 on table1.uid=table3.uid