27,579
社区成员
发帖
与我相关
我的任务
分享
select top 20 tagcode,tagname
from tag a
where not exists(select 1 from tag where tagname=a.tagname and maintaindate>a.maintaindate)
order by maintaindate desc,tagname
/*
tagcode tagname
----------- --------------------------------------------------
11127 高中以上
11126 软件学院
11122 Master
11120 哈师大
11123 碩士
11119 大學
11118 Bachelor
11117 广告
11113 Aston university
11115 International Trade
11114 TungHai university
11105 道学研究
11101 清华
11104 黑龙江大学
11096 Doctor
11095 fdfd
11094 fff
5645 142142142
6081 24124321
6619 Unlimited
(20 row(s) affected)
*/
SELECT TOP 20 a.tagcode,a.tagname FROM tag a
INNER JOIN
(SELECT MAX(maintaindate) mm,tagname FROM tag GROUP BY tagname) b
ON a.tagname=b.tagname AND mm=maintaindate
ORDER BY mm DESC
--以下这种写法是错的,语法未错,语意大错,它不是表中的数据原型,只是一种聚合结果。
SELECT TOP 20 MAX(tagcode),tagname
FROM tag
GROUP BY tagname
ORDER BY MAX(maintaindate) DESC
IF OBJECT_ID('tag','u') IS NOT NULL
DROP TABLE tag
GO
CREATE TABLE tag([tagcode] INT,[tagname] NVARCHAR(50),[usecount] INT,[maintaindate] DATETIME,[tagtype] TINYINT)
GO
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11086,N'博士',0,'2008-03-20 08:26:23.140',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11088,N'專科以上',1,'2008-03-20 08:26:23.140',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11094,N'fff',0,'2008-04-21 15:36:31.617',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11095,N'fdfd',0,'2008-04-21 15:36:31.617',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11096,N'Doctor',1,'2008-10-09 14:45:33.097',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11101,N'清华',0,'2008-10-24 10:46:06.063',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11102,N'黑龙江大学',0,'2008-10-23 14:39:33.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11103,N'黑龙江大学',0,'2008-10-23 15:35:10.687',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11104,N'黑龙江大学',0,'2008-10-23 15:36:25.157',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11105,N'道学研究',0,'2008-10-24 10:46:06.063',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11113,N'Aston university',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11114,N'TungHai university',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11115,N'International Trade',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11117,N'广告',0,'2008-10-27 10:46:50.653',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11118,N'Bachelor',0,'2008-10-27 10:46:50.653',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11119,N'大學',0,'2008-10-27 10:46:57.970',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11120,N'哈师大',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11122,N'Master',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11123,N'碩士',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11125,N'软件学院',1,'2008-10-28 15:14:25.107',2)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11126,N'软件学院',1,'2008-10-28 15:14:28.937',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11127,N'高中以上',15,'2008-10-28 17:01:15.893',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5605,N'I-Shou University',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5645,N'142142142',0,'2008-03-22 18:41:52.477',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5668,N'私立長榮大學',1,'2008-03-22 18:41:20.067',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5826,N'Thunderbird school of global management',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5867,N'私立中原大學',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5895,N'新竹中學',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5919,N'IED milano',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6067,N'Lancaster University, UK',0,'2008-03-20 12:32:49.780',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6081,N'24124321',0,'2008-03-22 18:41:52.477',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6126,N'Information engineering',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6144,N'Management & Organizational Learning',0,'2008-03-20 12:32:49.780',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6177,N'Product design',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6202,N'大眾傳播學系',0,'2008-03-22 18:41:20.067',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6204,N'工商業設計系',0,'2008-03-20 10:29:53.167',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6229,N'工業設計研究所碩士班/人因工程組',0,'2008-03-20 10:29:53.167',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6336,N'建築(輔系)',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6572,N'機械',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6619,N'Unlimited',1,'2008-03-22 18:41:52.477',1)
GO
--SELECT * FROM tag
SELECT TOP 20 a.* FROM tag a
WHERE NOT EXISTS(
SELECT 1 FROM tag WHERE tagName = a.tagName
AND tagcode > a.tagcode AND (usecount > a.usecount OR usecount=a.usecount AND maintaindate>a.maintaindate) )
ORDER BY usecount DESC,tagcode DESC
/*
11127 高中以上 15 2008-10-28 17:01:15.893 1
11126 软件学院 1 2008-10-28 15:14:28.937 1
11096 Doctor 1 2008-10-09 14:45:33.097 1
11088 專科以上 1 2008-03-20 08:26:23.140 1
6619 Unlimited 1 2008-03-22 18:41:52.477 1
5668 私立長榮大學 1 2008-03-22 18:41:20.067 1
11123 碩士 0 2008-10-28 10:49:47.577 1
11122 Master 0 2008-10-28 10:49:47.577 1
11120 哈师大 0 2008-10-28 10:49:47.577 1
11119 大學 0 2008-10-27 10:46:57.970 1
11118 Bachelor 0 2008-10-27 10:46:50.653 1
11117 广告 0 2008-10-27 10:46:50.653 1
11115 International Trade 0 2008-10-27 10:39:59.320 1
11114 TungHai university 0 2008-10-27 10:39:59.320 1
11113 Aston university 0 2008-10-27 10:39:59.320 1
11105 道学研究 0 2008-10-24 10:46:06.063 1
11104 黑龙江大学 0 2008-10-23 15:36:25.157 1
11101 清华 0 2008-10-24 10:46:06.063 1
11095 fdfd 0 2008-04-21 15:36:31.617 1
11094 fff 0 2008-04-21 15:36:31.617 1
*/
SELECT TOP 20 *
FROM tag
ORDER BY usecount desc,tagcode desc
SELECT TOP 20 tagcode,tagname
FROM tag
ORDER BY maintaindate desc,tagname
codeSELECT TOP 20 tagname
FROM tag
GROUP BY tagName
ORDER BY MAX(maintaindate) desc,tagname
DELETE FROM [tag]
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11086,N'博士',0,'2008-03-20 08:26:23.140',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11088,N'專科以上',1,'2008-03-20 08:26:23.140',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11094,N'fff',0,'2008-04-21 15:36:31.617',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11095,N'fdfd',0,'2008-04-21 15:36:31.617',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11096,N'Doctor',1,'2008-10-09 14:45:33.097',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11101,N'清华',0,'2008-10-24 10:46:06.063',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11102,N'黑龙江大学',0,'2008-10-23 14:39:33.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11103,N'黑龙江大学',0,'2008-10-23 15:35:10.687',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11104,N'黑龙江大学',0,'2008-10-23 15:36:25.157',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11105,N'道学研究',0,'2008-10-24 10:46:06.063',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11113,N'Aston university',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11114,N'TungHai university',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11115,N'International Trade',0,'2008-10-27 10:39:59.320',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11117,N'广告',0,'2008-10-27 10:46:50.653',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11118,N'Bachelor',0,'2008-10-27 10:46:50.653',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11119,N'大學',0,'2008-10-27 10:46:57.970',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11120,N'哈师大',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11122,N'Master',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11123,N'碩士',0,'2008-10-28 10:49:47.577',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11125,N'软件学院',1,'2008-10-28 15:14:25.107',2)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11126,N'软件学院',1,'2008-10-28 15:14:28.937',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (11127,N'高中以上',15,'2008-10-28 17:01:15.893',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5605,N'I-Shou University',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5645,N'142142142',0,'2008-03-22 18:41:52.477',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5668,N'私立長榮大學',1,'2008-03-22 18:41:20.067',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5826,N'Thunderbird school of global management',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5867,N'私立中原大學',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5895,N'新竹中學',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (5919,N'IED milano',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6067,N'Lancaster University, UK',0,'2008-03-20 12:32:49.780',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6081,N'24124321',0,'2008-03-22 18:41:52.477',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6126,N'Information engineering',0,'2008-03-17 06:32:02.333',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6144,N'Management & Organizational Learning',0,'2008-03-20 12:32:49.780',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6177,N'Product design',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6202,N'大眾傳播學系',0,'2008-03-22 18:41:20.067',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6204,N'工商業設計系',0,'2008-03-20 10:29:53.167',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6229,N'工業設計研究所碩士班/人因工程組',0,'2008-03-20 10:29:53.167',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6336,N'建築(輔系)',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6572,N'機械',0,'2008-03-17 03:41:10.133',1)
INSERT INTO [tag] ([tagcode],[tagname],[usecount],[maintaindate],[tagtype]) VALUES (6619,N'Unlimited',1,'2008-03-22 18:41:52.477',1)
create table tag(tagcode int,tagname varchar(100),maintaindate datetime)
insert tag
select 1,'a','2008-1-1' union all
select 1,'a','2008-1-2' union all
select 1,'a','2008-1-3' union all
select 2,'b','2008-2-1' union all
select 2,'b','2008-3-1' union all
select 3,'c','2008-1-1'
select top 20 tagcode,tagname,maintaindate
from
(
SELECT tagcode,tagname,maintaindate=max(maintaindate)
FROM tag
group by tagcode,tagname
) t
ORDER BY maintaindate desc,tagname
/*
tagcode tagname maintaindate
----------- ---------------------------------------------------------------------------------------------------- -----------------------
2 b 2008-03-01 00:00:00.000
1 a 2008-01-03 00:00:00.000
3 c 2008-01-01 00:00:00.000
*/
drop table tag
create table tag(tagcode int,tagname varchar(100),maintaindate datetime)
insert tag
select 1,'a','2008-1-1' union all
select 1,'a','2008-1-2' union all
select 1,'a','2008-1-3' union all
select 2,'b','2008-2-1' union all
select 2,'b','2008-3-1' union all
select 3,'c','2008-1-1'
select top 2 tagcode,tagname,maintaindate
from
(
SELECT tagcode,tagname,maintaindate=max(maintaindate)
FROM tag
group by tagcode,tagname
) t
ORDER BY maintaindate desc,tagname
/*
tagcode tagname maintaindate
----------- ---------------------------------------------------------------------------------------------------- -----------------------
2 b 2008-03-01 00:00:00.000
1 a 2008-01-03 00:00:00.000
*/
drop table tag