求一句SQL语句的写法,不要存储过程

chanfengsr 2008-03-17 11:18:19

有一张表:
CREATE TABLE [dbo].[Invt] (
[InvtID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataTime] [datetime] NULL ,
[UnitCost] [float] NULL
)

希望得到每个Invt以日期排序的前5个,(如果DataTime与UnitCost相同算一个)

例如:
数据如下:
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)

应得到:
A 2008-03-06 40.0
A 2008-03-05 60.0
A 2008-03-04 20.0
A 2008-03-04 30.0
A 2008-03-03 54.0
B 2008-03-07 12.0
B 2008-03-06 90.0
B 2008-03-05 50.0
B 2008-03-04 22.0
B 2008-03-04 31.0
...全文
342 点赞 收藏 34
写回复
34 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
chanfengsr 2008-03-19
回楼上的JiangHongTao,第二种方法不对,
第一种方法我稍微修正了下对了。不好意思,节贴了才发现的。
第一种方法甚是巧妙!!见识了!(虽然在主字段上使用了函数在数据量大的情况下会很慢)

改后的语句。
SELECT DISTINCT *
FROM invt t
WHERE checksum(*) IN (SELECT TOP 5 checksum(*)
FROM (
SELECT DISTINCT *
FROM invt
)i
WHERE i.invtid = t.invtid
ORDER BY
i.datatime DESC)
ORDER BY
invtid,
datatime DESC
回复
chanfengsr 2008-03-17
回14楼的:这样用“[datatime] in ”肯定是不对的, 如果 100个InvtID为‘B’的datatime都是2007-01-01,而UnitCost 各不相同,这样的话其实只要取前5个就行了,但是用用“[datatime] in ”的话肯定是全部取出来了。

请大家用我八楼的数据得出数据下面的答案。

谢谢各位的帮助,我先去吃饭,吃完饭回来加分~~
回复
flairsky 2008-03-17
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
回复
flairsky 2008-03-17
这样吧,唉,设计问题不小啊,勉强实现
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
回复
flairsky 2008-03-17
看了这个问题我不得不说一句

设计的好不好,看实现的困难不困难就可见一斑

这个问题总结出来不失一般性是这样的:
1、有n个invtid就会有n*m条记录,m为top数(有可能小于这个数,不影响结果)
2、要按照每个invtid进行时间上的排序
3、不能用临时表,少了个重要工具 - -!!
4、要做成视图,嵌套要少用……

唉,要多想想
回复
nextflying 2008-03-17
有一张表:
CREATE TABLE [dbo].[Invt] (
[InvtID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataTime] [datetime] NULL ,
[UnitCost] [float] NULL
)

希望得到每个Invt以日期排序的前5个,(如果DataTime与UnitCost相同算一个)

例如:
数据如下:
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)

应得到:
A 2008-03-06 40.0
A 2008-03-05 60.0
A 2008-03-04 20.0
A 2008-03-04 30.0
A 2008-03-03 54.0
B 2008-03-07 12.0
B 2008-03-06 90.0
B 2008-03-05 50.0
B 2008-03-04 22.0
B 2008-03-04 31.0

不用SQL 查 , 大脑分析一下 , 你的结果是不是对呢?? 如果 认为对, 那就是本身我们理解不一致!! , 更谈不上解决问题了
回复
nextflying 2008-03-17
加分吧 , 加完再续
回复
nextflying 2008-03-17
希望得到每个Invt以日期排序的前5个,(如果DataTime与UnitCost相同算一个???????????????????)
回复
chanfengsr 2008-03-17
很抱歉还是不对!!每个InvtID都要有5个,where Datatime in 的话显然是不对的 如果 100个InvtID为‘B’的datatime都是07-01-01的话你怎么办!!

请用以下数据:
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('A','2008-3-6',40)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-1',73)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-7',12)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-2',65)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-3',44)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-4',22)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-4',31)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-5',50)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-6',90)
INSERT INTO Invt(InvtID,DataTime,UnitCost)VALUES('B','2008-4-7',12)

得出以下结论:
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 20.0
A 2008-03-04 00:00:00.000 30.0
A 2008-03-03 00:00:00.000 54.0
B 2008-04-07 00:00:00.000 12.0
B 2008-04-06 00:00:00.000 90.0
B 2008-04-05 00:00:00.000 50.0
B 2008-04-04 00:00:00.000 22.0
B 2008-04-04 00:00:00.000 31.0


看来要加分了~~
回复
nextflying 2008-03-17
select * from Invt where Datatime in ( select Datatime from (select distinct top 5 * , (DataTime + UnitCost / 100.0) s from Invt order by s desc) t )

OK??
回复
chanfengsr 2008-03-17
拜托,5楼的大哥,我说得应该蛮清楚的,是“每个Invt以日期排序的前5个”,不是总共5个!!
回复
nextflying 2008-03-17

select distinct top 5 * , (DataTime + UnitCost / 100.0) s from Invt order by s desc
回复
chanfengsr 2008-03-17
两位回答的都不对,InvtID可能有很多不能简单的 top 10 ,每个InvtID的datatime可能相差很多,比如100个InvtID为‘A’的datatime 是从06-01-01至06-12-30,而100个InvtID为‘B’的datatime是从07-01-01至07-12-30的,所以二楼的做法也是不对的。
回复
nextflying 2008-03-17
select top 10 * from Invt order by (DataTime + UnitCost / 100.0) desc
回复
chanfengsr 2008-03-17
补充一下,不要建临表,这句SQL语句要进视图的
回复
dawugui 2008-03-17
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 行)
*/
回复
JiangHongTao 2008-03-17
1.
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

2.
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
回复
dawugui 2008-03-17
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 行)
*/
回复
loworth 2008-03-17
jf
回复
BILearner 2008-03-17
.......能指点一下,我错在了哪里?
回复
加载更多回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-17 11:18
社区公告
暂无公告