insert 表 select a,'01','0308',D,getdate() from 表 tem where datediff(month,E,getdate())=1 and B=(select top 1 b from 表 where datediff(month,E,getdate())=1 and A=tem.A order by b desc)
罵可: 伺服器: 訊息 147,層級 16,狀態 2,行 1 除非彙總置於 HAVING 子句或選取清單所包含的子查詢中,且彙總的資料行為外部參考,否則不得在 WHERE 子句中出現。
--测试: --你先改系统时间为8月1号create table 你的表 (a varchar(10),b varchar(10),c varchar(10),d int,e datetime) insert 你的表 values('BK01','01','0307',2000,'20030701') insert 你的表 values('BK02','01','0307',2000,'20030701') insert 你的表 values('BK01','02','0307',2000,'20030705') insert 你的表 values('BK01','03','0307',2000,'20030711') insert 你的表 values('BK02','02','0307',1340,'20030708') insert 你的表 values('BK01','04','0307',2200,'20030722') insert 你的表 values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01','0308',D,convert(char(8),getdate(),112) from 你的表 tem where datediff(month,E,getdate())=1 and B=(select top 1 b from 你的表 where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a --如果你要插入:insert 你的表 select a,'01','0308',D,convert(char(8),getdate(),112) from 你的表 tem where datediff(month,E,getdate())=1 and B=(select top 1 b from 你的表 where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a select * from 你的表 go drop table 你的表
ok--测试: --你先改系统时间为8月1号create table #a (a varchar(10),b varchar(10),c varchar(10),d int,e datetime) insert #a values('BK01','01','0307',2000,'20030701') insert #a values('BK02','01','0307',2000,'20030701') insert #a values('BK01','02','0307',2000,'20030705') insert #a values('BK01','03','0307',2000,'20030711') insert #a values('BK02','02','0307',1340,'20030708') insert #a values('BK01','04','0307',2200,'20030722') insert #a values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a --如果你要插入:insert #a select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a select * from #ago drop table #a
如果你的e这列不是datetime类型: --测试: --你先改系统时间为8月1号create table #a (a varchar(10),b varchar(10),c varchar(10),d int,e char(8)) insert #a values('BK01','01','0307',2000,'20030701') insert #a values('BK02','01','0307',2000,'20030701') insert #a values('BK01','02','0307',2000,'20030705') insert #a values('BK01','03','0307',2000,'20030711') insert #a values('BK02','02','0307',1340,'20030708') insert #a values('BK01','04','0307',2200,'20030722') insert #a values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a --如果你要插入:insert #a select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a select * from #ago drop table #a
select A,'01','0308',Max(D),'20030801' from t where left(E,6)='200307'group by A
update table1 aa
set b = '01',C = '0308'
where C = (Select max(c) from table1 where a = aa.a )
update table1 aa
set b = '01',C = '0308',E='20030801'
where C = (Select max(c) from table1 where a = aa.a )
假如現在是九月一號,取表中八月A相同字段的最大序號得數據插入表中,把C改成0308,B改成
01,E改成20030801
伺服器: 訊息 147,層級 16,狀態 2,行 1
除非彙總置於 HAVING 子句或選取清單所包含的子查詢中,且彙總的資料行為外部參考,否則不得在 WHERE 子句中出現。
--你先改系统时间为8月1号create table 你的表 (a varchar(10),b varchar(10),c varchar(10),d int,e datetime)
insert 你的表 values('BK01','01','0307',2000,'20030701')
insert 你的表 values('BK02','01','0307',2000,'20030701')
insert 你的表 values('BK01','02','0307',2000,'20030705')
insert 你的表 values('BK01','03','0307',2000,'20030711')
insert 你的表 values('BK02','02','0307',1340,'20030708')
insert 你的表 values('BK01','04','0307',2200,'20030722')
insert 你的表 values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01','0308',D,convert(char(8),getdate(),112) from 你的表 tem where datediff(month,E,getdate())=1 and B=(select top 1 b from 你的表 where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
--如果你要插入:insert 你的表 select a,'01','0308',D,convert(char(8),getdate(),112) from 你的表 tem where datediff(month,E,getdate())=1 and B=(select top 1 b from 你的表 where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
select * from 你的表 go
drop table 你的表
假如現在是十月:
BK01 01 0309 2000 20030901
變成:
BK01 01 0309 2000 20030901
BK01 01 0310 2000 20031001
--你先改系统时间为8月1号create table #a (a varchar(10),b varchar(10),c varchar(10),d int,e datetime)
insert #a values('BK01','01','0307',2000,'20030701')
insert #a values('BK02','01','0307',2000,'20030701')
insert #a values('BK01','02','0307',2000,'20030705')
insert #a values('BK01','03','0307',2000,'20030711')
insert #a values('BK02','02','0307',1340,'20030708')
insert #a values('BK01','04','0307',2200,'20030722')
insert #a values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
--如果你要插入:insert #a select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
select * from #ago
drop table #a
--测试:
--你先改系统时间为8月1号create table #a (a varchar(10),b varchar(10),c varchar(10),d int,e char(8))
insert #a values('BK01','01','0307',2000,'20030701')
insert #a values('BK02','01','0307',2000,'20030701')
insert #a values('BK01','02','0307',2000,'20030705')
insert #a values('BK01','03','0307',2000,'20030711')
insert #a values('BK02','02','0307',1340,'20030708')
insert #a values('BK01','04','0307',2200,'20030722')
insert #a values('BK02','01','0306',2000,'20030621')--如果你要显示:select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
--如果你要插入:insert #a select a,'01',right(convert(char(8),getdate(),112),4),D,convert(char(8),getdate(),112) from #a tem where datediff(month,E,getdate())=1 and B=(select top 1 b from #a where datediff(month,E,getdate())=1 and A=tem.A order by b desc) order by a
select * from #ago
drop table #a
create table #a (a varchar(10),b varchar(10),c varchar(10),d int,e char(8))
insert #a values('BK01','01','0309',2000,'20030701')
insert #a values('BK02','01','0309',2000,'20030701')
insert #a values('BK01','02','0309',2000,'20030705')
insert #a values('BK01','03','0309',2000,'20030711')
insert #a values('BK02','02','0309',1340,'20030708')
insert #a values('BK01','04','0309',2200,'20030722')
insert #a values('BK02','01','0306',2000,'20030621')就不行了