跪求一条SQL命令....蛮复杂的..
表：
AddDate Class
2009-10-30 02:00:00 新闻
2009-10-29 02:00:00 新闻
2009-10-28 02:00:00 新闻
2009-10-20 02:00:00 娱乐
2009-10-29 02:00:00 情感
得出：
栏目几天未更新!（也就是取各栏目最大的时间 和 当前时间做比较得出结果）
预想结果：
栏目 未更新时间（单位：天）
娱乐 13
情感 4
新闻 3
[解决办法]
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([AddDate] datetime,[Class] varchar(4))
insert [TB]
select '2009-10-30 02:00:00','新闻' union all
select '2009-10-29 02:00:00','新闻' union all
select '2009-10-28 02:00:00','新闻' union all
select '2009-10-20 02:00:00','娱乐' union all
select '2009-10-29 02:00:00','情感'
SELECT
Class,
MAX(DATEDIFF(DD,AddDate,GETDATE()))AS [时间]
FROM
TB
GROUP BY
Class
ORDER BY
[时间] DESC
（所影响的行数为 5 行）
Class 时间
----- -----------
娱乐 13
新闻 5
情感 4
（所影响的行数为 3 行）
if object_id('[TB]') is not null drop table [TB]
create table [TB]([AddDate] datetime,[Class] varchar(4))
insert [TB]
select '2009-10-30 02:00:00','新闻' union all
select '2009-10-29 02:00:00','新闻' union all
select '2009-10-28 02:00:00','新闻' union all
select '2009-10-20 02:00:00','娱乐' union all
select '2009-10-29 02:00:00','情感'
select datediff(day,max([AddDate]),getdate()) as '未更新时间',[Class] from [TB] group by [Class]
未更新时间 Class
----------- -----
4 情感
3 新闻
13 娱乐
(3 行受影响)----------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-11-02 09:01:55
-- 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)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([AddDate] datetime,[Class] varchar(4))
insert [tb]
select '2009-10-30 02:00:00','新闻' union all
select '2009-10-29 02:00:00','新闻' union all
select '2009-10-28 02:00:00','新闻' union all
select '2009-10-20 02:00:00','娱乐' union all
select '2009-10-29 02:00:00','情感'
--------------开始查询--------------------------
select
[Class] as 栏目,
datediff(day,max([AddDate]),getdate()) as 未更新时间
from
[TB]
group by
[Class]
----------------结果----------------------------
/* 栏目 未更新时间
---- -----------
情感 4
新闻 3
娱乐 13
(3 行受影响)
*/
----------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-11-02 09:01:55
-- 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)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([AddDate] datetime,[Class] varchar(4))
insert [tb]
select '2009-10-30 02:00:00','新闻' union all
select '2009-10-29 02:00:00','新闻' union all
select '2009-10-28 02:00:00','新闻' union all
select '2009-10-20 02:00:00','娱乐' union all
select '2009-10-29 02:00:00','情感'
--------------开始查询--------------------------
select
[Class] as 栏目,
datediff(day,[AddDate],getdate()) as 未更新时间
from
[TB] t
where
not exists(select 1 from tb where Class=t.Class and AddDate>t.AddDate)
----------------结果----------------------------
/* 栏目 未更新时间
---- -----------
情感 4
新闻 3
娱乐 13
(3 行受影响)
*/