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
