表table1,a,b,c,d,e为五个字段
a为字符型,b,c,d,e为数字型
表记录如下(省略更多行):
a b c d e
test1 100 200 500 10
test1 89 210 400 20
test2 200 89 300 10
test2 188 88 400 10
test2 121 20 200 30
希望得到的结果是:将a列分组,得到行数,b,c,d列取对应a值的任意一行值,而e列将按a分组求和。名称,个数,b, c, d, 求和
test1,2, 100,200,500,30
test2,3, 200,89,300, 50能不能用一条SQL搞定?解决立即给分,多谢!
a为字符型,b,c,d,e为数字型
表记录如下(省略更多行):
a b c d e
test1 100 200 500 10
test1 89 210 400 20
test2 200 89 300 10
test2 188 88 400 10
test2 121 20 200 30
希望得到的结果是:将a列分组,得到行数,b,c,d列取对应a值的任意一行值,而e列将按a分组求和。名称,个数,b, c, d, 求和
test1,2, 100,200,500,30
test2,3, 200,89,300, 50能不能用一条SQL搞定?解决立即给分,多谢!
from
tb
group by a,b,c,d
from
tb
group by a
select a as '名称',sum(1) '个数',max(c),max(d),SUm(e) from table1
group by a
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-07 14:36:28
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
--------------开始查询--------------------------
select a as 名称,count(a) as 个数,min(b),min(c),min(d),sum(e) as 求和
from
tb
group by a
----------------结果----------------------------
/*名称 个数 求和
----- ----------- ----------- ----------- ----------- -----------
test1 2 89 200 400 30
test2 3 121 20 200 50(所影响的行数为 2 行)
*/
drop table tb
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-07 14:38:28
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
--------------开始查询--------------------------
select a as 名称,count(a) as 个数,min(b)b,min(c)c,min(d)d,sum(e) as 求和
from
tb
group by a
----------------结果----------------------------
/*
名称 个数 b c d 求和
----- ----------- ----------- ----------- ----------- -----------
test1 2 89 200 400 30
test2 3 121 20 200 50(所影响的行数为 2 行)
*/
drop table tb
from (
select 名称=a,个数=count(1), 求和=sum(e)
from table1
group by a
) as t
join table1 b
on t.名称=b.a
and not exists (select 1 from table1 where a=t.名称 and b>b.b)
group by a
--> 测试时间:2009-07-07 14:37:09
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tab]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30select 名称=a,个数=count(a),b=max(b),c=min(c),d=max(d),求和=sum(e)
from tab group by a/*
名称 个数 b c d 求和
----- ----------- ----------- ----------- ----------- -----------
test1 2 100 200 500 30
test2 3 200 20 400 50(所影响的行数为 2 行)*/drop table tab
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-07 14:41:28
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
--------------开始查询--------------------------
select t.名称,t.个数,b.b, b.c, b.d, t.求和
from (
select 名称=a,个数=count(1), 求和=sum(e)
from tb
group by a
) as t
join tb b
on t.名称=b.a
and not exists (select 1 from tb where a=t.名称 and b>b.b)
----------------结果----------------------------
/*名称 个数 b c d 求和
----- ----------- ----------- ----------- ----------- -----------
test1 2 100 200 500 30
test2 3 200 89 300 50(所影响的行数为 2 行)
*/
drop table tb
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a char(5),b int,c int,d int,e int)
go
insert into tb
select
'test1', 100 ,200 ,500, 10 union all select
'test1' ,89 ,210 ,400, 20 union all select
'test2', 200 ,89 ,300 ,10 union all select
'test2' ,188 ,88 ,400 ,10 union all select
'test2', 121 ,20 ,200 ,30
go
select 名称,个数,b,c,d,求和
from
(
select 名称=a,个数=count(*),求和=SUM(e) from tb group by a ) t join
(select * from tb tt where not exists(select * from tb where tt.a=a and tt.b<b )) tbb on t.名称=tbb.a/*------------
test1 2 100 200 500 30
test2 3 200 89 300 50
-------*/
(a varchar(20),b int,c int,d int,e int)
insert into tbl_fs
select 'test1', 100 ,200 ,500 ,10
union all
select'test1', 89, 210, 400, 20
union all
select'test2', 200, 89 ,300 ,10
union all
select 'test2', 188, 88 ,400 ,10
union all
select 'test2', 121, 20, 200, 30
select m.a as '名称'
,x.i as '个数'
,m.b
,m.c
,m.d
,x.s as '求和'
from ( select a
,b
,c
,d
,rank() over (PARTITION by a order by a,b,c,d,e) id
from tbl_fs
) m
,(select a,count(a) i,sum(e) s
from tbl_fs
group by a) x
where m.a=x.a
and m.id=1名称 个数 b c d 求和
-------------------- ----------- ----------- ----------- ----------- -----------
test1 2 89 210 400 30
test2 3 121 20 200 50(2 row(s) affected)
from
(select *,(row_number() over(partition by a order by b))as number from table1)A
inner join
(select a,count(*)as [count] ,sum(e) as [sum] from table1 group by a)B
on A.a = B.a
where
A.number=1
insert @T
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
SELECT A.名称,A.个数,B.b,B.c,B.d,A.求和
from
(
select 名称=a,个数=count(1),求和=sum(e)
from @T
group by a
) A
join @T B on A.名称=B.a
where 1 > (select count(1) from @T where a = B.a and b > B.b ) order by B.a名称 个数 b c d 求和
----- ----------- ----------- ----------- ----------- -----------
test1 2 100 200 500 30
test2 3 200 89 300 50(2 行受影响)
--SQL2005
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
Go
select t1.[a],count(*) as cnt,t2.[b],t2.[c],t2.[d],sum([e]) as summary
from tb as t1
inner join
(
select [a],[b],[c],[d],row_number() over (partition by [a] order by getdate()) as tmp
from tb
) as t2
on t1.[a]=t2.[a] and t2.[tmp]=1
group by t1.[a],t2.[b],t2.[c],t2.[d]
/*
a cnt b c d summary
----- ----------- ----------- ----------- ----------- -----------
test1 2 100 200 500 30
test2 3 200 89 300 50*/
服务器: 消息 195,级别 15,状态 10,行 3
'row_number' 不是可以识别的 函数名。
服务器: 消息 170,级别 15,状态 1,行 5
第 5 行: 'B' 附近有语法错误。
if object_id('[tb]') is not null drop table [tb]
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30select [a],
[个数]=sum(case when [a]='test1' then 1
when [a]='test2' then 1
else 0 end),
[b]= MAX([b]),
[c]=MIN([c]),
[d]=MIN([d])
from tb group by [a]
如果是sql2005,直接用23#寫就可以了
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([a] varchar(5),[b] int,[c] int,[d] int,[e] int)
go
insert [tb]
select 'test1',100,200,500,10 union all
select 'test1',89,210,400,20 union all
select 'test2',200,89,300,10 union all
select 'test2',188,88,400,10 union all
select 'test2',121,20,200,30
goalter table tb add id int identity(1,1)
go
select a.a,cnt,a.b,a.c,a.d,se
from tb a
inner join
(
select min(id) mi,count(*) cnt,sum(e) se from tb group by a
) b
on id=mi
/*
test1 2 100 200 500 30
test2 3 200 89 300 50
*/
这种做法肯定是可以的,如果某两行所有列都相同,在主查询取数时加distinct就可以了。
但是效率很差。
所以是推荐identity的做法。
from
tb order by newid()
group by a
select a.a,b.个数,a.b,a.c,a.d.b.求和 from (select top 1 * from tb order by newid()) a,select a,count(a) as 个数,sum(e) as 求和 from tb group by a ) b