求一个查询结果中根据某一列分组的count(*)

[fling_boy]

现在有一个查询sql,数量级千万,sql中有多个表关联及字查询
得到的结果是:
id,groupkey,info1,info2,info3...
1 a ads dsf ds
2 a sdsf dsf dsf
3 b dsf dsf dsf
4 b dsfds dsf dsf
5 b dsf dsf df
6 c dsf dsf sdf
7 d dsf dsf dsf
现在想如果修改sql,在得到的结果中就成
id,groupkey,keycount,info1,info2,info3
1 a 2 ads dsf ds
2 a 2 sdsf dsf dsf
3 b 3 dsf dsf dsf
4 b 3 dsfds dsf dsf
5 b 3 dsf dsf df
6 c 1 dsf dsf sdf
7 d 1 dsf dsf dsf

[百年树人]

用这个结果与 select groupkey,count(1) as keycount from xxx group by groupkey 关联

[ws_hgo]

create table #YY
(
  id int identity(1,1) primary key,
  groupkey varchar(20),
  info1 varchar(20),
  info2 varchar(20),
  info3 varchar(20)
)
insert into #YY select 'a','ads','dsf','ds'
insert into #YY select 'a','sdsf','dsf','dsf'
insert into #YY select 'b','dsf','dsf','dsf'
insert into #YY select 'b','dsfds','dsf','dsf'
insert into #YY select 'b','dsf','dsf','dsf'
insert into #YY select 'c','dsf','dsf','dsf'
insert into #YY select 'sd','dsf','dsf','dsf'

select Y1.id,Y.keycount,Y1.info1,Y1.info2,Y1.info3
from #YY Y1
join
(
 select groupkey,count(groupkey) keycount from #YY group by groupkey
) Y
on Y.groupkey=Y1.groupkey
id          keycount    info1                info2                info3
----------- ----------- -------------------- -------------------- --------------------
1           2           ads                  dsf                  ds
2           2           sdsf                 dsf                  dsf
3           3           dsf                  dsf                  dsf
4           3           dsfds                dsf                  dsf
5           3           dsf                  dsf                  dsf
6           1           dsf                  dsf                  dsf
7           1           dsf                  dsf                  dsf

(7 行受影响)

[ws_hgo]

掉了一列

select Y1.id,Y.groupkey,Y.keycount,Y1.info1,Y1.info2,Y1.info3
from #YY Y1
join
(
 select groupkey,count(groupkey) keycount from #YY group by groupkey
) Y
on Y.groupkey=Y1.groupkey

id          groupkey             keycount    info1                info2                info3
----------- -------------------- ----------- -------------------- -------------------- --------------------
1           a                    2           ads                  dsf                  ds
2           a                    2           sdsf                 dsf                  dsf
3           b                    3           dsf                  dsf                  dsf
4           b                    3           dsfds                dsf                  dsf
5           b                    3           dsf                  dsf                  dsf
6           c                    1           dsf                  dsf                  dsf
7           sd                   1           dsf                  dsf                  dsf

(7 行受影响)

[csuxp2008]

select a.id,a.groupkey,b.num as keycount,info1,info2,info3
from table_name a,
		(
		select groupkey,count(*) as num
		from table_name
		group by groupkey
		) b
where a.groupkey=b.groupkey

[fling_boy]

上面的方法以前想过来了,因为数据量比较大,查询时还会加入条件,如info1 like 'x%' and info2 like 'y%'等
而且这个查询sql会被很多客户端频繁的调用,所以要保证查询效率.看有没有更好的办法.

[ws_hgo]

引用 5 楼 fling_boy 的回复:

上面的方法以前想过来了,因为数据量比较大,查询时还会加入条件,如info1 like 'x%' and info2 like 'y%'等
而且这个查询sql会被很多客户端频繁的调用,所以要保证查询效率.看有没有更好的办法.

就用我3楼的

[fling_boy]

/************************************
订单和库存自动匹配过程
作者:李志光
创建:2010-08-18
修改:2010-08-18
订单 主从表
     主表 ID state
     从表 orderid ,productid,number

库存表  productid ,numbercount

对一批订单进行库存自动匹配,找出可以出库的订单
出库规则
1.按订单先后顺序出库
2.一个订单中有一件商品不足,则整个订单作废

设计思路
1.按订单顺序,把前面订单中相同货品的数量累加到后续订单上.
2.判断第一个不能出库的订单,设置为作废
3.循环上述过程
     
************************************/

--创建订单表,订单明细表,库存表
create table t_ordermain (f_id int,f_number varchar(10),f_state int)
create table t_orderDetail (f_id int ,f_orderID int,f_productID int,f_number int,f_numberall int)
create table t_Stock (f_productID int ,f_numbercount int)

--生成数据
insert into t_stock values (1,1000)
insert into t_stock values (2,500)
insert into t_stock values (3,2000)
insert into t_stock values (4,3000)
insert into t_stock values (5,1000)

insert into t_ordermain values (1,'001',0)
insert into t_ordermain values (2,'002',0)
insert into t_ordermain values (3,'003',0)
insert into t_ordermain values (4,'004',0)
insert into t_ordermain values (5,'005',0)

insert into t_orderDetail values (1,1,1,200,0)
insert into t_orderDetail values (2,1,2,300,0)
insert into t_orderDetail values (3,1,3,500,0)

insert into t_orderDetail values (4,2,2,100,0)
insert into t_orderDetail values (5,2,3,600,0)
insert into t_orderDetail values (6,2,4,2000,0)

insert into t_orderDetail values (7,3,1,200,0)
insert into t_orderDetail values (8,3,2,200,0)
insert into t_orderDetail values (9,3,3,1000,0)
insert into t_orderDetail values (10,3,4,200,0)


insert into t_orderDetail values (11,4,1,300,0)
insert into t_orderDetail values (12,4,3,200,0)
insert into t_orderDetail values (13,4,5,200,0)

insert into t_orderDetail values (13,5,4,200,0)

--删除表
drop table t_ordermain
drop table t_orderDetail
drop table t_Stock

--自动匹配过程
create proc procOrderAutoMatch
as
begin
		--创建临时表
		create table #temp (f_orderid int)

	while(1=1)
	begin
			--找出第一个不能出库的订单
            --保存订单号到临时表
			insert into #temp 
			select top 1 f_orderid
			from t_Stock s inner join (
			select  o1.f_orderid,o1.f_productid,sum(o2.f_number) as sumNumber
			from  t_orderDetail as o1 inner join t_orderDetail as o2 on o1.f_productid=o2.f_productid and o1.f_orderid>=o2.f_orderid
			where o1.f_orderid in (select f_id from t_ordermain where f_state=0) and o2.f_orderid in (select f_id from t_ordermain where f_state=0)
			group by o1.f_orderid,o1.f_productid
			) as t  on s.f_productid=t.f_productid and s.f_numbercount<sumnumber
			
			--如果存在数据,则设置对应单据为作废
			if exists(select * from #temp)
				begin
					update t_ordermain
					set f_state=1
					where f_id in (select f_orderid from #temp)
					delete from #temp
				end
			else
				--如果都可以出库,则退出循环
				break;
	end
	--删除临时表
	drop table #temp
	--如果可以出库的订单
	select * from t_ordermain as om inner join t_orderdetail as od on om.f_id=od.f_orderid
	select * from t_stock
	select * from t_ordermain as om inner join t_orderdetail as od on om.f_id=od.f_orderid
	where f_state=0
	
end