三表连查
有三表
TA
ID XX YY
1 x1 y1
2 x2 y2
TB(本表的唯一性ID我不写了)
ID TIME
1 2013.1.4
1 2013.1.6
1 2013.1.5
2 2013.1.11
TC
ID NAME PHOTO
1 AA xyxy
2 BB abab
求SQL语句以得到如下表
ID XX YY 行数 最新时间 PHOTO
1 x1 y1 3 2013.1.6 xyxy
2 x2 y2 1 2013.1.11 abab
[解决办法]
select ta.*,b.ct 行数,b.dt 最新时间,c.photo from ta a
inner join (select id,count(1) ct,max([time])dt from tb group by id) b on a.id=b.id
inner join tc c on b.id=c.id
[解决办法]
create table TA
(
ID int,
XX varchar(50),
YY varchar(50)
)
go
insert into TA
select 1,'x1','y1' union all
select 2,'x2','y2'
go
create table TB
(
ID int,
TIME datetime
)
go
insert into TB
select 1,'2013.1.4' union all
select 1,'2013.1.6' union all
select 1,'2013.1.5' union all
select 2,'2013.1.11'
go
create table TC
(
Id int,
Name varchar(50),
PHOTO varchar(50)
)
insert into TC
select 1,'AA','xyxy' union all
select 2,'BB','abab'
go
--查询语句
declare @time datetime
set @time=(select MAX(time) from TB where ID=1)
select TA.ID,TA.XX,TA.YY,temp.calcid '行数',@time '时间',TC.PHOTO
from TA
left join(select ID,COUNT(*) as calcid from TB group by ID) as temp on TA.ID=temp.ID
left join TC on TA.ID=TC.Id
ID XX YY 行数 时间 PHOTO
----------- -------- ------------- --------------------------------------- -----------
1 x1 y1 3 2013-01-06 00:00:00.000 xyxy
2 x2 y2 1 2013-01-06 00:00:00.000 abab
(2 行受影响)
create table TA
(
ID int,
XX varchar(50),
YY varchar(50)
)
go
insert into TA
select 1,'x1','y1' union all
select 2,'x2','y2'
go
create table TB
(
ID int,
TIME datetime
)
go
insert into TB
select 1,'2013.1.4' union all
select 1,'2013.1.6' union all
select 1,'2013.1.5' union all
select 2,'2013.1.11'
go
create table TC
(
Id int,
Name varchar(50),
PHOTO varchar(50)
)
insert into TC
select 1,'AA','xyxy' union all
select 2,'BB','abab'
go
--查询语句
select TA.ID,TA.XX,TA.YY,temp.calcid '行数',temp.time '时间',TC.PHOTO
from TA
left join(select ID,COUNT(*) as calcid,MAX(TIME) as [time] from TB group by ID) as temp on TA.ID=temp.ID
left join TC on TA.ID=TC.Id
//运行结果
ID XX YY 行数 时间 PHOTO
----------- ------ ---------- ----------- ----------------------- --------------
1 x1 y1 3 2013-01-06 00:00:00.000 xyxy
2 x2 y2 1 2013-01-11 00:00:00.000 abab
(2 行受影响)
