送高分啊！，请问GROUP BY用法？
能举个事例吗？分数没问题，谢谢! 问题点数：100、回复次数：8Top
1 楼happydreamer（www.sz.js.cn,www.gyxk.com）回复于 2002-12-21 09:34:32 得分 0
select '批发',wm_client,substring(ws_product,1,4),td_class.cl_name,sum(fact_amt) 销售数量,sum(ws_price*fact_amt/(1+a.saletax/100)) 不含税销售额,sum(fact_amt*ws_avecost) 销售成本 ,td_client_m.cl_name
from td_wholesale_m,td_wholesale_s a ,td_product,td_class,td_client_m
where convert(char(6),firmdate,112)=200210 and wm_sheetno=ws_sheetno and ws_product=productno
and wm_client=m_clientno
and cl_class in('1101','1102','1103')
and substring(ws_product,1,4)=convert(char(4),cl_class)
group by wm_client, substring ---used group by(ws_product,1,4),cl_class,td_class.cl_name,td_client_m.cl_name
union
select '批发',wm_client,'8888','其他', sum(fact_amt) 销售数量,sum(ws_price*fact_amt/(1+a.saletax/100)) 不含税销售额,sum(fact_amt*ws_avecost) 销售成本 ,td_client_m.cl_name
from td_wholesale_m,td_wholesale_s a ,td_product,td_class,td_client_m
where convert(char(6),firmdate,112)=200210 and wm_sheetno=ws_sheetno and ws_product=productno
and cl_class not in('1101','1102','1103')
and wm_client=m_clientno
and substring(ws_product,1,4)=convert(char(4),cl_class)
group by wm_client,td_client_m.cl_nameTop
2 楼zhwyt（妙语清心）回复于 2002-12-21 09:39:49 得分 0
好象是
"select * from _tabname group by _fieldname",比如作者信息表
"select name,age from author group by country" 返回每个国家的第一个
作者(无逻辑顺序)
"select count(name) from auther group by country"可能是比较有用一点的用法.Top
3 楼tonton98（射手）回复于 2002-12-21 09:47:03 得分 0
我觉得group by在你需要知道某一类事物的信息时比较有用，还有就是在用聚合函数时，需要根据你group by里的字段进行聚合
比较麻烦的是在用聚合函数时，不能漏了一个字段Top
4 楼tonton98（射手）回复于 2002-12-21 09:49:21 得分 0
例子给个自己做的存储过程
CREATE PROCEDURE b_p_zkfx
@mstim datetime,@metim datetime AS
select z.dndh,z.jzsj,z.bz,z.zddx,z.sgdh,z.jzyg,f.* ,jzfs=(case when f.xflb=4 then f.cmc else '' end) into #xfd8 from t_p_fxfd f,t_p_zxfd z
where z.dndh=f.dh and z.zt='xx'and z.scbz=0 and f.scbz=0 and z.jzsj between @mstim and @metim
select dndh,zddx,sgdh,jzyg,jzsj,bz,max(jzfs) as jzfs,case when xflb=4 then sum(je) else 0 end as xfze,
case when xflb=2 then sum(je) else 0 end as zkje into #xfd9 from #xfd8 group by dndh,sgdh,jzyg,jzsj,bz,xflb,zddx
select dndh,zddx,sgdh,jzsj,jzyg,bz,sum(xfze) as xfze,sum(zkje) as zkje,
max(jzfs) as jzfs from #xfd9 group by dndh,sgdh,jzsj,jzyg,bz,zddx order by jzyg,jzfs
Top
5 楼ttyp（@http://www.cnblogs.com/ttyp/）回复于 2002-12-21 10:10:46 得分 0
看一句两句SQL语句没什么用的，还是看看连机帮助,讲得也比较详细Top
6 楼gks_cn（981530）回复于 2002-12-21 10:24:19 得分 50
USE pubs
SELECT type, pub_id, 'avg' = AVG(price), 'sum' = sum(ytd_sales)
FROM titles
GROUP BY type, pub_id
下面是结果集：
type pub_id avg sum
------------ ------ ---------------------- -----------
business 0736 2.99 18722
psychology 0736 11.48 9564
mod_cook 0877 11.49 24278
psychology 0877 21.59 375
trad_cook 0877 15.96 19566
UNDECIDED 0877 NULL NULL
business 1389 17.31 12066
popular_comp 1389 21.48 12875
Top
7 楼orcale（）回复于 2002-12-21 11:02:22 得分 0
sql server的联机帮助上有Top
8 楼CoolAbu（阿卜-Never Stop（★★★★））回复于 2002-12-21 12:47:49 得分 50
例如表 A
Name Grade
小明 2
小王 1
小利 3
旺才 2
小强 3
你如果想按年级统计人数就可以：
SELECT Grade, COUNT(*) AS 人数 FROM A GROUP BY Grade
Grade 人数
----------- -----------
1 1
2 2
3 2
Top
