select
sum(库存) as 库存合计
from
(
select 库存,时间
from tb t
where not exists(select * from tb where convert(char(10),时间,120)=convert(char(10),t.时间,120) and 时间>t.时间)
) tt
sum(库存) as 库存合计
from
(
select 库存,时间
from tb t
where not exists(select * from tb where convert(char(10),时间,120)=convert(char(10),t.时间,120) and 时间>t.时间)
) tt
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([库存] int,[时间] datetime)
insert [tb]
select 3,'2009-1-1 19:18:20' union all
select 5,'2009-1-1 19:28:40' union all
select 5,'2009-1-2 12:21:43' union all
select 12,'2009-1-3 19:28:40' union all
select 5,'2009-1-3 23:21:43' union all
select 6,'2009-1-3 23:28:40'
---查询---
select
sum(库存) as 库存合计
from
(
select *
from tb t
where not exists(select 1 from tb where convert(char(10),时间,120)=convert(char(10),t.时间,120) and 时间>t.时间)
) tt
---结果---
库存合计
-----------
16(所影响的行数为 1 行)
sum(库存) as 库存合计
from tb t
where not exists(select 1 from tb where convert(char(10),时间,120)=convert(char(10),t.时间,120) and 时间>t.时间)这样就可以了,看来今天有点晕...
select sum(库存) from tb t where 时间 = (select max(时间) from tb convert(varchar(10),时间,120) = convert(varchar(10),t.时间,120))
insert [tb]
select 3,'2009-1-1 19:18:20' union all
select 5,'2009-1-1 19:28:40' union all
select 5,'2009-1-2 12:21:43' union all
select 12,'2009-1-3 19:28:40' union all
select 5,'2009-1-3 23:21:43' union all
select 6,'2009-1-3 23:28:40'select sum(库存) from tb t where 时间 = (select max(时间) from tb where convert(varchar(10),时间,120) = convert(varchar(10),t.时间,120))drop table tb/*
-----------
16(所影响的行数为 1 行)
*/
create table libaray
(
sign int,
[time] datetime
)
insert into libaray
select 3 ,'2009-1-1 19:18:20 ' union all
select 5 ,'2009-1-1 19:28:40 ' union all
select 5 ,'2009-1-2 12:21:43 ' union all
select 12 ,'2009-1-3 19:28:40 ' union all
select 5 ,'2009-1-3 23:21:43 ' union all
select 6 ,'2009-1-3 23:28:40 '
------------------------------------------
select sum([sign]) 合計
from
(
select *,
row_number() over(partition by convert(char(10),[time],120) order by [time] desc) as orders
from libaray
)A
where orders=1
--结果----------------------------------
/*
合計
16
*/
create table libaray
(
sign int,
[time] datetime
)
insert into libaray
select 3 ,'2009-1-1 19:18:20 ' union all
select 5 ,'2009-1-1 19:28:40 ' union all
select 5 ,'2009-1-2 12:21:43 ' union all
select 12 ,'2009-1-3 19:28:40 ' union all
select 5 ,'2009-1-3 23:21:43 ' union all
select 6 ,'2009-1-3 23:28:40 '
-----------------------------------------------
select sum(sign) [合計] from libaray B
where not exists
(
select 1
from libaray A
where
convert(char(10),B.[time],120)=convert(char(10),A.[time],120)
and A.[time]>B.[time]
)
--结果-----------------------------------------
/*
合計
16
*/