怎么通过Select选出非重复标题的记录,并且对已重复的标题进行计数
现有的表table记录如下:
ID title cont1 cont2 addtime
1 nihao 2 3 2011-7-27
2 nihao2 4 5 2011-7-28
3 nihao 6 7 2011-7-29
4 nihao3 8 9 2011-7-20
5 nihao 10 11 2011-7-21
6 nihao2 12 13 2011-7-22
结果得到(只查询标题是否重复,如果重复就只显示最新一条,并显示重复计数值)
1 nihao 2 3 2011-7-27 3
2 nihao2 4 5 2011-7-28 2
4 nihao3 8 9 2011-7-20 1
------解决方案--------------------
- SQL code
------------------------------ Author :fredrickhu(小F,向高手学习)-- Date :2012-07-17 15:04:59-- Version:-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) -- Apr 22 2011 11:57:00 -- Copyright (c) Microsoft Corporation-- Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)--------------------------------> 测试数据:[table]if object_id('[table]') is not null drop table [table]go create table [table]([ID] int,[title] varchar(6),[cont1] int,[cont2] int,[addtime] datetime)insert [table]select 1,'nihao',2,3,'2011-7-27' union allselect 2,'nihao2',4,5,'2011-7-28' union allselect 3,'nihao',6,7,'2011-7-29' union allselect 4,'nihao3',8,9,'2011-7-20' union allselect 5,'nihao',10,11,'2011-7-21' union allselect 6,'nihao2',12,13,'2011-7-22'--------------开始查询--------------------------select *,(select count(1) from [table] where title=t.title) from [table] twhere not exists(select 1 from [table] where title=t.title and addtime>t.addtime)----------------结果----------------------------/* ID title cont1 cont2 addtime ----------- ------ ----------- ----------- ----------------------- -----------2 nihao2 4 5 2011-07-28 00:00:00.000 23 nihao 6 7 2011-07-29 00:00:00.000 34 nihao3 8 9 2011-07-20 00:00:00.000 1(3 行受影响)*/