group by 分组问题,因为有几张表,求助高手,在线等10分钟
select  
a.ID,
c.AD_Name_code,
d.AD_Game_code,
a.Register,
a.StartDate,
count(b.IP)   AS   IP,
count(b.AD_ID)   as   AD_ID,
sum(b.Rs_Win)   as   Rs_Win  

from   AD_Append   a,AD_Stat   b,AD_Name   c,AD_Game   d   where   a.ID=b.AD_ID   and   a.AD_Name=c.id   and   a.Game=d.id   and   a.AD_Name   = "   +   ad_name   +   "   and   a.StartDate= ' "   +   dt1.ToString( "yyyy-MM-dd ")   +   " '   and   a.Game   = "   +   ad_game   +   "  

group   by   a.ID,c.AD_Name_code,d.AD_Game_code,a.Register,a.StartDate,b.Rs_Win
---------------
上面是我写的语句,我觉得group   by这里错了,我理解的group   by分组是针对聚合函数的(比如每个名字对应的统计)

现在问题是select   语句的字段要显示(还不在一张表),group   by后面的分组字段不写又不行(不写报错)该怎么写啊

------解决方案--------------------
select
a.ID,
c.AD_Name_code,
d.AD_Game_code,
a.Register,
a.StartDate,
count(b.IP) AS IP,
count(b.AD_ID) as AD_ID,
sum(b.Rs_Win) as Rs_Win

from AD_Append a,AD_Stat b,AD_Name c,AD_Game d where a.ID=b.AD_ID and a.AD_Name=c.id and a.Game=d.id and a.AD_Name = " + ad_name + " and a.StartDate= ' " + dt1.ToString( "yyyy-MM-dd ") + " ' and a.Game = " + ad_game + "

group by a.ID,c.AD_Name_code,d.AD_Game_code,a.Register,a.StartDate
------解决方案--------------------

--如果你要分组后,还要显示除分组列之后的其他字段,(其他字段显示每组中第一行.)

select * from #

create table #(id int,a varchar(5),b varchar(5))
insert # select 1, 'xx ', 'yy '
union all select 1, 'cc ', 'ee '
union all select 2, 'nn ', 'mm '
union all select 2, 'tt ', 'pp '


select id,count(*) as [count],(select top 1 a from # t where t.id=#.id ),(select top 1 b from # t where t.id=#.id ) from # group by id

drop table #

--如果不需要本表的其他字段，用ID去关联其他表，可以有子查询：
select * from othertable join (select id,count(*) as [count] from # group by id) t on othertable.id=t.id