日期:2014-05-18  浏览次数:20357 次

跪求一条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



------解决方案--------------------
探讨
SQL code--> 测试数据:[TB]ifobject_id('[TB]')isnotnulldroptable[TB]createtable[TB]([AddDate]datetime,[Class]varchar(4))insert[TB]select'2009-10-30 02:00:00','新闻'unionallselect'2009-10-29 02:00:00','新闻'unio¡­

------解决方案--------------------
SQL code
--> 测试数据:[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 行)

------解决方案--------------------
SQL code
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 行受影响)

------解决方案--------------------
SQL code
----------------------------
-- 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 行受影响)
*/

------解决方案--------------------
SQL code
----------------------------
-- 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