34,587
社区成员
发帖
与我相关
我的任务
分享
A 2007-03-06 00:00:00.000 40
A 2007-03-05 00:00:00.000 60
A 2007-03-04 00:00:00.000 20
A 2007-03-04 00:00:00.000 30
A 2007-03-03 00:00:00.000 54
B 2008-03-06 00:00:00.000 90
B 2008-03-05 00:00:00.000 50
B 2008-03-04 00:00:00.000 22
B 2008-03-04 00:00:00.000 31
B 2008-03-03 00:00:00.000 44
c 2009-03-07 00:00:00.000 42
c 2008-03-07 00:00:00.000 32
c 2007-03-07 00:00:00.000 22
c 2006-03-07 00:00:00.000 12
drop table #invt
CREATE TABLE #Invt (
[InvtID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataTime] [datetime] NULL ,
[UnitCost] [float] NULL
)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-1',20)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-2',78)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-3',54)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-4',20)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-4',30)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-5',60)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('A','2007-3-6',40)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-1',73) ---这总差开了
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-2',65)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',22)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',31)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-5',50)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-6',90)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('c','2006-3-7',12) --弄个bt的
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('c','2007-3-7',22)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('c','2008-3-7',32)
INSERT INTO #Invt(InvtID,DataTime,UnitCost)VALUES('c','2009-3-7',42)
select distinct * from #Invt t where [datatime] in (select top 5 [datatime] from #invt where invtid = t.invtid order by [datatime] desc) order by t.invtid , datatime desc
CREATE TABLE [dbo].[Invt] (
[InvtID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataTime] [datetime] NULL ,
[UnitCost] [float] NULL
)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-1',20)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-2',78)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-3',54)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-4',20)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-4',30)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-5',60)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-6',40)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-1',73)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-2',65)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',22)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',31)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-5',50)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-6',90)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-7',12)
go
select * from Invt t where [datatime] in (select top 5 [datatime] from invt where invtid = t.invtid order by [datatime] desc) order by t.invtid , datatime desc
drop table Invt
/*
InvtID DataTime UnitCost
---------- ------------------------------------------------------ -----------------------------------------------------
A 2008-03-06 00:00:00.000 40.0
A 2008-03-05 00:00:00.000 60.0
A 2008-03-04 00:00:00.000 30.0
A 2008-03-04 00:00:00.000 20.0
A 2008-03-03 00:00:00.000 54.0
B 2008-03-07 00:00:00.000 12.0
B 2008-03-06 00:00:00.000 90.0
B 2008-03-05 00:00:00.000 50.0
B 2008-03-04 00:00:00.000 31.0
B 2008-03-04 00:00:00.000 22.0
(所影响的行数为 10 行)
*/
select distinct * from invt t
where checksum(*) in (select top 5 checksum(*) from invt where invtid=t.invtid order by datatime desc)
order by invtid,datatime desc
select distinct * from invt t
where (select count(distinct unitcost) from invt where invtid=t.invtid and datatime >=t.datatime) <=5
order by invtid,datatime desc
CREATE TABLE [Invt] (
[InvtID] [char] (10) NOT NULL ,
[DataTime] [datetime] NOT NULL ,
[UnitCost] [float] NOT NULL
)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-1',20)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-2',78)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-3',54)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-4',20)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-4',30)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-5',60)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('A','2008-3-6',40)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-1',73)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-2',65)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',22)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-4',31)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-5',50)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-6',90)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-3-7',12)
go
select * from
(
select distinct * from invt
) m where datatime in
(
select top 5 datatime from
(
select distinct * from invt
) n
where invtid = m.invtid order by datatime desc
)
drop table invt
/*
InvtID DataTime UnitCost
---------- ------------------------------------------------------ -----------------------------------------------------
A 2008-03-03 00:00:00.000 54.0
A 2008-03-04 00:00:00.000 20.0
A 2008-03-04 00:00:00.000 30.0
A 2008-03-05 00:00:00.000 60.0
A 2008-03-06 00:00:00.000 40.0
B 2008-03-04 00:00:00.000 22.0
B 2008-03-04 00:00:00.000 31.0
B 2008-03-05 00:00:00.000 50.0
B 2008-03-06 00:00:00.000 90.0
B 2008-03-07 00:00:00.000 12.0
(所影响的行数为 10 行)
*/