SQL高手，请教一SQL语句，想了两天了

[oyh484203]

请教一SQL语句
数据结构如下：
ID com_name cprice startTime
-------------------------------------------
1 公司甲 2000 2007-1-1
2 公司乙 1000 2007-1-1
3 公司丙 3000 2007-1-1
4 公司丁 1000 2007-2-1
5 公司甲 2800 2007-2-1
6 公司丙 3000 2007-2-1
7 公司乙 1500 2007-3-1
8 公司丙 3000 2007-3-1
---------------------------------------------
我想统计出：每月获得标王次数最多的那个公司名

如上表，我想统计的结果是：公司丙

[sp4]

怎么都得内嵌个查询

[fan22176391]

create table test(ID int,com_name varchar(8),cprice int,startTime datetime)
insert into test select 1,'公司甲',2000,'2007-1-1'
union all select 2,'公司乙',1000,'2007-1-1'
union all select 3,'公司丙',3000,'2007-1-1'
union all select 4,'公司丁',1000,'2007-2-1'
union all select 5,'公司甲',2800,'2007-2-1'
union all select 6,'公司丙',3000,'2007-2-1'
union all select 7,'公司乙',1500,'2007-3-1'
union all select 8,'公司丙',3000,'2007-3-1'


select *
from test
where cprice in
(select max(cprice) from test group by startTime)
group by ID,com_name , cprice,startTime


ID com_name cprice startTime

3 公司丙 3000 2007-01-01 00:00:00.000
6 公司丙 3000 2007-02-01 00:00:00.000
8 公司丙 3000 2007-03-01 00:00:00.000

[子陌红尘]

declare @t table(ID int,com_name varchar(8),cprice int,startTime datetime)
insert into @t select 1,'公司甲',2000,'2007-1-1'
insert into @t select 2,'公司乙',1000,'2007-1-1'
insert into @t select 3,'公司丙',3000,'2007-1-1'
insert into @t select 4,'公司丁',1000,'2007-2-1'
insert into @t select 5,'公司甲',2800,'2007-2-1'
insert into @t select 6,'公司丙',3000,'2007-2-1'
insert into @t select 7,'公司乙',1500,'2007-3-1'
insert into @t select 8,'公司丙',3000,'2007-3-1'


select
top 1 a.com_name
from
(select
t.*
from
@t t
where
not exists(select
1
from
@t
where
startTime=t.startTime
and
cprice>t.cprice)) a
group by
a.com_name
order by
count(a.ID) desc

/*
com_name
--------
公司丙
*/

[hrb2008]

select top 1 from
(select com_name,count(1) as cnt from tb group by com_name order by cnt desc) b

[dawugui]

select convert(varchar(7),starttime,120) , com_name , max(sum(cprice)) cprice from
tb
group by convert(varchar(7),starttime,120) , com_name

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb(ID int,com_name varchar(10),cprice int,startTime datetime)
insert into tb(ID,com_name,cprice,startTime) values(1, '公司甲', 2000, '2007-1-1')
insert into tb(ID,com_name,cprice,startTime) values(2, '公司乙', 1000, '2007-1-1')
insert into tb(ID,com_name,cprice,startTime) values(3, '公司丙', 3000, '2007-1-1')
insert into tb(ID,com_name,cprice,startTime) values(4, '公司丁', 1000, '2007-2-1')
insert into tb(ID,com_name,cprice,startTime) values(5, '公司甲', 2800, '2007-2-1')
insert into tb(ID,com_name,cprice,startTime) values(6, '公司丙', 3000, '2007-2-1')
insert into tb(ID,com_name,cprice,startTime) values(7, '公司乙', 1500, '2007-3-1')
insert into tb(ID,com_name,cprice,startTime) values(8, '公司丙', 3000, '2007-3-1')
go

select a.* from tb a,
(
select convert(varchar(7),starttime,120) as 年月, max(cprice) cprice from tb
group by convert(varchar(7),starttime,120)
) b
where convert(varchar(7),a.starttime,120) = b.年月 and a.cprice = b.cprice

drop table tb

/*
ID com_name cprice startTime
----------- ---------- ----------- ------------------------------------------------------
3 公司丙 3000 2007-01-01 00:00:00.000
6 公司丙 3000 2007-02-01 00:00:00.000
8 公司丙 3000 2007-03-01 00:00:00.000

（所影响的行数为 3 行）
*/

[gaoninggao]

select sum(case when com_name='公司甲'then 1 else 0 end) 公司甲 ,
sum(case when com_name='公司乙'then 1 else 0 end) 公司乙,
sum(case when com_name='公司丙'then 1 else 0 end) 公司丙,
sum(case when com_name='公司丁'then 1 else 0 end) 公司丁
from tb join (select max(cprice) maxcp,datepart(mm,starttime) as mon from tb group by datepart(mm,starttime)) tc
on tb.cprice=tc.maxcp and datepart(mm,starttime)=mon

公司甲 公司乙 公司丙 公司丁
0 0 3 0