没看明白bagid是什么意思
if object_id('bag') is not null
drop table bag
create table bag
(
clothid varchar(7),
number int,
colorlight varchar(1),
bagid int
)insert into bag
select '11-11-1' ,80 ,'A' ,null union all
select '11-11-2' ,70 ,'A' ,null union all
select '11-12' ,60 ,'A' ,null union all
select '15-13-1' ,45 ,'A' ,null union all
select '15-13-2' ,50 ,'A' ,null union all
select '12-01' ,70 ,'B' ,null union all
select '12-02-1' ,50 ,'B' ,null union all
select '12-02-2' ,90 ,'B' ,null union all
select '14-03 ' ,80 ,'B' ,null union all
select '13-01' ,50 ,'c' ,null union all
select '16-01' ,50 ,'c' ,nulldrop proc pr_bag
gocreate proc pr_bag
as
if object_id('#test') is not null
drop table #test
select *,identity(int,1,1) biaohao into #test from bag
select * from #test
go
exec pr_bag
if object_id('bag') is not null
drop table bag
create table bag
(
clothid varchar(7),
number int,
colorlight varchar(1),
bagid int
)insert into bag
select '11-11-1' ,80 ,'A' ,null union all
select '11-11-2' ,70 ,'A' ,null union all
select '11-12' ,60 ,'A' ,null union all
select '15-13-1' ,45 ,'A' ,null union all
select '15-13-2' ,50 ,'A' ,null union all
select '12-01' ,70 ,'B' ,null union all
select '12-02-1' ,50 ,'B' ,null union all
select '12-02-2' ,90 ,'B' ,null union all
select '14-03 ' ,80 ,'B' ,null union all
select '13-01' ,50 ,'c' ,null union all
select '16-01' ,50 ,'c' ,nulldrop proc pr_bag
gocreate proc pr_bag
as
if object_id('#test') is not null
drop table #test
select *,identity(int,1,1) biaohao into #test from bag
select * from #test
go
exec pr_bag
insert into tt select 'a' union all select 'b' union all select 'c' union all select 'd'
go
select * from tt
go
create proc d
@id int
as
declare @bh int
set @bh=@id
exec('select identity(int,'+@bh+',1) id,* into #t from tt select * from #t drop table #t')
goexec d 10
go
drop proc d
drop table tt
@num int,
@bagid int,
@bh int --添加
as--select identity(int, @bh ,1) id,* into #t from bag order by colorlight,clothid
exec('select identity(int,'+@bh+', 1) id,* into #t from bag order by colorlight,clothid')
declare @id int,@i int,@colorlight char(1)
set @id=@bagid-1
update #t set @id=case when @i+number<@num and @colorlight=colorlight then @id else @id+1 end
,@i=case when @i+number<@num and @colorlight=colorlight then @i+number else number end
,@colorlight=colorlight
,bagid=@id
update bag set bag.bagid=#t.bagid from #t
where bag.clothid=#t.clothid update bag set bag.bianhao=#t.id from #t
where bag.clothid=#t.clothid select * from #t
所影响的行数为 11 行)服务器: 消息 208,级别 16,状态 1,过程 p_qry,行 41
对象名 '#t' 无效。
Drop table bagIf Exists (Select * from sysobjects Where ID=OBJECT_ID('p_qry'))
Drop Proc p_qry--测试数据
create table bag
(
clothid varchar(7),
number int,
colorlight varchar(1),
--bagid int, --,改为,
bagid int,
--binhao varchar(4) --binhao改为bianhao,列名写错
bianhao varchar(4)
)
GO
insert into bag
select '11-11-1' ,80 ,'A' ,null,'' union all
select '11-11-2' ,70 ,'A' ,null,'' union all
select '11-12' ,60 ,'A' ,null,'' union all
select '15-13-1' ,45 ,'A' ,null,'' union all
select '15-13-2' ,50 ,'A' ,null,'' union all
select '12-01' ,70 ,'B' ,null,'' union all
select '12-02-1' ,50 ,'B' ,null,'' union all
select '12-02-2' ,90 ,'B' ,null,'' union all
select '14-03 ' ,80 ,'B' ,null,'' union all
select '13-01' ,50 ,'c' ,null,'' union all
select '16-01' ,50 ,'c' ,null,''GO
--存储过程如下:
--创建一个存储过程
CREATE Procedure p_qry
@num int,
@bagid int,
@bh int --添加
as
declare @id int,@i int,@colorlight char(1)
set @id=@bagid-1
If Exists (Select * from sysobjects Where ID=OBJECT_ID('bagTemp'))
Drop table bagTemp
exec('select identity(int,'+@bh+', 1) id,* into bagTemp from bag order by colorlight,clothid')
update bagTemp set @id=case when @i+number<@num and @colorlight=colorlight then @id else @id+1 end
,@i=case when @i+number<@num and @colorlight=colorlight then @i+number else number end
,@colorlight=colorlight
,bagid=@idupdate bag set bag.bagid=bagTemp.bagid from bagTemp
where bag.clothid=bagTemp.clothid --update bag set bag.bianhao=bagTemp.bh from bagTemp where bag.clothid=bagTemp.clothid --字段名改下,由bh改为id
update bag set bag.bianhao=bagTemp.id from bagTemp where bag.clothid=bagTemp.clothid
--select * from bagTemp
Drop table bagTempGOexec p_qry 200,1,1
Select * from bag
/*
clothid number colorlight bagid bianhao
11-11-1 80 A 1 1
11-11-2 70 A 1 2
11-12 60 A 2 3
15-13-1 45 A 2 4
15-13-2 50 A 2 5
12-01 70 B 3 6
12-02-1 50 B 3 7
12-02-2 90 B 4 8
14-03 80 B 4 9
13-01 50 c 5 10
16-01 50 c 5 11*/
IDENTITY(函数)
只用在带有 INTO table 子句的 SELECT 语句中,以将标识列插入到新表中。尽管类似,但是 IDENTITY 函数不是与 CREATE TABLE 和 ALTER TABLE 一起使用的 IDENTITY 属性。语法
IDENTITY ( data_type [ , seed , increment ] ) AS column_name参数
data_type标识列的数据类型。标识列的有效数据类型可以是任何整数数据类型分类的数据类型(bit 数据类型除外),也可以是 decimal 数据类型。seed要指派给表中第一行的值。给每一个后续行指派下一个标识值,该值等于上一个 IDENTITY 值加上 increment 值。如果既没有指定 seed,也没有指定 increment,那么它们都默认为 1。increment用来添加到 seed 值以获得表中连续行的增量。column_name将插入到新表中的列的名称。 返回类型
返回与 data_type 相同的类型。注释
因为该函数在表中创建一个列,所以必须用下列方式中的一种在选择列表中指定该列的名称:--(1)
SELECT IDENTITY(int, 1,1) AS ID_Num
INTO NewTable
FROM OldTable--(2)
SELECT ID_Num = IDENTITY(int, 1, 1)
INTO NewTable
FROM OldTable示例
下面的示例将来自 pubs 数据库中 employee 表的所有行都插入到名为 employees 的新表。使用 IDENTITY 函数在 employees 表中从 100 而不是 1 开始编标识号。USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'employees')
DROP TABLE employees
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'SELECT emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last,
IDENTITY(smallint, 100, 1) AS job_num,
job_lvl AS job_level,
pub_id,
hire_date
INTO employees
FROM employee
GO
USE pubs
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'