declare @t table(subject nvarchar(10),score int) insert @t select N'语文' , 60 insert @t select N'数学' , 70 insert @t select N'数学' , 78 insert @t select N'语文' , 54 insert @t select N'数学' , 30 select subject,sum(score)[SUM],avg(score)[AVG] from @T WHERE score >50 group by subject ORDER BY [AVG] /*subject SUM AVG ---------- ----------- ----------- 语文 114 57 数学 148 74*/
--> By dobear_0922(小熊) 2009-02-11 16:58:57 --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([subject] varchar(4),[score] int) insert [tb] select '语文',60 union all select '数学',70 union all select '数学',78 union all select '语文',54 union all select '数学',30select subject as [科目],sum(score) as [总分], avg(score) as [平均分] from tb group by subject /* (5 行受影响) 科目 总分 平均分 ---- ----------- ----------- 数学 178 59 语文 114 57(2 行受影响) */drop table [tb]
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([subject] varchar(4),[score] int) insert [tb] select '语文',60 union all select '数学',70 union all select '数学',78 union all select '语文',54 union all select '数学',30
---查询--- select subject, sum(score) 总分, avg(score) 平均分 from [tb] group by subject---结果--- subject 总分 平均分 ------- ----------- ----------- 数学 178 59 语文 114 57(所影响的行数为 2 行)
--> 生成测试数据: @T DECLARE @T TABLE (subject VARCHAR(4),score INT) INSERT INTO @T SELECT '语文',60 UNION ALL SELECT '数学',70 UNION ALL SELECT '数学',78 UNION ALL SELECT '语文',54 UNION ALL SELECT '数学',30--SQL查询如下:SELECT subject, SUM(score) AS 总分, AVG(score) AS 平均分 FROM( SELECT * FROM @T WHERE score>50 ) AS A GROUP BY subject/* subject 总分 平均分 ------- ----------- ----------- 数学 148 74 语文 114 57(2 行受影响) */
select tid=identity(int,1,1),* into # from tbselect * from # where tid between 5 and 10 drop table #
select subject as [科目],sum(score) as [总分], avg(score) as [平均分] from tb group by subject having(avg(score)>50) order by [平均分] desc/* (5 行受影响) 科目 总分 平均分 ---- ----------- ----------- 数学 178 59 语文 114 57(2 行受影响) */
CREATE TABLE #Tb (id int identity(1,1),subject varchar(10),score int) insert into #Tb select '语文','60' union all select '数学','70' union all select '数学','78' union all select '语文','54' union all select '数学','30' --select Select * from #TbGO select subject,sum(score)as sumScore,avg(score) as avgScore from #Tb Group by subject --dele table Drop table #Tb
select subject as [科目],sum(score) as [总分], avg(score) as [平均分] from tb group by subject
select subject,sum(score),avg(score) from [table] group by subject having avg(score)>50
if object_id('[tb]') is not null drop table [tb] go create table [tb]([subject] varchar(4),[score] int) insert [tb] select '语文',60 union all select '数学',70 union all select '数学',78 union all select '语文',54 union all select '数学',30select [subject],sum([score]) '总分',avg([score]) '平均分' from [tb] group by [subject]subject 总分 平均分 ------- ----------- ----------- 数学 178 59 语文 114 57
from tb group by subject
insert @t select N'语文' , 60
insert @t select N'数学' , 70
insert @t select N'数学' , 78
insert @t select N'语文' , 54
insert @t select N'数学' , 30
select subject,sum(score),avg(score) from @T WHERE score >50 group by subject
/*subject
---------- ----------- -----------
数学 148 74
语文 114 57*/
insert @t select N'语文' , 60
insert @t select N'数学' , 70
insert @t select N'数学' , 78
insert @t select N'语文' , 54
insert @t select N'数学' , 30
select subject,sum(score)[SUM],avg(score)[AVG] from @T WHERE score >50 group by subject ORDER BY [AVG]
/*subject SUM AVG
---------- ----------- -----------
语文 114 57
数学 148 74*/
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([subject] varchar(4),[score] int)
insert [tb]
select '语文',60 union all
select '数学',70 union all
select '数学',78 union all
select '语文',54 union all
select '数学',30select subject as [科目],sum(score) as [总分], avg(score) as [平均分]
from tb group by subject
/*
(5 行受影响)
科目 总分 平均分
---- ----------- -----------
数学 178 59
语文 114 57(2 行受影响)
*/drop table [tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([subject] varchar(4),[score] int)
insert [tb]
select '语文',60 union all
select '数学',70 union all
select '数学',78 union all
select '语文',54 union all
select '数学',30
---查询---
select
subject,
sum(score) 总分,
avg(score) 平均分
from [tb]
group by subject---结果---
subject 总分 平均分
------- ----------- -----------
数学 178 59
语文 114 57(所影响的行数为 2 行)
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (subject VARCHAR(4),score INT)
INSERT INTO @T
SELECT '语文',60 UNION ALL
SELECT '数学',70 UNION ALL
SELECT '数学',78 UNION ALL
SELECT '语文',54 UNION ALL
SELECT '数学',30--SQL查询如下:SELECT
subject,
SUM(score) AS 总分,
AVG(score) AS 平均分
FROM(
SELECT *
FROM @T
WHERE score>50
) AS A
GROUP BY subject/*
subject 总分 平均分
------- ----------- -----------
数学 148 74
语文 114 57(2 行受影响)
*/
select tid=identity(int,1,1),* into # from tbselect * from # where tid between 5 and 10 drop table #
from tb
group by subject
having(avg(score)>50)
order by [平均分] desc/*
(5 行受影响)
科目 总分 平均分
---- ----------- -----------
数学 178 59
语文 114 57(2 行受影响)
*/
CREATE TABLE #Tb (id int identity(1,1),subject varchar(10),score int)
insert into #Tb select '语文','60'
union all select '数学','70'
union all select '数学','78'
union all select '语文','54'
union all select '数学','30'
--select
Select * from #TbGO
select subject,sum(score)as sumScore,avg(score) as avgScore from #Tb Group by subject
--dele table
Drop table #Tb
from tb group by subject
having avg(score)>50
go
create table [tb]([subject] varchar(4),[score] int)
insert [tb]
select '语文',60 union all
select '数学',70 union all
select '数学',78 union all
select '语文',54 union all
select '数学',30select [subject],sum([score]) '总分',avg([score]) '平均分' from [tb] group by [subject]subject 总分 平均分
------- ----------- -----------
数学 178 59
语文 114 57