declare @a table (daqu_pk_id int,daqu_name varchar(20))
insert into @a select 11,'东北大区'
insert into @a select 22,'西北大区'
declare @b table (daqu_fk_id int,xiaoqu_pk_id int,xiaoqu_name varchar(20))
insert into @b select 11,1101,'黑龙江'
insert into @b select 22,2201,'某某'
insert into @b select 11,1102,'辽宁'
declare @c table (daqu_fk_id int,xiaoqu_pk_id int,dian_pk_id int,dian_name varchar(20))
insert into @c select 11,1101,110101,'黑龙江某区'
insert into @c select 11,1102,110201,'辽宁某区'
insert into @c select 22,2201,220101,'西北某某某某区'select a.daqu_name,b.xiaoqu_name,c.dian_name from
@a a join @b b on a.daqu_pk_id=b.daqu_fk_id
join @c c on b.xiaoqu_pk_id=c.xiaoqu_pk_id
order by a.daqu_namedaqu_name xiaoqu_name dian_name
东北大区 黑龙江 黑龙江某区
东北大区 辽宁 辽宁某区
西北大区 某某 西北某某某某区
insert into @a select 11,'东北大区'
insert into @a select 22,'西北大区'
declare @b table (daqu_fk_id int,xiaoqu_pk_id int,xiaoqu_name varchar(20))
insert into @b select 11,1101,'黑龙江'
insert into @b select 22,2201,'某某'
insert into @b select 11,1102,'辽宁'
declare @c table (daqu_fk_id int,xiaoqu_pk_id int,dian_pk_id int,dian_name varchar(20))
insert into @c select 11,1101,110101,'黑龙江某区'
insert into @c select 11,1102,110201,'辽宁某区'
insert into @c select 22,2201,220101,'西北某某某某区'select a.daqu_name,b.xiaoqu_name,c.dian_name from
@a a join @b b on a.daqu_pk_id=b.daqu_fk_id
join @c c on b.xiaoqu_pk_id=c.xiaoqu_pk_id
order by a.daqu_namedaqu_name xiaoqu_name dian_name
东北大区 黑龙江 黑龙江某区
东北大区 辽宁 辽宁某区
西北大区 某某 西北某某某某区
from 表A a,表B b,表C c
where a.daqu_pk_id=b.daqu_fk_id and
b.daqu_fk_id=c.daqu_fk_id and b.xiaoqu_pk_id =c.xiao_fk_id
from 表A a,表B b,表C c
where a.daqu_pk_id=c.daqu_fk_id
and b.xiaoqu_pk_id =c.xiao_fk_id
select a.daqu_name,b.xiaoqu_name,c.dian_name from c
left join a on c.daqu_fk_id=a.daqu_pk_id
left join b on c.xiaoqu_pk_id=b.xiaoqu_pk_id
insert into @a select 11,'东北大区'
insert into @a select 22,'西北大区'
declare @b table (daqu_fk_id int,xiaoqu_pk_id int,xiaoqu_name varchar(20))
insert into @b select 11,1101,'黑龙江'
insert into @b select 22,2201,'某某'
insert into @b select 11,1102,'辽宁'
declare @c table (daqu_fk_id int,xiaoqu_pk_id int,dian_pk_id int,dian_name varchar(20))
insert into @c select 11,1101,110101,'黑龙江某区'
insert into @c select 11,1102,110201,'辽宁某区'
insert into @c select 22,2201,220101,'西北某某某某区'
select daqu_name,xiaoqu_name,dian_name
from @a a,@b b,@c c
where a.daqu_pk_id = b.daqu_fk_id and b.xiaoqu_pk_id = c.xiaoqu_pk_id
order by daqu_name/*
daqu_name xiaoqu_name dian_name
-------------------- -------------------- --------------------
东北大区 黑龙江 黑龙江某区
东北大区 辽宁 辽宁某区
西北大区 某某 西北某某某某区(所影响的行数为 3 行)
*/select daqu_name,xiaoqu_name,dian_name
from @a a
left join @b b on a.daqu_pk_id = b.daqu_fk_id
left join @c c on b.xiaoqu_pk_id = c.xiaoqu_pk_id
order by daqu_name
/*daqu_name xiaoqu_name dian_name
-------------------- -------------------- --------------------
东北大区 黑龙江 黑龙江某区
东北大区 辽宁 辽宁某区
西北大区 某某 西北某某某某区(所影响的行数为 3 行)*/select daqu_name,xiaoqu_name,dian_name
from @a a
right join @b b on a.daqu_pk_id = b.daqu_fk_id
right join @c c on b.xiaoqu_pk_id = c.xiaoqu_pk_id
order by daqu_name
declare @a table (daqu_pk_id int,daqu_name varchar(20))
insert into @a select 11,'东北大区'
insert into @a select 22,'西北大区'
declare @b table (daqu_fk_id int,xiaoqu_pk_id int,xiaoqu_name varchar(20))
insert into @b select 11,1101,'黑龙江'
insert into @b select 22,2201,'某某'
insert into @b select 11,1102,'辽宁'
declare @c table (daqu_fk_id int,xiaoqu_pk_id int,dian_pk_id int,dian_name varchar(20))
insert into @c select 11,1101,110101,'黑龙江某区'
insert into @c select 11,1102,110201,'辽宁某区'
insert into @c select 22,2201,220101,'西北某某某某区'select a.daqu_name,b.xiaoqu_name,c.dian_name from @a a
inner join @b b
on a.daqu_pk_id=b.daqu_fk_id
inner join @c c
on b.xiaoqu_pk_id=c.xiaoqu_pk_id
order by a.daqu_pk_id
接分
------通过表连接起来就行了
INNER指定返回每对匹配的行。废弃两个表中不匹配的行。如果未指定联接类型,则这是默认设置。FULL [OUTER]指定在结果集中包含左表或右表中不满足联接条件的行,并将对应于另一个表的输出列设为 NULL。这是对通常由 INNER JOIN 返回的所有行的补充。说明 按此处指定的方法指定外联接或在 WHERE 子句中使用旧式非标准的 *= 和 =* 运算符都是可行的。不能在同一语句中同时使用这两种方法。
LEFT [OUTER]指定在结果集中包含左表中所有不满足联接条件的行,且在由内联接返回所有的行之外,将另外一个表的输出列设为 NULL。RIGHT [OUTER]指定在结果集中包含右表中所有不满足联接条件的行,且在由内联接返回的所有行之外,将与另外一个表对应的输出列设为 NULL。-------------------------
declare @a table (daqu_pk_id int,daqu_name varchar(20))
insert into @a select 11,'东北大区'
insert into @a select 22,'西北大区'declare @b table (daqu_fk_id int,xiaoqu_pk_id int,xiaoqu_name varchar(20))
insert into @b select 11,1101,'黑龙江'
insert into @b select 22,2201,'某某'
insert into @b select 11,1102,'辽宁'declare @c table (daqu_fk_id int,xiaoqu_pk_id int,dian_pk_id int,dian_name varchar(20))
insert into @c select 11,1101,110101,'黑龙江某区'
insert into @c select 11,1102,110201,'辽宁某区'
insert into @c select 22,2201,220101,'西北某某某某区'
select
daqu_name,
xiaoqu_name,
dian_name
from
@a a
inner join
@b b on a.daqu_pk_id=b.daqu_fk_id
inner join
@c c on b.xiaoqu_pk_id=c.xiaoqu_pk_id
order by daqu_namedaqu_name xiaoqu_name dian_name
-------------------- -------------------- --------------------
东北大区 黑龙江 黑龙江某区
东北大区 辽宁 辽宁某区
西北大区 某某 西北某某某某区(所影响的行数为 3 行)