id 修改成时间就好了 declare @t table (id decimal(9,0) ,fa decimal(9,0)) insert into @t(id,fa)values(1,11) insert into @t(id,fa)values(2,22) insert into @t(id,fa)values(3,33) insert into @t(id,fa)values(4,44) insert into @t(id,fa)values(5,55) insert into @t(id,fa)values(6,33) insert into @t(id,fa)values(7,8) select * from @t --最大 where fa=(select max(fa) from @t) select * from @t --最小 where fa=(select min(fa) from @t)
不一定非用聚合函数:Select Top 1 * From yourTable Order By yourField Desc '最大 Select Top 1 * From yourTable Order By yourField '最小
select b.YourField,b.YourDate from (select Max(YourField) YourField from YourTable where YourDate between '2008-01-01' and '2008-12-31') a left join YourTable b on a.YourField=b.YourField這個怎么樣
declare @t table (id decimal(9,0)
,fa decimal(9,0))
insert into @t(id,fa)values(1,11)
insert into @t(id,fa)values(2,22)
insert into @t(id,fa)values(3,33)
insert into @t(id,fa)values(4,44)
insert into @t(id,fa)values(5,55)
insert into @t(id,fa)values(6,33)
insert into @t(id,fa)values(7,8)
select * from @t --最大
where fa=(select max(fa) from @t)
select * from @t --最小
where fa=(select min(fa) from @t)
不一定非用聚合函数:Select Top 1 * From yourTable Order By yourField Desc '最大
Select Top 1 * From yourTable Order By yourField '最小
我要打印一个电能监测终端的一个月的数据。其中包括这个月中电压电流的最值,需要MAX和MIN。每天运行时间的总和需要用SUM。每天用电量的平均值需要AVG。同时我还需要把最值发生的时间和日期也弄出来。感觉在一个SQL语句中是不可能实现。想尽可能的把这些数据弄到一张表里面去。所以来求助下下高手们。
(select Max(YourField) YourField from YourTable where YourDate between '2008-01-01' and '2008-12-31') a
left join YourTable b on a.YourField=b.YourField這個怎么樣
[proid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Sjdata] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[Sjtime] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[Sjname] [nvarchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[Sjnameinf] [int] NULL
) ON [PRIMARY]CREATE TABLE [dbo].[Xwl_tj] (
[proid] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Tjdata] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[Vamax] [float] NULL ,
[Vamaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Vbmax] [float] NULL ,
[Vbmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Vcmax] [float] NULL ,
[Vcmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Vamin] [float] NULL ,
[Vamint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Vbmin] [float] NULL ,
[Vbmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Vcmin] [float] NULL ,
[Vcmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Iamax] [float] NULL ,
[Iamaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Ibmax] [float] NULL ,
[Ibmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Icmax] [float] NULL ,
[Icmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Iamin] [float] NULL ,
[Iamint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Ibmin] [float] NULL ,
[Ibmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Icmin] [float] NULL ,
[Icmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Cosamax] [float] NULL ,
[Cosamaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Cosbmax] [float] NULL ,
[Cosbmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Coscmax] [float] NULL ,
[Coscmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Cosamin] [float] NULL ,
[Cosamint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Cosbmin] [float] NULL ,
[Cosbmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Coscmin] [float] NULL ,
[Coscmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Samax] [float] NULL ,
[Samaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Sbmax] [float] NULL ,
[Sbmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Scmax] [float] NULL ,
[Scmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Samin] [float] NULL ,
[Samint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Sbmin] [float] NULL ,
[Sbmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Scmin] [float] NULL ,
[Scmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pamax] [float] NULL ,
[Pamaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pbmax] [float] NULL ,
[Pbmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pcmax] [float] NULL ,
[Pcmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pamin] [float] NULL ,
[Pamint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pbmin] [float] NULL ,
[Pbmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Pcmin] [float] NULL ,
[Pcmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qamax] [float] NULL ,
[Qamaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qbmax] [float] NULL ,
[Qbmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qcmax] [float] NULL ,
[Qcmaxt] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qamin] [float] NULL ,
[Qamint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qbmin] [float] NULL ,
[Qbmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Qcmin] [float] NULL ,
[Qcmint] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Uamaxt] [float] NULL ,
[Ubmaxt] [float] NULL ,
[Ucmaxt] [float] NULL ,
[Uamint] [float] NULL ,
[Ubmint] [float] NULL ,
[Ucmint] [float] NULL ,
[Uaxmaxt] [float] NULL ,
[Ubxmaxt] [float] NULL ,
[Ucxmaxt] [float] NULL ,
[Ualt] [float] NULL ,
[Ublt] [float] NULL ,
[Uclt] [float] NULL ,
[F] [float] NULL ,
[G] [float] NULL ,
[Fcount] [float] NULL ,
[Gcount] [float] NULL ,
[gcount01] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Ub9] [float] NULL ,
[Cba] [float] NULL ,
[Bbt] [float] NULL ,
[Tbd] [float] NULL ,
[Acostbf] [float] NULL ,
[Bcostbf] [float] NULL ,
[Ccostbf] [float] NULL ,
[Acosc5] [float] NULL ,
[Bcosc5] [float] NULL ,
[Ccosc5] [float] NULL ,
[I0errt] [float] NULL ,
[Tc1] [float] NULL ,
[Tc2] [float] NULL ,
[Tc3] [float] NULL ,
[Tc4] [float] NULL ,
[Tc5] [float] NULL ,
[Tc6] [float] NULL ,
[Tc7] [float] NULL ,
[Tc8] [float] NULL ,
[Tc9] [float] NULL ,
[Tca] [float] NULL ,
[Tcb] [float] NULL ,
[Tcc] [float] NULL ,
[Cc1] [float] NULL ,
[Cc2] [float] NULL ,
[Cc3] [float] NULL ,
[Cc4] [float] NULL ,
[Cc5] [float] NULL ,
[Cc6] [float] NULL ,
[Cc7] [float] NULL ,
[Cc8] [float] NULL ,
[Cc9] [float] NULL ,
[Cca] [float] NULL ,
[Ccb] [float] NULL ,
[Ccc] [float] NULL ,
[Iamax15] [float] NULL ,
[Iamax15t] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Ibmax15] [float] NULL ,
[Ibmax15t] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[Icmax15] [float] NULL ,
[Icmax15t] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,
[tdtime] [float] NULL ,
[kklv] [float] NULL ,
[uta] [float] NULL ,
[utb] [float] NULL ,
[utc] [float] NULL ,
[uatlv] [float] NULL ,
[ubtlv] [float] NULL ,
[uctlv] [float] NULL ,
[ut] [float] NULL ,
[utlv] [float] NULL ,
[A89] [float] NULL ,
[B89] [float] NULL ,
[C89] [float] NULL ,
[maxfzlv] [float] NULL ,
[minfzlv] [float] NULL ,
[avgfz] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[usrlimit] (
[id] [int] NOT NULL ,
[u01_01] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]CREATE TABLE [dbo].[xwl_Data] (
[D01_01] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[D01_02] [datetime] NULL ,
[ID] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[D01_03] [datetime] NULL ,
[D01_04] [char] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[data] [image] NULL ,
[D01_05] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]CREATE TABLE [dbo].[xwl_line] (
[ID] [int] NOT NULL ,
[p01_01] [char] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_02] [int] NULL
) ON [PRIMARY]CREATE TABLE [dbo].[xwl_p02] (
[p01_id] [char] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[p01_01] [char] (12) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_02] [int] NULL ,
[p01_03] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_04] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_05] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_06] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_07] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_08] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_09] [char] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_10] [char] (200) COLLATE Chinese_PRC_CI_AS NULL ,
[p01_11] [int] NULL
) ON [PRIMARY]
SELECT CONVERT(char(8), 时间, 112) 天, SUM(运行时间), AVG(用电量)
FROM T
GROUP BY CONVERT(char(8), 时间, 112)
SQL Server可以使用存储过程来解决,Access就比较麻烦了