有一表A,數據如下﹕NAME DH QTY DATE
AA62B30590-US 5026902 7200 2005/3/7 00:00
AA62B30590-US 5041008 4000 2005/3/16 00:00
AA62B30590-US 5041022 1000 2005/3/17 00:00
AA62B30590-US 5041023 2800 2005/3/18 00:00
AA62B30590-US A6-D0173/05 15000 2005/3/18 00:00
AA62B31200-US A6-D1416/03 8000 2003/10/20 00:00
AA62B31200-US 24154 4800 2003/10/20 00:00
AA62B31200-US 15543 8000 2003/10/20 00:00
AA62B31200-US A6-D1398/03 4800 2003/10/21 00:00
AA62B31200-US 23824 14400 2003/11/1 00:00
AA62B31200-US A6-D1461/03 12880 2003/11/1 00:00
AA62B31200-US A6-D1489/03 1520 2003/11/4 00:00表B:NAME VERSION
AA62B30590-US 2005020700
AA62B30590-US 2005030600
AA62B30590-US 2005031500
AA62B30590-US 2005032000
AA62B30590-US 2005031700
AA62B31200-US 2003093000
AA62B31200-US 2003100100
AA62B31200-US 2003101900
AA62B31200-US 2003102100
AA62B31200-US 2003110100
AA62B31200-US 2003110400
注﹕表B中的字段VERSION是由一個日期轉換而成的﹐轉換的代碼﹕convert(char(8),date,112)+convert(char(2),date,108) AS VERSION
關聯字段﹐用A表NAME與B表NAME
然后用convert(char(8),date,112)+convert(char(2),date,108)尋出B表中VERSTION中小于當前記錄中DATE的最大值。
結果如下﹕NAME DH QTY DATE Version
AA62B30590-US 5026902 7200 2005/3/7 00:00 2005030600
AA62B30590-US 5041008 4000 2005/3/16 00:00 2005031500
AA62B30590-US 5041022 1000 2005/3/16 00:00 2005031500
AA62B30590-US 5041023 2800 2005/3/18 00:00 2005031700
AA62B30590-US A6-D0173/05 15000 2005/3/18 00:00 2005031700
AA62B31200-US A6-D1416/03 8000 2003/10/20 00:00 2003100100
AA62B31200-US 24154 4800 2003/10/20 00:00 2003100100
AA62B31200-US 15543 8000 2003/10/20 00:00 2003100100
AA62B31200-US A6-D1398/03 4800 2003/10/21 00:00 2003101900
AA62B31200-US 23824 14400 2003/11/1 00:00 2003102100
AA62B31200-US A6-D1461/03 12880 2003/11/1 00:00 2003102100
AA62B31200-US A6-D1489/03 1520 2003/11/4 00:00 2003110100
AA62B30590-US 5026902 7200 2005/3/7 00:00
AA62B30590-US 5041008 4000 2005/3/16 00:00
AA62B30590-US 5041022 1000 2005/3/17 00:00
AA62B30590-US 5041023 2800 2005/3/18 00:00
AA62B30590-US A6-D0173/05 15000 2005/3/18 00:00
AA62B31200-US A6-D1416/03 8000 2003/10/20 00:00
AA62B31200-US 24154 4800 2003/10/20 00:00
AA62B31200-US 15543 8000 2003/10/20 00:00
AA62B31200-US A6-D1398/03 4800 2003/10/21 00:00
AA62B31200-US 23824 14400 2003/11/1 00:00
AA62B31200-US A6-D1461/03 12880 2003/11/1 00:00
AA62B31200-US A6-D1489/03 1520 2003/11/4 00:00表B:NAME VERSION
AA62B30590-US 2005020700
AA62B30590-US 2005030600
AA62B30590-US 2005031500
AA62B30590-US 2005032000
AA62B30590-US 2005031700
AA62B31200-US 2003093000
AA62B31200-US 2003100100
AA62B31200-US 2003101900
AA62B31200-US 2003102100
AA62B31200-US 2003110100
AA62B31200-US 2003110400
注﹕表B中的字段VERSION是由一個日期轉換而成的﹐轉換的代碼﹕convert(char(8),date,112)+convert(char(2),date,108) AS VERSION
關聯字段﹐用A表NAME與B表NAME
然后用convert(char(8),date,112)+convert(char(2),date,108)尋出B表中VERSTION中小于當前記錄中DATE的最大值。
結果如下﹕NAME DH QTY DATE Version
AA62B30590-US 5026902 7200 2005/3/7 00:00 2005030600
AA62B30590-US 5041008 4000 2005/3/16 00:00 2005031500
AA62B30590-US 5041022 1000 2005/3/16 00:00 2005031500
AA62B30590-US 5041023 2800 2005/3/18 00:00 2005031700
AA62B30590-US A6-D0173/05 15000 2005/3/18 00:00 2005031700
AA62B31200-US A6-D1416/03 8000 2003/10/20 00:00 2003100100
AA62B31200-US 24154 4800 2003/10/20 00:00 2003100100
AA62B31200-US 15543 8000 2003/10/20 00:00 2003100100
AA62B31200-US A6-D1398/03 4800 2003/10/21 00:00 2003101900
AA62B31200-US 23824 14400 2003/11/1 00:00 2003102100
AA62B31200-US A6-D1461/03 12880 2003/11/1 00:00 2003102100
AA62B31200-US A6-D1489/03 1520 2003/11/4 00:00 2003110100
解决方案 »
- Sql 2000 行列转换的问题
- 【百分求助】SQL SERVER 2005对于30多个GB的数据库文件大小支持怎么样
- 请教用newid 取随机数
- sql.....
- 请问with(nolock)到底用处在哪?不加它是不是对数据库的性能有影响?
- 如何利用日志文件进行数据的回滚?
- 行整合查询
- 求教SQL语句:请高手帮我看看!!!!!!!!!!
- 麻烦大家一起给我想个好的解决方案好吗?谢谢
- 在enterprise manager中数据无法更新,提示transcation cannot start while in firehose mode
- 请教一个select的问题
- 非id排序有没有比较理想的分页办法?
(
[NAME] varchar(20),
DH varchar(20),
QTY int,
[DATE] datetime
)
insert into t
select 'AA62B30590-US', '5026902',7200, '2005/3/7 00:00' union all
select 'AA62B30590-US', '5041008',4000, '2005/3/16 00:00' union all
select 'AA62B30590-US', '5041022',1000, '2005/3/17 00:00' union all
select 'AA62B30590-US', '5041023',2800,'2005/3/18 00:00' union all
select 'AA62B30590-US', 'A6-D0173/05', 15000, '2005/3/18 00:00' union all
select 'AA62B31200-US', 'A6-D1416/03', 8000, '2003/10/20 00:00' union all
select 'AA62B31200-US', '24154', 4800,'2003/10/20 00:00' union all
select 'AA62B31200-US', '15543', 8000, '2003/10/20 00:00' union all
select 'AA62B31200-US', 'A6-D1398/03', 4800, '2003/10/21 00:00' union all
select 'AA62B31200-US', '23824', 14400, '2003/11/1 00:00' union all
select 'AA62B31200-US', 'A6-D1461/03', 12880, '2003/11/1 00:00' union all
select 'AA62B31200-US', 'A6-D1489/03', 1520, '2003/11/4 00:00'create table tt
(
[NAME] varchar(20),
[VERSION] varchar(20)
)
insert into tt
select 'AA62B30590-US', '2005020700' union all
select 'AA62B30590-US', '2005030600' union all
select 'AA62B30590-US', '2005031500' union all
select 'AA62B30590-US', '2005032000' union all
select 'AA62B30590-US', '2005031700' union all
select 'AA62B31200-US', '2003093000' union all
select 'AA62B31200-US', '2003100100' union all
select 'AA62B31200-US', '2003101900' union all
select 'AA62B31200-US', '2003102100' union all
select 'AA62B31200-US', '2003110100' union all
select 'AA62B31200-US', '2003110400'select t.[NAME],t.DH,t.QTY,[DATE], max(tt.VERSION) as VERSION from t,tt where t.[NAME]=tt.[NAME]
and tt.VERSION < (convert(char(8),[DATE],112)+convert(char(2),[DATE],108))
group by t.[NAME],DH,QTY,[DATE]
SELECT a.*,version =
(
select max(VERSION)
from tt where name = a.name and version < convert(char(8),a.date,112)+convert(char(2),a.date,108)
)
FROM t a