有两个表album 和photo 表
结构如下
album:
Albums_ID Albums_UserID Albums_Name
1 1 阳光小美女
2 1 天使的翅膀photo:
Photo_ID Photo_AlbumsID Photo_Simg Photo_Addate
1 1 upload/a.jpg 2009-10-1
2 1 upload/b.jpg 2009-10-2
3 1 upload/c.jpg 2009-10-4
4 1 upload/d.jpg 2009-10-3
5 2 upload/e.jpg 2009-10-3
6 2 upload/f.jpg 2009-10-4
7 2 upload/g.jpg 2009-10-5其中Albums_ID和Photo_AlbumsID关联,要求结果统计某用户的图片数和最近一次上传图片的路径
结果如下:
Albums_ID Albums_Name photosnum photo_simg photo_addate
1 阳光小美女 4 upload/c.jpg 2009-10-4
2 天使的翅膀 3 upload/g.jpg 2009-10-5
其中where Albums_UserID=1 求一条sql语句 谢谢大侠们
结构如下
album:
Albums_ID Albums_UserID Albums_Name
1 1 阳光小美女
2 1 天使的翅膀photo:
Photo_ID Photo_AlbumsID Photo_Simg Photo_Addate
1 1 upload/a.jpg 2009-10-1
2 1 upload/b.jpg 2009-10-2
3 1 upload/c.jpg 2009-10-4
4 1 upload/d.jpg 2009-10-3
5 2 upload/e.jpg 2009-10-3
6 2 upload/f.jpg 2009-10-4
7 2 upload/g.jpg 2009-10-5其中Albums_ID和Photo_AlbumsID关联,要求结果统计某用户的图片数和最近一次上传图片的路径
结果如下:
Albums_ID Albums_Name photosnum photo_simg photo_addate
1 阳光小美女 4 upload/c.jpg 2009-10-4
2 天使的翅膀 3 upload/g.jpg 2009-10-5
其中where Albums_UserID=1 求一条sql语句 谢谢大侠们
(select m.Albums_ID , m.Albums_Name , count(1) photosnum from album m, photo n where m.Albums_ID = m.Photo_AlbumsID group by m.Albums_ID) t1,
(select t.* from photo t where Photo_Addate = (select max(Photo_Addate) from photo where Photo_AlbumsID = t.Photo_AlbumsID)) t2
where t1.Albums_ID = t2.Photo_AlbumsID
a.Albums_ID,
a.Albums_Name,
b.photosnum,
c.photo_simg,
c.photo_addate
from
album a
left join
(select Photo_AlbumsID,count(1) as photosnum from photo group by Photo_AlbumsID) b
on
a.Albums_UserID=b.Photo_AlbumsID
left join
photo c
on
a.Albums_UserID=c.Photo_AlbumsID
and
not exists(select 1 from photo where Albums_UserID=c.Photo_AlbumsID and photo_addate>c.photo_addate)
insert into album values(1 , 1 , '阳光小美女')
insert into album values(2 , 1 , '天使的翅膀')
create table photo(Photo_ID int, Photo_AlbumsID int,Photo_Simg varchar(20),Photo_Addate datetime)
insert into photo values(1 , 1 , 'upload/a.jpg' , '2009-10-1')
insert into photo values(2 , 1 , 'upload/b.jpg' , '2009-10-2')
insert into photo values(3 , 1 , 'upload/c.jpg' , '2009-10-4')
insert into photo values(4 , 1 , 'upload/d.jpg' , '2009-10-3')
insert into photo values(5 , 2 , 'upload/e.jpg' , '2009-10-3')
insert into photo values(6 , 2 , 'upload/f.jpg' , '2009-10-4')
insert into photo values(7 , 2 , 'upload/g.jpg' , '2009-10-5')
goselect t1.* , t2.photo_simg , t2.photo_addate from
(select m.Albums_ID , m.Albums_Name , count(1) photosnum from album m, photo n where m.Albums_ID = n.Photo_AlbumsID group by m.Albums_ID , m.Albums_Name) t1,
(select t.* from photo t where Photo_Addate = (select max(Photo_Addate) from photo where Photo_AlbumsID = t.Photo_AlbumsID)) t2
where t1.Albums_ID = t2.Photo_AlbumsID
order by t1.Albums_IDdrop table album , photo /*
Albums_ID Albums_Name photosnum photo_simg photo_addate
----------- ----------- ----------- -------------------- ------------------------------------------------------
1 阳光小美女 4 upload/c.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 3 upload/g.jpg 2009-10-05 00:00:00.000(所影响的行数为 2 行)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-15 15:37:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[album]
if object_id('[album]') is not null drop table [album]
go
create table [album]([Albums_ID] int,[Albums_UserID] int,[Albums_Name] varchar(10))
insert [album]
select 1,1,'阳光小美女' union all
select 2,1,'天使的翅膀'
--> 测试数据:[photo]
if object_id('[photo]') is not null drop table [photo]
go
create table [photo]([Photo_ID] int,[Photo_AlbumsID] int,[Photo_Simg] varchar(12),[Photo_Addate] datetime)
insert [photo]
select 1,1,'upload/a.jpg','2009-10-1' union all
select 2,1,'upload/b.jpg','2009-10-2' union all
select 3,1,'upload/c.jpg','2009-10-4' union all
select 4,1,'upload/d.jpg','2009-10-3' union all
select 5,2,'upload/e.jpg','2009-10-3' union all
select 6,2,'upload/f.jpg','2009-10-4' union all
select 7,2,'upload/g.jpg','2009-10-5'
--------------开始查询--------------------------
select
a.[Albums_ID],a.[Albums_Name],b.[Photo_Simg],b.[Photo_Addate]
from
[album] a
left join
(select
[Photo_AlbumsID],[Photo_Simg],[Photo_Addate]
from
[photo] t
where not exists(select 1 from [photo] where [Photo_AlbumsID]=t.[Photo_AlbumsID] and photo_addate>t.photo_addate))b on
a.[Albums_ID]=b.[Photo_AlbumsID]
----------------结果----------------------------
/*Albums_ID Albums_Name Photo_Simg Photo_Addate
----------- ----------- ------------ -----------------------
1 阳光小美女 upload/c.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 upload/g.jpg 2009-10-05 00:00:00.000(2 行受影响)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-15 15:37:09
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[album]
if object_id('[album]') is not null drop table [album]
go
create table [album]([Albums_ID] int,[Albums_UserID] int,[Albums_Name] varchar(10))
insert [album]
select 1,1,'阳光小美女' union all
select 2,1,'天使的翅膀'
--> 测试数据:[photo]
if object_id('[photo]') is not null drop table [photo]
go
create table [photo]([Photo_ID] int,[Photo_AlbumsID] int,[Photo_Simg] varchar(12),[Photo_Addate] datetime)
insert [photo]
select 1,1,'upload/a.jpg','2009-10-1' union all
select 2,1,'upload/b.jpg','2009-10-2' union all
select 3,1,'upload/c.jpg','2009-10-4' union all
select 4,1,'upload/d.jpg','2009-10-3' union all
select 5,2,'upload/e.jpg','2009-10-3' union all
select 6,2,'upload/f.jpg','2009-10-4' union all
select 7,2,'upload/g.jpg','2009-10-5'
--------------开始查询--------------------------
select
a.[Albums_ID],a.[Albums_Name],b.[Photo_Simg],b.[Photo_Addate]
from
[album] a
left join
(select
[Photo_AlbumsID],[Photo_Simg],[Photo_Addate]
from
[photo] t
where
not exists(select 1 from [photo] where [Photo_AlbumsID]=t.[Photo_AlbumsID] and photo_addate>t.photo_addate))b
on
a.[Albums_ID]=b.[Photo_AlbumsID]
----------------结果----------------------------
/*Albums_ID Albums_Name Photo_Simg Photo_Addate
----------- ----------- ------------ -----------------------
1 阳光小美女 upload/c.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 upload/g.jpg 2009-10-05 00:00:00.000(2 行受影响)
*/
if object_id('[album]') is not null drop table [album]
go
create table [album]([Albums_ID] int,[Albums_UserID] int,[Albums_Name] varchar(10))
insert [album]
select 1,1,'阳光小美女' union all
select 2,1,'天使的翅膀'
if object_id('[photo]') is not null drop table [photo]
go
create table [photo]([Photo_ID] int,[Photo_AlbumsID] int,[Photo_Simg] varchar(12),[Photo_Addate] datetime)
insert [photo]
select 1,1,'upload/a.jpg','2009-10-1' union all
select 2,1,'upload/b.jpg','2009-10-2' union all
select 3,1,'upload/c.jpg','2009-10-4' union all
select 4,1,'upload/d.jpg','2009-10-3' union all
select 5,2,'upload/e.jpg','2009-10-3' union all
select 6,2,'upload/f.jpg','2009-10-4' union all
select 7,2,'upload/g.jpg','2009-10-5'
select
a.Albums_ID,
a.Albums_Name,
b.photosnum,
c.photo_simg,
c.photo_addate
from
album a
left join
(select Photo_AlbumsID,count(1) as photosnum from photo group by Photo_AlbumsID) b
on
a.Albums_ID=b.Photo_AlbumsID
left join
photo c
on
a.Albums_ID =c.Photo_AlbumsID
and
not exists(select 1 from photo where Photo_AlbumsID=c.Photo_AlbumsID and photo_addate>c.photo_addate)
--测试结果:
/*
Albums_ID Albums_Name photosnum photo_simg photo_addate
----------- ----------- ----------- ------------ -----------------------
1 阳光小美女 4 upload/c.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 3 upload/g.jpg 2009-10-05 00:00:00.000(2 行受影响)*/
IF OBJECT_ID('album') IS NOT NULL DROP TABLE album
GO
CREATE TABLE album(Albums_ID int,Albums_UserID int, Albums_Name varchar(10))
INSERT album SELECT
1 , 1 , '阳光小美女' UNION ALL select
2 , 1 , '天使的翅膀' IF OBJECT_ID('photo') IS NOT NULL DROP TABLE photo
GO
CREATE TABLE photo(Photo_ID int, Photo_AlbumsID int,Photo_Simg varchar(20),Photo_Addate datetime)
INSERT photo SELECT
1, 1 , 'upload/a.jpg', '2009-10-1' UNION ALL select
2, 1 , 'upload/b.jpg', '2009-10-2' UNION ALL select
3, 1 , 'upload/c.jpg', '2009-10-4' UNION ALL select
4, 1 , 'upload/d.jpg', '2009-10-3' UNION ALL select
5, 2 , 'upload/e.jpg', '2009-10-3' UNION ALL select
6, 2 , 'upload/f.jpg', '2009-10-4' UNION ALL select
7, 2, 'upload/g.jpg', '2009-10-5'select Albums_ID,Albums_Name,
photosnum=count(*),c.photo_simg,c.photo_addate
from album a
left join photo b
on a.Albums_ID=b.Photo_AlbumsID
left join
(select * from photo t
where not exists(select * from photo where Photo_AlbumsID=t.Photo_AlbumsID and Photo_Addate>t.Photo_Addate)
) c
on a.Albums_ID=c.Photo_AlbumsID
where Albums_UserID=1
group by Albums_ID,Albums_Name,c.photo_simg,c.photo_addateAlbums_ID Albums_Name photosnum photo_simg photo_addate
----------- ----------- ----------- -------------------- -----------------------
1 阳光小美女 4 upload/c.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 3 upload/g.jpg 2009-10-05 00:00:00.000(2 行受影响)
如果时间有时分秒的话,也没所谓,不会时分秒也相同吧Photo_ID应该是递增的,也可以使用它来做比较,把photo_addate改为Photo_ID即可
Photo_ID Photo_AlbumsID Photo_Simg Photo_Addate
1 1 upload/a.jpg 2009-10-1
2 1 upload/b.jpg 2009-10-1
3 1 upload/c.jpg 2009-10-1
4 1 upload/d.jpg 2009-10-1
5 2 upload/e.jpg 2009-10-1
6 2 upload/f.jpg 2009-10-1
7 2 upload/g.jpg 2009-10-1结果是Albums_ID Albums_Name photosnum photo_simg photo_addate
1 阳光小美女 4 是一张图片的路径就行(a,b.c,d任何一张) 2009-10-1
2 天使的翅膀 3 是一张图片的路径就行(e,f,g任何一张) 2009-10-1
需要另开一贴吗?在研究你们的语句找不出来怎么改
insert into album values(1 , 1 , '阳光小美女')
insert into album values(2 , 1 , '天使的翅膀')
create table photo(Photo_ID int, Photo_AlbumsID int,Photo_Simg varchar(20),Photo_Addate datetime)
insert into photo values(1 , 1 , 'upload/a.jpg' , '2009-10-1')
insert into photo values(2 , 1 , 'upload/b.jpg' , '2009-10-2')
insert into photo values(3 , 1 , 'upload/c.jpg' , '2009-10-4')
insert into photo values(4 , 1 , 'upload/d.jpg' , '2009-10-4') --这里改了时间.
insert into photo values(5 , 2 , 'upload/e.jpg' , '2009-10-3')
insert into photo values(6 , 2 , 'upload/f.jpg' , '2009-10-4')
insert into photo values(7 , 2 , 'upload/g.jpg' , '2009-10-5')
goselect t1.* , t2.photo_simg , t2.photo_addate from
(select m.Albums_ID , m.Albums_Name , count(1) photosnum from album m, photo n where m.Albums_ID = n.Photo_AlbumsID group by m.Albums_ID , m.Albums_Name) t1,
(select t.* from photo t where not exists (select 1 from photo where Photo_AlbumsID = t.Photo_AlbumsID and (Photo_Addate > t.Photo_Addate or (Photo_Addate = t.Photo_Addate and Photo_ID > t.Photo_ID) ) )) t2
where t1.Albums_ID = t2.Photo_AlbumsID
order by t1.Albums_IDdrop table album , photo /*
Albums_ID Albums_Name photosnum photo_simg photo_addate
----------- ----------- ----------- -------------------- ------------------------------------------------------
1 阳光小美女 4 upload/d.jpg 2009-10-04 00:00:00.000
2 天使的翅膀 3 upload/g.jpg 2009-10-05 00:00:00.000(所影响的行数为 2 行)
*/
a.Albums_ID,
a.Albums_Name,
b.photosnum,
c.photo_simg,
c.photo_addate
from
album a
left join
(select Photo_AlbumsID,count(1) as photosnum from photo group by Photo_AlbumsID) b
on
a.Albums_ID=b.Photo_AlbumsID
left join
photo c
on
a.Albums_ID =c.Photo_AlbumsID
and
not exists(select 1 from photo where Photo_AlbumsID=c.Photo_AlbumsID and Photo_ID>c.Photo_ID)
你另外开一贴的话我不反对