表A是栏目表与B对应,B为文章内容表ID NAME
1 文章
2 图片表Bid A.ID title datatime
1 1 123 2009-1-1
2 1 1234 2009-1-2
3 2 1111 2009-1-1我现在要查询的是栏目列表也就是表A的ID,name,和表A的ID,TITLE。并且每个栏目只会查询出一条最新记录
也就是查询出来是A.id name B.id title
1 文章 2 1234
2 图片 3 1111 求教MSSQL查询语句怎么写,谢谢
1 文章
2 图片表Bid A.ID title datatime
1 1 123 2009-1-1
2 1 1234 2009-1-2
3 2 1111 2009-1-1我现在要查询的是栏目列表也就是表A的ID,name,和表A的ID,TITLE。并且每个栏目只会查询出一条最新记录
也就是查询出来是A.id name B.id title
1 文章 2 1234
2 图片 3 1111 求教MSSQL查询语句怎么写,谢谢
WHERE NOT exists(SELECT 1 FROM b WHERE aid=bb.aid AND DATETIME>bb.datetime)
FROM A ,B ON A.id=B.aid
WHERE [DATETIME]=(SELECT MAX([DATETIME] FROM b WHERE aid=B.aid AND )
A inner join B b1 on A.ID=B1.AID
where not exists
(
select 1 from B where b1.id=id and b.datatime<datatime
)
from a, b t
where a.id = t.id
and t.datatime = (select max(datatime) from b where [A.ID] = t.[A.ID])
order by a.id
A.id,NAME,B.id bid,title
FROM
A ,B
where
A.id=B.aid
and
[DATETIME]=(SELECT MAX([DATETIME] FROM b WHERE aid=B.aid )
Select * from
A inner join B b1 on A.ID=B1.AID
where not exists
(
select 1 from B where b.aid=aid and b.datatime<datatime
)
A.id,NAME,B.id bid,title
FROM
A ,B t
where
A.id=B.aid
and
[DATETIME]=(SELECT MAX([DATETIME] FROM b WHERE aid=t.aid )
insert into a values(1 , '文章')
insert into a values(2 , '图片')
create table b(id int,[A.ID] int, title varchar(10) , datatime varchar(10))
insert into b values(1 , 1 , '123' , '2009-1-1')
insert into b values(2 , 1 , '1234' , '2009-1-2')
insert into b values(3 , 2 , '1111' , '2009-1-1')
goselect a.id , a.name , t.id , t.title , t.datatime
from a, b t
where a.id = t.[A.ID]
and t.datatime = (select max(datatime) from b where [A.ID] = t.[A.ID])
order by a.iddrop table a , b /*
id name id title datatime
----------- ---------- ----------- ---------- ----------
1 文章 2 1234 2009-1-2
2 图片 3 1111 2009-1-1(所影响的行数为 2 行)
*/
insert into a values(1 , '文章')
insert into a values(2 , '图片')
create table b(id int,[A.ID] int, title varchar(10) , datatime varchar(10))
insert into b values(1 , 1 , '123' , '2009-1-1')
insert into b values(2 , 1 , '1234' , '2009-1-2')
insert into b values(3 , 2 , '1111' , '2009-1-1')
goselect a.id , a.name , t.id , t.title , t.datatime
from a, b t
where a.id = t.[A.ID]
and t.datatime = (select max(datatime) from b where [A.ID] = t.[A.ID])
order by a.idselect a.id , a.name , t.id , t.title , t.datatime
from a, b t
where a.id = t.[A.ID]
and not exists (select 1 from b where [A.ID] = t.[A.ID] and datatime > t.datatime)
order by a.id
drop table a , b /*
id name id title datatime
----------- ---------- ----------- ---------- ----------
1 文章 2 1234 2009-1-2
2 图片 3 1111 2009-1-1(所影响的行数为 2 行)id name id title datatime
----------- ---------- ----------- ---------- ----------
1 文章 2 1234 2009-1-2
2 图片 3 1111 2009-1-1(所影响的行数为 2 行)
*/