请教各位大虾:
有表
LINE PART DATE CREATE_BY CREATE_TIME UPDATE_BY UPDATE_TIME
A1 1111 11/29/2007 ADMIN 2007-11-29 15:10:35.610 ADMIN 2007-11-29 15:34:35.610
A1 2222 11/29/2007 ADMIN 2007-11-29 11:05:35.610 ADMIN 2007-11-29 14:34:35.610
B1 1111 11/29/2007 ADMIN 2007-11-29 08:28:35.610 ADMIN 2007-11-29 15:47:35.610
B1 3333 11/29/2007 ADMIN 2007-11-29 09:20:35.610 ADMIN 2007-11-29 12:34:35.610
B1 4444 11/29/2007 ADMIN 2007-11-29 07:34:35.610 ADMIN 2007-11-29 10:55:35.610选取该表中每条拉最后生产的part 记录.在线候教!!!
有表
LINE PART DATE CREATE_BY CREATE_TIME UPDATE_BY UPDATE_TIME
A1 1111 11/29/2007 ADMIN 2007-11-29 15:10:35.610 ADMIN 2007-11-29 15:34:35.610
A1 2222 11/29/2007 ADMIN 2007-11-29 11:05:35.610 ADMIN 2007-11-29 14:34:35.610
B1 1111 11/29/2007 ADMIN 2007-11-29 08:28:35.610 ADMIN 2007-11-29 15:47:35.610
B1 3333 11/29/2007 ADMIN 2007-11-29 09:20:35.610 ADMIN 2007-11-29 12:34:35.610
B1 4444 11/29/2007 ADMIN 2007-11-29 07:34:35.610 ADMIN 2007-11-29 10:55:35.610选取该表中每条拉最后生产的part 记录.在线候教!!!
A1 1111 11/29/2007 ADMIN 2007-11-29 15:10:35.610 ADMIN 2007-11-29 15:34:35.610
B1 1111 11/29/2007 ADMIN 2007-11-29 08:28:35.610 ADMIN 2007-11-29 15:47:35.610 取A1拉 最后更新的记录和B1拉 最后更新的记录。
按照你上面的方法试了下,好象不行。
建个简单的表输入上面的记录,调试通过没有啊? UPDATE_TIME 还有可能是NULL的可能性.
如有这样的记录:A1 1111 11/29/2007 ADMIN 2007-11-29 15:10:35.610 NULL NULL 在线候教!
[/code]UPDATE YOURTABLE SET UPDATE_BY = CREATE_BY,UPDATE_TIME = CREATE_TIME WHERE UPDATE_TIME IS NULL[/code]二,
[/code]
SELECT LINE, FIRST(PART), FIRST([DATE]), FIRST(CREATE_BY), FIRST(CREATE_TIME), FIRST(UPDATE_BY),FIRST(UPDATE_TIME) FROM (SELECT * YUORTABLE ORDER BY UPDATE_TIME DESC) GROUP BY LINE
[/code]
table name: today_Model
td_SBU td_line td_part td_process td_Createtime td_Createby td_Updatetime td_Updateby
SBU4 LXK 3206-11 PACKING 2007-11-30 00:00:00.000 1012048 2007-11-30 10:10:08.733 1012048 2007-11-30 10:46:24.420
SBU4 LXK r4698 PACKING 2007-11-30 00:00:00.000 1012054 2007-11-30 10:10:49.610 1012054 2007-11-30 10:46:48.233
SBU4 MSBI-2 r2969-a FQC 2007-11-30 00:00:00.000 1018881 2007-11-30 10:14:54.687 1018117 2007-11-30 10:47:44.670
SBU4 SMTM r4905-0 PACKING 2007-11-30 00:00:00.000 1000375 2007-11-30 10:34:00.733 1000375 2007-11-30 10:34:10.077
SBU4 SMTM1 R3817-C PACKING 2007-11-30 00:00:00.000 1015964 2007-11-30 10:19:43.123 1015964 2007-11-30 10:38:27.590
SBU4 SMTM2 R3817-C PACKING 2007-11-30 00:00:00.000 1017497 2007-11-30 10:39:31.217 1017497 2007-11-30 10:39:31.937
SBU4 SMTM3 R3817-C PACKING 2007-11-30 00:00:00.000 1017610 2007-11-30 10:27:59.093 1017610 2007-11-30 10:39:53.763
SBU4 SMTM5 R3817-C PACKING 2007-11-30 00:00:00.000 1016256 2007-11-30 10:15:51.593 1016256 2007-11-30 10:35:11.780
SBU4 SMTM6 R3817-C PACKING 2007-11-30 00:00:00.000 1000712 2007-11-30 10:23:17.310 1000712 2007-11-30 10:43:00.263运行环境: SQL2000 查询分析器中输入
SELECT td_sbu,td_line,FIRST(td_part), FIRST(td_date), FIRST(td_updateby),FIRST(td_updatetime) FROM (SELECT * from today_model ORDER BY td_updatetime DESC) GROUP BY td_lineERROR Information:
Server: Msg 195, Level 15, State 10, Line 1
'FIRST' is not a recognized function name.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ORDER'.
每条LINE 按 UPDATE_TIME (由时间最后的排) 取 top 1 的思路去写SQL。
一条全部取出我不清楚怎么写,但每条LINE分别取是很简单的。
select top 1 * from today_Model where td_sbu='SBU4' and td_line='LXK' order by td_updatetime DESC现在的要求是: 一条SQL语句取出所有符合要求的记录。
我考虑的是: 自身连自身的表。 但我写了半天写的结果不对
select td_sbu ,td_line ,td_part,td_date ,td_updatetime from today_Model a where td_updatetime=(select max(td_updatetime) from today_Model b where b.td_sbu=a.td_sbu and b.td_line=a.td_line)各位大虾门过来看看,帮帮忙啊