sql2005中多表连接查询的用法

--创建视图 显示子版区的详细信息
if exists(select * from dbo.sysobjects
where id=object_id('dbo.v_cake') and objectproperty(id,'isview')=1)
drop view v_cake
go
create view v_cake
as
select cakeid,parentid,img,cakename,describe,position,
(select count(*) from topic where topic.cakeid=cake.cakeid) as topicnum,
(select count(*) from topic where topic.cakeid=cake.cakeid)+
(select count(*) from replies where replies.topicid in
(select topicid from topic where topic.cakeid=cake.cakeid)) as msgnum,
(select count(*) from topic where topic.cakeid=cake.cakeid and datediff(dd,emittime,getdate())=0) as todaynum,
(select top 1 uid from topic where topic.cakeid=cake.cakeid order by emittime desc) as lastone,
(select top 1 emittime from topic where topic.cakeid=cake.cakeid order by emittime desc) as lasttime
from cake
go

以某个字段进行关联，就以这个字段为条件进行查询就可以

连接查询还是很重要的，以后的开发中少不了要有统计模块，这个统计经常会涉及多个表的查询

其主要有：内连接、外连接（左外连接、右外连接）

select 列1,列2 from 表1 inner join 表2 on 表1.列名=表2.列名 inner join 表3 on 表1.列名=表2.列名 [where 条件]

select 列1,列2 from 表1 left join 表2 on 表1.列名=表2.列名 //左连接 以表1为主表

select 列1,列2 from 表1,表2 where 表1.列名=表2.列名

inner join...on 内连接

left join...on 左外连接

right join...on 右外连接