日期:2014-05-17 浏览次数:20485 次
select 编号,
(select top 1 站点 from [tb] where 编号=a.编号 group by 站点 order by count(1) desc),
count(1)
from [tb] a
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-05-16 16:21:16
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
-- Jun 17 2011 00:54:03
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([编号] int,[站点] varchar(6),[时间] varchar(8))
insert [huang]
select 1,'北京','2013-1-1' union all
select 2,'石家庄','2013-1-1' union all
select 1,'石家庄','2013-1-2' union all
select 2,'北京','2013-1-2' union all
select 1,'石家庄','2013-1-3' union all
select 2,'北京','2013-1-3'
--------------开始查询-------------------------
select 编号,
(select top 1 站点 from [huang] where 编号=a.编号 group by 站点 order by count(1) desc),
count(1)
from [huang] a
GROUP BY 编号
----------------结果----------------------------
/*
编号