--使用了max,要进行分组group INSERT INTO cpy(bh, unitid, nd, type, buyyear, factory, address, tze, tzly, re)
SELECT CONVERT(varchar(50), MAX(bh) + 1) AS bh, unitid, CONVERT(varchar(50),
YEAR(GETDATE())) AS nd, type, buyyear, factory, address, tze, tzly, re
FROM cpy
WHERE (nd = YEAR(GETDATE()) - 1)
group by unitid,type, buyyear, factory, address, tze, tzly, re
SELECT CONVERT(varchar(50), MAX(bh) + 1) AS bh, unitid, CONVERT(varchar(50),
YEAR(GETDATE())) AS nd, type, buyyear, factory, address, tze, tzly, re
FROM cpy
WHERE (nd = YEAR(GETDATE()) - 1)
group by unitid,type, buyyear, factory, address, tze, tzly, re
解决方案 »
- sql查询语句
- sql 2005版本的问题(急!但最多只能给20分了)
- 如何看Function被哪些其他的Function或者Procedure调用了.
- right outer join 可否不填充null,填别的值(在线等)
- mysql默认值问题
- 数据库的日志与SQL SERVER日志
- SQL语句能不能写文件操作的?
- 有关存储过程的入门问题!
- 存储过程中取得当前插入行的自动递增列的值的问题??
- 是关于对SQL(SQL6.5)后台的操作出的问题,急急急!!!高分!
- 触发器的执行顺序是什么??
- VB代码执行一个*.sql脚本 不用循环去执行,而是我给这个.sql脚本存放的路径,代码调用SQL SERVER2000的查询分析器去执行这个脚本,怎么解
(bh, unitid, nd, type, buyyear, factory, address, tze, tzly, re)SELECT CONVERT(varchar(50), MAX(bh) + 1) AS bh, unitid, CONVERT(varchar(50),
YEAR(GETDATE())) AS nd, type, buyyear, factory, address, tze, tzly, re
FROM cpy
WHERE (nd = YEAR(GETDATE()) - 1)
group by unitid, type, buyyear, factory, address, tze, tzly, re
bh = identity(int,1,1),
unitid,
CONVERT(varchar(50), YEAR(GETDATE())) AS nd,
type, buyyear, factory, address, tze, tzly, re
into
#T
FROM
cpy
WHERE
nd = YEAR(GETDATE()) - 1
insert into cpy
(bh, unitid, nd, type, buyyear, factory, address, tze, tzly, re)
select
bh = (select max(bh) from cpy where nd = YEAR(GETDATE())-1)+bh,
unitid, nd, type, buyyear, factory, address, tze, tzly, re
from
#T
order by
bhdrop table #T
(bh, unitid, nd, type, buyyear, factory, address, tze, tzly, re)
SELECT
CONVERT(varchar(50),MAX(bh)+1) AS bh,
unitid,
CONVERT(varchar(50), YEAR(GETDATE())) AS nd,
type, buyyear, factory, address, tze, tzly, re
FROM
cpy
WHERE
nd = YEAR(GETDATE()) - 1
group by
unitid,type, buyyear, factory, address, tze, tzly, re
select @bh=max(cast(bh as int))+1 from cpyINSERT INTO cpy(bh, unitid, nd, type, buyyear, factory,
address, tze, tzly, re)
SELECT cast(@bh as varchar(50), unitid,
CONVERT(varchar(50),YEAR(GETDATE())) ,
type, buyyear, factory,address, tze, tzly, re
FROM cpy
WHERE (nd = YEAR(GETDATE()) - 1) Update cpy set bh=cast(@bh-1 as varchar(50)),@bh=@bh+1
where cast(bn as int)>=@bh