34,587
社区成员
发帖
与我相关
我的任务
分享
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] varchar(4),[d] varchar(2))
insert [tb]
select 1,8,'张三','蛇' union all
select 2,2,'张三','大' union all
select 4,4,'李四','三' union all
select 5,11,'李四','组'
;
select t1.*, t2.d from
(select SUM(a) a, SUM(b) b, c from tb group by c) t1
left join
(select ROW_NUMBER() over(partition by c order by c asc) row_id, c, d from tb) t2
on t1.c = t2.c and t2.row_id = 1
select sum(a) as A,sum(B) as B,C,min(C1) as C1 from 表
group by C
select sum(A),sum(B),C,MIN(C)
from 表结果如下
group by C
select
sum(A) as A,
sum(B) as B,
(select top 1 D from tb where C = t.C)
C
from tb t
group by C
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] varchar(4),[d] varchar(2))
insert [tb]
select 1,8,'张三','一' union all
select 2,2,'张三','二' union all
select 4,4,'李四','三' union all
select 5,11,'李四','四'
select * from tb
--=================
select sum(T.A)as A,sum(T.B)as B, T.C,[D] =(select top 1 D from tb where c = T.c)
from tb T
group by T.c
--====================
/*
A B C D
9 15 李四 三
3 10 张三 一
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] varchar(4),[d] varchar(2))
insert [tb]
select 1,8,'张三','一' union all
select 2,2,'张三','二' union all
select 4,4,'李四','三' union all
select 5,11,'李四','四'
select * from tb
--=================
select sum(T.B)as B, T.C,[D] =(select top 1 D from tb where c = T.c)
from tb T
group by T.c
-====================
/*
B C D
15 李四 三
10 张三 一
*/
----------------------------------------------------------------
-- Author :SQL77(只为思齐老)
-- Date :2010-01-26 10:24:57
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([A] int,[B] int,[C] varchar(4),[D] varchar(2))
insert #TB
select 1,8,'张三','蛇' union all
select 2,2,'张三','大' union all
select 4,4,'李四','三' union all
select 5,11,'李四','组'
--------------开始查询--------------------------
SELECT *,IDD=IDENTITY(INT,1,1) INTO #T FROM #TB
select SUM(T.A)A,SUM(T.B)B,T.C,T1.D
FROM #T T,(SELECT * FROM #T T WHERE NOT EXISTS(SELECT 1 FROM #T WHERE C=T.C AND IDD<T.IDD))AS T1
WHERE T.C=T1.C
GROUP BY T.C,T1.D
----------------结果----------------------------
/*
A B C D
----------- ----------- ---- ----
9 15 李四 三
3 10 张三 蛇
(所影响的行数为 2 行)
*/
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2010-01-26 09:58:50
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([A] int,[B] int,[C] varchar(4),[d] varchar(2))
insert [tb]
select 1,8,'张三','一' union all
select 2,2,'张三','二' union all
select 4,4,'李四','三' union all
select 5,11,'李四','四'
--------------开始查询--------------------------
select
a.*,min(b.d) as d
from
(select c,sum(a) as a,sum(b) as b from tb group by c)a
left join
(select c,d from tb)b
on
a.c=b.c
group by
a.a,a.b,a.c
----------------结果----------------------------
/* c a b d
---- ----------- ----------- ----
李四 9 15 三
张三 3 10 二
(2 行受影响)
*/
select
a.*,b.c
from
(select c,sum(a) as a,sum(b) as b from tb group by c)a
left join
(select c,d from tb)b
on
a.c=b.c