SELECT
A.ConfirmationCodeID, B.OverdueDate, C.CourseName
FROM
A
INNER JOIN B ON A.ConfirmationCodeID=B.id
INNER JOIN C ON B.CourseID=C.id
WHERE
B.OverdueDate=(SELECT MAX(OverdueDate) FROM b AS t WHERE ConfirmationCodeID=b.ConfirmationCodeID)这样有效果没?
A.ConfirmationCodeID, B.OverdueDate, C.CourseName
FROM
A
INNER JOIN B ON A.ConfirmationCodeID=B.id
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 CourseName order by 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
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
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)
ConfirmationCodeID OverdueDate CourseName
1 2011 数学
3 2014 数学
4 2011 语文我希望的结果
ConfirmationCodeID OverdueDate CourseName
3 2014 数学
4 2011 语文原来 第1 2 行 对比 时间后 取大的
-- 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 语文*/自己去修改一下吧。
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
(ConfirmationCodeID, OverdueDate,CourseName,Rank )
AS
(
SELECT A.ConfirmationCodeID,
B.OverdueDate,
C.CourseName ,
RANK() OVER (PARTITION BY C.CourseName ORDER BY B.OverdueDate desc) AS Rank
FROM A
INNER JOIN B ON A.ConfirmationCodeID = B.ConfirmationCodeID
INNER JOIN C ON B.CourseID = C.CourseID
)
SELECT * FROM STAGING
WHERE RANK = 1
AND A.v=26 请指教
造成 ConfirmationCodeID 没地方 放了
试试这个:
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 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
多谢 这个 是我需要的能不能 简单介绍一下另外 ROW_NUMBER() over 有没有版本要求
然后 挑选上边结果 rownum = 1 的
试试这个:
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
多谢 这个 是我需要的能不能 简单介绍一下另外 ROW_NUMBER() over 有没有版本要求2005及以上的都能用的,2000好像不能用