id group class score1 scroe2 date
1 1 1 80 84 a
2 1 1 70 83 b
3 2 2 85 82 c
4 2 2 75 81 d
5 3 3 90 83 a
6 3 3 95 80 b按group和class分组,求每组中score1的和,求每组中score2最大值结果:
id group class score1 score2 data
1 1 1 150 84 a
3 2 2 160 82 c
5 3 3 185 83 a
1 1 1 80 84 a
2 1 1 70 83 b
3 2 2 85 82 c
4 2 2 75 81 d
5 3 3 90 83 a
6 3 3 95 80 b按group和class分组,求每组中score1的和,求每组中score2最大值结果:
id group class score1 score2 data
1 1 1 150 84 a
3 2 2 160 82 c
5 3 3 185 83 a
from tb
group by group,class
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,[group] int,class int,score1 int,score2 int,date varchar(1))
insert into #tb
select 1,1,1,80,84,'a' union all
select 2,1,1,70,83,'b' union all
select 3,2,2,85,82,'c' union all
select 4,2,2,75,81,'d' union all
select 5,3,3,90,83,'a' union all
select 6,3,3,95,80,'b'select [group],class,score1=sum(score1),score2=max(score2)
from #tb
group by [group],class
group class score1 score2
----------- ----------- ----------- -----------
1 1 150 84
2 2 160 82
3 3 185 83(3 row(s) affected)
from tb
group by group,class
from #tb
group by [group],class
CREATE TABLE #tab(id INT,[GROUP] INT,class INT,score1 INT,score2 INT,date VARCHAR(1))
INSERT #tab
SELECT 1, 1, 1,80, 84, 'a' UNION ALL
SELECT 2, 1, 1, 70, 83, 'b' UNION ALL
SELECT 3, 2, 2, 85, 82, 'c'UNION ALL
SELECT 4, 2, 2, 75, 81, 'd'UNION ALL
SELECT 5, 3, 3, 90, 83, 'a'UNION ALL
SELECT 6, 3, 3, 95, 80, 'b'SELECT id=MIN(id),[GROUP],class,score1=SUM(score1),score2=MAX(score2),date=MIN(date) FROM #tab
GROUP BY [GROUP],class
(select a.date
from tb a
group by a.group,a.class,a.date
where a.group=tb.group and a.classs=tb.class) as date
from tb
group by group,class
USE City;
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
id int identity(1,1),
[group] int,
class int,
score1 int,
scroe2 int,
[date] nvarchar(10)
)
INSERT INTO A--插入测试数据
select 1, 1, 80, 84, 'a' union all
select 1, 1, 70, 83, 'b' union all
select 2, 2, 85, 82, 'c' union all
select 2, 2, 75, 81, 'd' union all
select 3, 3, 90, 83, 'a' union all
select 3, 3, 95, 80, 'b'
go
select [group],class,SUM(score1) as 'score1',MAX(scroe2) as 'scroe2',min([date]) as 'date' from A
group by [group],class
/*
group class score1 scroe2 date
----------- ----------- ----------- ----------- ----------
1 1 150 84 a
2 2 160 82 c
3 3 185 83 a
*/
(select a.date
from tb a
where a.id=tb.id
group by a.group,a.class,a.date
) as date
from tb
group by group,class
(select a.date
from tb a
where a.id=tb.id ) as date
from tb
group by group,class
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
id int identity(1,1),
[group] int,
class int,
score1 int,
scroe2 int,
[date] nvarchar(10)
)
INSERT INTO A--插入测试数据
select 1, 1, 80, 84, 'a' union all
select 1, 1, 70, 83, 'b' union all
select 2, 2, 85, 82, 'c' union all
select 2, 2, 75, 81, 'd' union all
select 3, 3, 90, 83, 'a' union all
select 3, 3, 95, 88, 'b'
go
with cte as(select [group],class,SUM(score1) as score1,MAX(scroe2) as score2 from A a1 group by [group],class)
select *,
(select a2.[date] from A a2 where cte.[group]=a2.[group] and cte.class=a2.class and cte.score2=a2.scroe2) as [date]
from cte
/*
group class score1 score2 date
----------- ----------- ----------- ----------- ----------
1 1 150 84 a
2 2 160 82 c
3 3 185 83 a
*/
(
id int identity(1,1),
[group] int,
class int,
score1 int,
scroe2 int,
[date] nvarchar(10)
)
INSERT INTO A--插入测试数据
select 1, 1, 80, 84, 'a' union all
select 1, 1, 70, 83, 'b' union all
select 2, 2, 85, 82, 'c' union all
select 2, 2, 75, 81, 'd' union all
select 3, 3, 90, 83, 'a' union all
select 3, 3, 95, 80, 'b'--SELECT * FROM aSELECT A.id, B.*, A.date FROM A
INNER JOIN
(
SELECT [group], class, score1 = SUM(score1), scroe2 = MAX(scroe2)
FROM A
GROUP BY [group], class
) B
ON A.[group] = B.[group] AND A.class = B.class AND A.scroe2 = B.scroe2
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GOCREATE TABLE A--创建测试数据表
(
id int identity(1,1),
[group] int,
class int,
score1 int,
scroe2 int,
[date] nvarchar(10)
)
INSERT INTO A--插入测试数据
select 1, 1, 80, 84, 'a' union all
select 1, 1, 70, 83, 'b' union all
select 2, 2, 85, 82, 'c' union all
select 2, 2, 75, 81, 'd' union all
select 3, 3, 90, 83, 'a' union all
select 3, 3, 222, 88, 'd' union all
select 3, 3, 95, 88, 'b'
go
with cte as(select [group],class,SUM(score1) as score1,MAX(scroe2) as score2 from A a1 group by [group],class)
select
(select top 1 a2.id from A a2 where cte.[group]=a2.[group] and cte.class=a2.class and cte.score2=a2.scroe2 order by a2.[date]) as id
,*,
(select top 1 a2.[date] from A a2 where cte.[group]=a2.[group] and cte.class=a2.class and cte.score2=a2.scroe2 order by a2.[date]) as [date]
from cte
/*
id group class score1 score2 date
----------- ----------- ----------- ----------- ----------- ----------
1 1 1 150 84 a
3 2 2 160 82 c
7 3 3 407 88 b
*/
go
create table #tb (id int,[group] int,class int,score1 int,score2 int,date varchar(1))
insert into #tb
select 1,1,1,80,84,'a' union all
select 2,1,1,70,83,'b' union all
select 3,2,2,85,82,'c' union all
select 4,2,2,75,81,'d' union all
select 5,3,3,90,83,'a' union all
select 6,3,3,95,80,'b'select [group],class,
score1=sum(score1),
score2=max(score2),
[date]=(select top 1 [date] from #tb where [group]=t.[group] and class=t.class order by score1 desc,id)
from #tb t
group by [group],class
group class score1 score2 date
----------- ----------- ----------- ----------- ----
1 1 150 84 a
2 2 160 82 c
3 3 185 83 b(3 row(s) affected)