sql求某一字段中最大值和最小值的问题，高手请进！ SQL Server 数据库 选择题 高手请进！！！
SQL\u9ad8\u624b\u9ad8\u624b\u8bf7\u8fdb\uff0cSQL\u9ad8\u624b\u6709\u95ee\u9898---*/
/**//*--\u8c03\u7528\u793a\u4f8b
p_exporttb @tbname='\u5730\u533a\u8d44\u6599',@path='c:',@fname='aa.xls'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
create proc p_exporttb
@tbname sysname, --\u8981\u5bfc\u51fa\u7684\u8868\u540d,\u6ce8\u610f\u53ea\u80fd\u662f\u8868\u540d/\u89c6\u56fe\u540d
@path nvarchar(1000), --\u6587\u4ef6\u5b58\u653e\u76ee\u5f55
@fname nvarchar(250)='' --\u6587\u4ef6\u540d,\u9ed8\u8ba4\u4e3a\u8868\u540d
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--\u53c2\u6570\u68c0\u6d4b
if isnull(@fname,'')='' set @fname=@tbname+'.xls'
--\u68c0\u67e5\u6587\u4ef6\u662f\u5426\u5df2\u7ecf\u5b58\u5728
if right(@path,1)'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--\u6570\u636e\u5e93\u521b\u5efa\u8bed\u53e5
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+';DATABASE='+@sql+'"'
--\u8fde\u63a5\u6570\u636e\u5e93
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err0 goto lberr
--\u521b\u5efa\u8868\u7684SQL
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in
('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and object_id(@tbname)=id
select @sql='create table ['+@tbname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err0 goto lberr
exec @err=sp_oadestroy @obj
--\u5bfc\u5165\u6570\u636e
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@tbname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from '+@tbname)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as \u9519\u8bef\u53f7
,@src as \u9519\u8bef\u6e90,@desc as \u9519\u8bef\u63cf\u8ff0
select @sql,@constr,@fdlist
go
*--\u6570\u636e\u5bfc\u51faEXCEL
\u5bfc\u51fa\u67e5\u8be2\u4e2d\u7684\u6570\u636e\u5230Excel,\u5305\u542b\u5b57\u6bb5\u540d,\u6587\u4ef6\u4e3a\u771f\u6b63\u7684Excel\u6587\u4ef6
,\u5982\u679c\u6587\u4ef6\u4e0d\u5b58\u5728,\u5c06\u81ea\u52a8\u521b\u5efa\u6587\u4ef6
,\u5982\u679c\u8868\u4e0d\u5b58\u5728,\u5c06\u81ea\u52a8\u521b\u5efa\u8868
\u57fa\u4e8e\u901a\u7528\u6027\u8003\u8651,\u4ec5\u652f\u6301\u5bfc\u51fa\u6807\u51c6\u6570\u636e\u7c7b\u578b
--*/
/**//*--\u8c03\u7528\u793a\u4f8b
p_exporttb @sqlstr='select * from \u5730\u533a\u8d44\u6599'
,@path='c:',@fname='aa.xls',@sheetname='\u5730\u533a\u8d44\u6599'
--*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
create proc p_exporttb
@sqlstr sysname, --\u67e5\u8be2\u8bed\u53e5,\u5982\u679c\u67e5\u8be2\u8bed\u53e5\u4e2d\u4f7f\u7528\u4e86order by ,\u8bf7\u52a0\u4e0atop 100 percent,\u6ce8\u610f,\u5982\u679c\u5bfc
\u51fa\u8868/\u89c6\u56fe,\u7528\u4e0a\u9762\u7684\u5b58\u50a8\u8fc7\u7a0b
@path nvarchar(1000), --\u6587\u4ef6\u5b58\u653e\u76ee\u5f55
@fname nvarchar(250), --\u6587\u4ef6\u540d
@sheetname varchar(250)='' --\u8981\u521b\u5efa\u7684\u5de5\u4f5c\u8868\u540d,\u9ed8\u8ba4\u4e3a\u6587\u4ef6\u540d
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--\u53c2\u6570\u68c0\u6d4b
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')
--\u68c0\u67e5\u6587\u4ef6\u662f\u5426\u5df2\u7ecf\u5b58\u5728
if right(@path,1)'' set @path=@path+''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--\u6570\u636e\u5e93\u521b\u5efa\u8bed\u53e5
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="'+@sql+'";DBQ='+@sql
else
set @constr='Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 5.0;HDR=YES'
+';DATABASE='+@sql+'"'
--\u8fde\u63a5\u6570\u636e\u5e93
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err0 goto lberr
exec @err=sp_oamethod @obj,'open',null,@constr
if @err0 goto lberr
--\u521b\u5efa\u8868\u7684SQL
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
select @sql='',@fdlist=''
select @fdlist=@fdlist+','+a.name
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in
('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,'execute',@out out,@sql
if @err0 goto lberr
exec @err=sp_oadestroy @obj
--\u5bfc\u5165\u6570\u636e
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']')
set @sql='drop table ['+@tbname+']'
exec(@sql)
return
lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as \u9519\u8bef\u53f7
,@src as \u9519\u8bef\u6e90,@desc as \u9519\u8bef\u63cf\u8ff0
select @sql,@constr,@fdlist
go
\u4f7f\u7528SSIS(SQL SERVER 2005)\u6216\u8005DTS(2000)\u8c03\u7528\uff0c\u5e76\u8bbe\u7f6e\u597d\u8fd0\u884c\u65f6\u95f4
1.D
2.A
3.A
4.C
5.B
6.A
7.B
8.C
9.C
10.B
11.C
12.D
13.A
14.A
\u8865\u5145\uff1a\u5f88\u591a\u4eba\u89c9\u5f97\u7b2c\u4e8c\u9898\u6ca1\u6709\u7b54\u6848\uff0c\u5176\u5b9eA\u7684\u5199\u6cd5\u662f\u6b63\u786e\u7684
sql查询字段的最大值使用max()函数。
例：select
max(a)
from
table
语句大意：检索表table中a字段中的最大值。
扩展资料：
1、SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式又叫做视图(View)，全局模式简称模式( Schema)，内模式由系统根据数据库模式自动实现，一般无需用户过问。
2、SQL数据操纵功能：包括对基本表和视图的数据插入、删除和修改，特别是具有很强的数据查询功能。
3、SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。
参考资料来源：百度百科-结构化查询语言
select * from(select id,max(cast(riqi+' '+shijian as datetime)) as newt from kq group by id,riqi
union all
select id,min(cast(riqi+' '+shijian as datetime))as newt from kq group by id,riqi)a
order by a.id ,a.newt
--插入测试数据:
create table kq
(
id varchar(3),
riqi varchar(10),
shijian varchar(8)
)
go
insert into kq
select '001' ,'2011-4-1','07:55:00'union all
select '001' ,'2011-4-1','12:02:00'union all
select '001' ,'2011-4-1','12:26:00'union all
select '001' ,'2011-4-1','18:56:00'union all
select '001' ,'2011-4-2','07:55:00'union all
select '001' ,'2011-4-2','12:02:00'union all
select '001' ,'2011-4-2','12:26:00'union all
select '001' ,'2011-4-2','18:56:00'union all
select '002' ,'2011-4-1','07:55:00'union all
select '002' ,'2011-4-1','12:02:00'union all
select '002' ,'2011-4-1','12:26:00'union all
select '002' ,'2011-4-1','18:56:00'union all
select '002' ,'2011-4-2','07:55:00'union all
select '002' ,'2011-4-2','12:02:00'union all
select '002' ,'2011-4-2','12:26:00'union all
select '002' ,'2011-4-2','18:56:00'
go
执行测试结果:
id newt
---- ------------------------------------------------------
001 2011-04-01 07:55:00.000
001 2011-04-01 18:56:00.000
001 2011-04-02 07:55:00.000
001 2011-04-02 18:56:00.000
002 2011-04-01 07:55:00.000
002 2011-04-01 18:56:00.000
002 2011-04-02 07:55:00.000
002 2011-04-02 18:56:00.000
（所影响的行数为 8 行）
用where设置两个区间就行了，我写日期格式可能不对，你自己调一下
select id,riqi,shijian from kq where kq.shijian>date(18:00:00) or kq.shijian<date(09:30:00)
select id,riqi,min(shijian),max(shijian) from kq k group by id ,riqi
你如果不要中午的,可以再加个查询条件
select id,riqi,min(shijian),max(shijian) from kq where riqi>'....' or riqi<'....' k group by id
如果你的表中存在唯一编号最好了。如果没有，则你可以为该表增加一个字段叫aid 要求自增，且为主键。
那么下面就可以开始选择id 相同的第一条记录了。
比如表名叫tmpTable，则有：
第一种解决方式：
你可以选择用分组函数，也就是group by，那么id之外的所有列，你就要对他们使用聚合函数，比如max最大值，min最小值，count计算行数等，语句的例子：
select id,max(任意列),min(任意列),count(任意列)
from table
注：这里可以增加个where条件，用来筛选你的数据
group by id
第二种：
就是根据某一列来排序，用row_number()来筛选数据，例如
select aa.*
from
(select a.*,
row_number() over(partition by a.id order by 你需要排序的列) n1
from table a )aa
where aa.n1=1
绛旓細max(a)from table 璇彞澶ф剰锛氭绱㈣〃table涓璦瀛楁涓鐨鏈澶у銆
绛旓細3銆佹煡璇㈡暟鎹〃锛屽彂鐜鏈澶鐨剆core鍊间负100锛泂elect t.*, t.rowid from TEST_MAX2 t 4銆佹煡璇core鍊间负鏈澶э紙100锛夌殑璁板綍锛泂elect * from (select t.*, row_number() over(order by score desc) rn from TEST_MAX2 t) where rn = 1锛...
绛旓細select max(Age) from Student鐢╩ax鍑芥暟鍙互璁＄畻鍑虹浉搴斿瓧娈电殑鏈澶у 鎵╁睍:SQL 鎷ユ湁寰堝鍙敤浜庤鏁板拰璁＄畻鐨勫唴寤哄嚱鏁般傚嚱鏁扮殑璇硶 鍐呭缓 SQL 鍑芥暟鐨勮娉曟槸锛歋ELECT function(鍒) FROM 琛 鍑芥暟鐨勭被鍨 鍦 SQL 涓紝鍩烘湰鐨勫嚱鏁扮被鍨嬪拰绉嶇被鏈夎嫢骞茬銆傚嚱鏁扮殑鍩烘湰绫诲瀷鏄細Aggregate 鍑芥暟 Scalar 鍑芥暟 鍙傝冭祫鏂:鐧...
绛旓細涓銆丮AX 鍑芥暟鍔熻兘锛氳繑鍥炰竴鍒椾腑鐨勬渶澶у笺侼ULL 鍊间笉鍖呮嫭鍦ㄨ绠椾腑銆備簩銆丼QL MAX() 璇硶锛歋ELECT MAX(column_name) FROM table_name 娉ㄩ噴锛歁IN 鍜 MAX 涔熷彲鐢ㄤ簬鏂囨湰鍒楋紝浠ヨ幏寰楁寜瀛楁瘝椤哄簭鎺掑垪鐨勬渶楂樻垨鏈浣庡笺備笁銆佹绱id瀛楁鐨勬渶澶у艰鍙ワ細select max(aid) as '鏈澶у' from aaa;缁撴灉濡傚浘锛...
绛旓細鍦ㄤ綘鐨勬煇鏉′欢涓嬶紝涓鏈澶鐨勪竴鏉¤褰曪細SELECT MAX(瀛楁) FORM 琛ㄦ牸1 WHERE 浣犵殑鏉′欢 --鎴栫敤涓嬮潰鏌ョ湅杩欐潯璁板綍閲屾墍鏈夌殑瀛楁鐨勬儏鍐 SELECT * FORM 琛ㄦ牸1 WHERE 瀛楁 IN (SELECT MAX(瀛楁) FORM 琛ㄦ牸1 WHERE 浣犵殑鏉′欢)鑷充簬 鈥滃強鏄庣粏鈥濓紝灏变笉鐭ラ亾 浣犳槸浠涔 鎯虫硶浜嗭紝濡傛灉鏄墍鏈夎褰曪紝閭ｅ氨鐩存帴鍒楀嚭...
绛旓細select max(t) as 娓╁害鏈澶у,min(t) as 娓╁害鏈灏鍊,max(p) as 婀垮害鏈澶у,min(p) as 婀垮害鏈灏忓,max(datetime) as 鏈鍚庡嚭鐜版椂闂,min(datetime) as 鏈鏃╁嚭鐜版椂闂 from yourtable_name
绛旓細鏈澶у锛氶鍏堟眰鍑烘墍鏈夌殑闈炴渶澶у硷紝鐒跺悗鍒╃敤闆嗗悎鐨勫樊杩愮畻鍗冲彲銆傝绠楁墍鏈夌殑闈炴渶澶у硷紝瑕佸埄鐢ㄨ〃鑷韩鐨勭瑳鍗″皵绉.鍒楀锛歴elect scores from MY_MAX where scores not in (select A.scores from MY_MAX A,MY_MAX B where A.scores<B.scores)楹荤儲寰楀緢銆傘備负鍟ヤ笉鐢∕AX 绛夊嚱鏁帮紒锛佸緢绠鍗曞槢锛
绛旓細avg鍑芥暟锛氳绠楁煡璇腑鏌愪竴鐗瑰畾瀛楁璧勬枡鐨勭畻鏈钩鍧囧笺俢ount鍑芥暟锛氳绠楃鍚堟煡璇㈡潯浠剁殑璁板綍鏁般俶in, max鍑芥暟锛氫紶鍥炴寚瀹瀛楁鍊间腑绗﹀悎鏌ヨ鏉′欢鐨勭涓鏉°佹渶鏈潯璁板綍鐨勮祫鏂欍俧irst, last鍑芥暟锛氫紶鍥炴寚瀹氬瓧娈靛间腑绗﹀悎鏌ヨ鏉′欢鐨鏈灏鍊笺鏈澶у銆俿tdev鍑芥暟锛氳绠楁寚瀹氬瓧娈靛间腑绗﹀悎鏌ヨ鏉′欢鐨勬爣鍑嗗樊銆俿um鍑芥暟锛氳绠...
绛旓細select max(姘旀俯)锛宮in(姘旀俯) from 姘旀俯琛紱鍝堝搱
绛旓細oracle涓夊嚭鏌愪釜瀛楁閲岄潰鏈澶у鐨勮褰曠殑sql璇彞鎬庝箞鍐 瀹炵幇渚嬪彞濡備笅锛歋ELECT a.FROM table1 a WHERE NOT EXISTS (SELECT 1 FROM table1 b WHERE b.id>a.id)
