34,593
社区成员
发帖
与我相关
我的任务
分享
select ConfirmationCodeID, OverdueDate, CourseName
from
(
SELECT A.ConfirmationCodeID, B.OverdueDate, C.CourseName ,
ROW_NUMBER() over(partition by CourseName order by OverdueDate desc) rownum
FROM A INNER
JOIN B ON A.ConfirmationCodeID = B.id
INNER JOIN C ON B.CourseID = C.id
where v = 26
)t
where rownum = 1
[/quote]
多谢 这个 是我需要的
能不能 简单介绍一下
另外 ROW_NUMBER() over 有没有版本要求[/quote]
2005及以上的都能用的,2000好像不能用select ConfirmationCodeID, OverdueDate, CourseName
from
(
SELECT A.ConfirmationCodeID, B.OverdueDate, C.CourseName ,
ROW_NUMBER() over(partition by CourseName order by OverdueDate desc) rownum
FROM A INNER
JOIN B ON A.ConfirmationCodeID = B.id
INNER JOIN C ON B.CourseID = C.id
where v = 26
)t
where rownum = 1
[/quote]
多谢 这个 是我需要的
能不能 简单介绍一下
另外 ROW_NUMBER() over 有没有版本要求select ConfirmationCodeID, OverdueDate, CourseName
from
(
SELECT A.ConfirmationCodeID, B.OverdueDate, C.CourseName ,
ROW_NUMBER() over(partition by CourseName order by OverdueDate desc) rownum
FROM A INNER
JOIN B ON A.ConfirmationCodeID = B.id
INNER JOIN C ON B.CourseID = C.id
where v = 26
)t
where rownum = 1
SELECT A.ConfirmationCodeID,
B.OverdueDate,
C.CourseName
FROM A INNER JOIN (select max(OverdueDate)OverdueDate,id
from b
group by id)B ON A.ConfirmationCodeID = B.id
INNER JOIN C ON B.CourseID = C.id
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-03-13 15:31:04
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ConfirmationCodeID] int,[OverdueDate] int,[CourseName] varchar(4))
insert [tb]
select 1,2011,'数学' union all
select 3,2014,'数学' union all
select 4,2011,'语文'
--------------开始查询--------------------------
select * from [tb] t WHERE OverdueDate=(SELECT MAX(OverdueDate) FROM TB WHERE CourseName=t.CourseName)
SELECT * FROM TB t WHERE NOT EXISTS(SELECT 1 FROM TB WHERE CourseName=t.CourseName AND OverdueDate>t.OverdueDate)
----------------结果----------------------------
/* ConfirmationCodeID OverdueDate CourseName
------------------ ----------- ----------
3 2014 数学
4 2011 语文
*/
自己去修改一下吧。V是哪个表的字段?直接加上。
SELECT
A.ConfirmationCodeID, B.OverdueDate, C.CourseName
FROM
A
INNER JOIN B ON A.ConfirmationCodeID=B.id --如果是A表或者B表的 加这里 and a.v=26
INNER JOIN C ON B.CourseID=C.id
WHERE
B.OverdueDate=(SELECT MAX(OverdueDate) FROM b AS t WHERE ConfirmationCodeID=b.ConfirmationCodeID)
select ConfirmationCodeID, OverdueDate, CourseName
from
(
SELECT A.ConfirmationCodeID, B.OverdueDate, C.CourseName ,
ROW_NUMBER() over(partition by C.CourseName order by B.OverdueDate desc) rownum
FROM A INNER
JOIN B ON A.ConfirmationCodeID = B.id
INNER JOIN C ON B.CourseID = C.id
)t
where rownum = 1