本人用delphi6自带的数据库工具做了一个表,字段分别为:
班级 总分
现在我要把表里的所有班级相同的,分组求出平均总分,于是用了下列语句:
select 班级,avg(总分) from mm.db group by 班级
可是我还想把当天的日期,和按照平均总分排一下后的名次,也加上去,请问该怎么办?
先谢谢了
班级 总分
现在我要把表里的所有班级相同的,分组求出平均总分,于是用了下列语句:
select 班级,avg(总分) from mm.db group by 班级
可是我还想把当天的日期,和按照平均总分排一下后的名次,也加上去,请问该怎么办?
先谢谢了
group by 班级
order by 总分合计;SysDate是oracle的日期函数,其他数据库系统的相应函数名称可能不同。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Test]
GOCREATE TABLE [dbo].[Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Class] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[SumNumeric] [numeric](18, 0) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[Test] WITH NOCHECK ADD
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
---------------功能实现-----------------------------
if exists(select * from tempdb..sysobjects where name = '##temp')
drop table ##temp
go
select a.* into ##temp from
(select Class,Sum(SumNumeric) as SumNumeric,Getdate() as NowTime from test
group by class) as a
select * from ##temp order by SumNumeric asc
drop table ##temp
go
select a.* into ##temp from
(select Class,Sum(SumNumeric) as SumNumeric,Getdate() as NowTime from test
group by class) as a
select * from ##temp order by SumNumeric asc
select 名次=(select count(*)+1 from ##temp where SumNumeric>a.SumNumeric),Class,SumNumeric,
Getdate() as NowTime from ##temp as a order by 名次
(select Class,Sum(SumNumeric) as SumNumeric,Getdate() as NowTime from test
group by class) as a
弹出这样一个错误Invalid use of keyword.
Token:select
Line Number:1
ID Class SumNumeric
1 A 2000
2 A 1000
3 B 1200
4 B 1500
5 C 1000
6 C 2000
7 D 1500
2、在查询分析器中执行以下SQL语句,不会出错的,
if exists(select * from tempdb..sysobjects where name = '##temp')
drop table ##temp
go
select a.* into ##temp from
(select Class,Sum(SumNumeric) as SumNumeric,Getdate() as NowTime from test
group by class) as a
select 名次=(select count(*)+1 from ##temp where SumNumeric>a.SumNumeric),Class,SumNumeric,
Getdate() as NowTime from ##temp as a order by 名次3、方法是很多的,关键是效率和功能
1 C 3000 2006-09-26 16:09:44.297
3 B 2700 2006-09-26 16:09:44.297
4 D 1500 2006-09-26 16:09:44.297
select a.* from (select * from test) as a
是根本没法执行的