兄弟,你试了我给你的两个没有,若不成功请帖错误提示:
select *,identity(int,1,1) as ID into netty2 from (
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01) a
或者
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid,identity(int,1,1) as ID into netty2 from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01(order by 里必须是tr_date01而不是tr_mod_date)
select *,identity(int,1,1) as ID into netty2 from (
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01) a
或者
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid,identity(int,1,1) as ID into netty2 from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01(order by 里必须是tr_date01而不是tr_mod_date)
表示產的字段值從n開始,每次增加m
如:identity(int,2,3)
表示產的字段值從2開始,每次增加3即2,5,8,11,...
1。/*************************************/
select *,identity(int,1,1) as ID into netty2 from (
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01) a
/***************************************/
错误原因:
(1)tr_date01 as tr_mod_date,我的tr_hist表里面根本没有tr_date01这个字段。
(2)An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
2。
/***************************************/
select tr_part, tr_date, tr_cmp, tr_date01 as tr_mod_date, '5' as tr_grpid,identity(int,1,1) as ID into netty2 from tr_hist where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11' order by tr_date, tr_date01
/***************************************/有时侯是正确的,有时候是错误的,当tr_date的时间发生改变时,ID就不是按照order by tr_date,tr_mode_date来排序的了。
SQL补丁:
'全部补丁的位置
http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=9032f608-160a-4537-a2b6-4cb265b80766'应该安装的补丁sql 2000 sp3
http://download.microsoft.com/download/3/c/0/3c06f7e7-39f5-4dec-b295-a3849e348a39/chs_sql2kasp3.exe'for asp的补丁
http://download.microsoft.com/download/3/c/0/3c06f7e7-39f5-4dec-b295-a3849e348a39/chs_sql2kasp3.exe'桌面版本的补丁
http://download.microsoft.com/download/3/c/0/3c06f7e7-39f5-4dec-b295-a3849e348a39/CHS_SQL2KDeskSP3.exe
既然你的tr_hist表里面根本没有tr_date01这个字段,那为什么你还要select tr_part, tr_date, tr_cmp, tr_date01...?(这里为什么有tr_date01?)SOrry,我没看清楚,你就当tr_date01 as tr_made_Date 为tr_date01好了。不要别名一样的但是对于第一种解决方法这个错误是存在的:
An ORDER BY clause is invalid in views, derived tables, and subqueries unless TOP is also specified.
select identity(int,1,1) as ID,tr_part,tr_date,tr_cmp,tr_date01,'5' as tr_grp
from tr_hist
where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11'
order by tr_date,tr_date01
select identity(int,1,1) as ID,tr_part,tr_date,tr_cmp,tr_date01,'5' as tr_grp
into netty2
from tr_hist
where tr_cmp = '000' and tr_date <= '2004-09-10' and tr_date > '2003-09-11'
order by tr_date,tr_date01
d, 11, 22, 2003-01-01 00:00:00.000
b, 11, 22, 2003-01-10 00:00:00.000
c, 11, 22, 2003-01-05 00:00:00.000
a, 11, 22, 2003-01-15 00:00:00.000
问题变成,如果我需要生成下面的表,该如何写(按a1来编号,用identity function)
a1, a2, a3, date ,ID_num
a, 11, 22, 2003-01-15 00:00:00.000, 1
b, 11, 22, 2003-01-10 00:00:00.000, 2
c, 11, 22, 2003-01-05 00:00:00.000, 3
d, 11, 22, 2003-01-01 00:00:00.000, 4我需要下面的另外一张表,又如何写(按date来编号,用identity function)a1, a2, a3, date ,ID_num
d, 11, 22, 2003-01-01 00:00:00.000,1
c, 11, 22, 2003-01-05 00:00:00.000,2
b, 11, 22, 2003-01-10 00:00:00.000,3
a, 11, 22, 2003-01-15 00:00:00.000,4这样问题变得很清楚了吧,至于其他的Where条件就是附加条件了,这个解决了,就好办了。
Select a1,a2,a3,date,identity(int,1,1) id ID_num into #tem from table1 order by a1
select * from #tem
drop table #tem
2.
Select a1,a2,a3,date,identity(int,1,1) id ID_num into #tem from table1 order by date
select * from #tem
drop table #tem
declare @表 table(a1 char(1),a2 int,a3 int,date datetime)
insert into @表
select 'd',11,22,'2003-01-01 00:00:00.000'
union all select 'b',11,22,'2003-01-10 00:00:00.000'
union all select 'c',11,22,'2003-01-05 00:00:00.000'
union all select 'a',11,22,'2003-01-15 00:00:00.000'--按date
select *,id_num=identity(int,1,1) into #t1 from @表 order by dateselect * from #t1
godrop table #t1/*--测试结果
a1 a2 a3 date id_num
---- ----------- ----------- ------------------------ -------
d 11 22 2003-01-01 00:00:00.000 1
c 11 22 2003-01-05 00:00:00.000 2
b 11 22 2003-01-10 00:00:00.000 3
a 11 22 2003-01-15 00:00:00.000 4(所影响的行数为 4 行)
--*/