比如10月的数据如下
批次: 精度:
111111 ......
111111 1.123
111111 1.121
222222 ......
222222 1.732
222222 1.341
222222 1.195
333333 ......
333333 1.643
333333 1.932
333333 1.283
****** ......
****** 1.721现在要计算每批生产完成时精度(即每批最后一个精度值)的平均值,望大家赐教
批次: 精度:
111111 ......
111111 1.123
111111 1.121
222222 ......
222222 1.732
222222 1.341
222222 1.195
333333 ......
333333 1.643
333333 1.932
333333 1.283
****** ......
****** 1.721现在要计算每批生产完成时精度(即每批最后一个精度值)的平均值,望大家赐教
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (批次 int,精度 numeric(4,3))
insert into #tb
select 111111,1.123 union all
select 111111,1.121 union all
select 222222,1.732 union all
select 222222,1.341 union all
select 222222,1.195 union all
select 333333,1.643 union all
select 333333,1.932 union all
select 333333,1.283alter table #tb add id int identity(1,1)select avg(精度) from #tb t
where not exists(select * from #tb where 批次=t.批次 and id>t.id)
select (1.121+1.195+1.283)/3.0---------------------------------------
1.199666(1 行受影响)
---------------------------------------
1.199666(1 行受影响)
go
create table #tb (批次 int,精度 numeric(4,3))
insert into #tb
select 111111,1.123 union all
select 111111,1.121 union all
select 222222,1.732 union all
select 222222,1.341 union all
select 222222,1.195 union all
select 333333,1.643 union all
select 333333,1.932 union all
select 333333,1.283;WITH CTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY 批次 ORDER BY GETDATE()) AS RN,批次,精度 FROM tempdb.dbo.#tb
)
SELECT AVG(精度) FROM CTE C WHERE NOT EXISTS(SELECT 1 FROM CTE C1 WHERE C.批次=C1.批次 AND C.RN<C1.RN)
create table tb (批次 int,精度 numeric(4,3))
insert into tb
select 111111,1.123 union all
select 111111,1.121 union all
select 222222,1.732 union all
select 222222,1.341 union all
select 222222,1.195 union all
select 333333,1.643 union all
select 333333,1.932 union all
select 333333,1.283--构造一个序列表
select * , id = identity(int,1,1) into tmp from tbselect t.批次,t.精度 from tmp t where id = (select max(id) from tmp where 批次 = t.批次) order by t.批次
/*
批次 精度
----------- ------
111111 1.121
222222 1.195
333333 1.283(所影响的行数为 3 行)
*/select t.批次,t.精度 from tmp t where not exists (select 1 from tmp where 批次 = t.批次 and id > t.id) order by t.批次
/*
批次 精度
----------- ------
111111 1.121
222222 1.195
333333 1.283(所影响的行数为 3 行)
*/drop table tb , tmp
select avg(精度) from #tb t
where not exists(select * from #tb where 批次=t.批次 and id>t.id)但是上面的代码什么意思啊?