如下表
SQL code
[code]=SQLCREATE TABLE [dbo].[ysc] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [numeric](18, 0) NOT NULL ,
[数量] [numeric](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[审核时间] [datetime] NULL ,
[业务1] [bit] NOT NULL ,
[业务2] [bit] NOT NULL ,
[业务3] [bit] NOT NULL ,
[业务4] [bit] NOT NULL ,
[业务5] [bit] NOT NULL ,
[单价] [float] NULL ,
[金额] [numeric](18, 2) NULL ,
[毛利] [numeric](18, 2) NULL ,
[退货数量] [numeric](18, 4) NULL ,
[退货人] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[退货时间] [datetime] NULL ,
[减差价] [float] NULL ,
[减差额] [numeric](18, 2) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[ysc] ADD
CONSTRAINT [DF_ysc_库房数量] DEFAULT (0) FOR [库房数量],
CONSTRAINT [DF_ysc_数量] DEFAULT (0) FOR [数量],
CONSTRAINT [DF_ysc_业务1] DEFAULT (0) FOR [业务1],
CONSTRAINT [DF_ysc_业务2] DEFAULT (0) FOR [业务2],
CONSTRAINT [DF_ysc_业务3] DEFAULT (0) FOR [业务3],
CONSTRAINT [DF_ysc_业务4] DEFAULT (0) FOR [业务4],
CONSTRAINT [DF_ysc_业务5] DEFAULT (0) FOR [业务5],
CONSTRAINT [DF_ysc_退货数量] DEFAULT (0) FOR [退货数量],
CONSTRAINT [PK_ysc] PRIMARY KEY NONCLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
执代码如下,不知道为何不好用
SQL code
declare @i int,@b int,@px int,@n datetime,@nn datetime
set @I = 0
set @px = 0
update a set 序号 = @i
,票号 = left(票号,12)+ right(10000+ltrim(@px),4)
,@I = case when 保管员 = @b then @i else 0 end + 1
,@px = @px + case when 保管员 = @b then 0 else 1 end
,@b = a.保管员 from y a where substring(票号,5,8)='20100501'
[/code]
数据格式是这样的
票号 序号 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020002 1 2002
a01c201005020003 1 2001
请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
结果票号 序号 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
SQL code
[code]=SQLCREATE TABLE [dbo].[ysc] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [numeric](18, 0) NOT NULL ,
[数量] [numeric](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[审核时间] [datetime] NULL ,
[业务1] [bit] NOT NULL ,
[业务2] [bit] NOT NULL ,
[业务3] [bit] NOT NULL ,
[业务4] [bit] NOT NULL ,
[业务5] [bit] NOT NULL ,
[单价] [float] NULL ,
[金额] [numeric](18, 2) NULL ,
[毛利] [numeric](18, 2) NULL ,
[退货数量] [numeric](18, 4) NULL ,
[退货人] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[退货时间] [datetime] NULL ,
[减差价] [float] NULL ,
[减差额] [numeric](18, 2) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[ysc] ADD
CONSTRAINT [DF_ysc_库房数量] DEFAULT (0) FOR [库房数量],
CONSTRAINT [DF_ysc_数量] DEFAULT (0) FOR [数量],
CONSTRAINT [DF_ysc_业务1] DEFAULT (0) FOR [业务1],
CONSTRAINT [DF_ysc_业务2] DEFAULT (0) FOR [业务2],
CONSTRAINT [DF_ysc_业务3] DEFAULT (0) FOR [业务3],
CONSTRAINT [DF_ysc_业务4] DEFAULT (0) FOR [业务4],
CONSTRAINT [DF_ysc_业务5] DEFAULT (0) FOR [业务5],
CONSTRAINT [DF_ysc_退货数量] DEFAULT (0) FOR [退货数量],
CONSTRAINT [PK_ysc] PRIMARY KEY NONCLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GOSET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
执代码如下,不知道为何不好用
SQL code
declare @i int,@b int,@px int,@n datetime,@nn datetime
set @I = 0
set @px = 0
update a set 序号 = @i
,票号 = left(票号,12)+ right(10000+ltrim(@px),4)
,@I = case when 保管员 = @b then @i else 0 end + 1
,@px = @px + case when 保管员 = @b then 0 else 1 end
,@b = a.保管员 from y a where substring(票号,5,8)='20100501'
[/code]
数据格式是这样的
票号 序号 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020002 1 2002
a01c201005020003 1 2001
请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
结果票号 序号 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
解决方案 »
- union排序问题
- sql语句在查旬分析器里可以执行,但写成存储过程就报错
- text和ntext的区别
- 密文有的用md5能解,有的不能解?算法不是一样的吗,为什么不能解呢?
- mssql 架构对比 权限 应该如何授予?
- 为什么这触发器调用存储过程有错。急!!!!!
- 用access导入数据库SQL2005报错,怎么解决
- 关于存储过程参数的一个问题
- 急急救命。-----在线等待。。为什么安装了SOAP Toolkit 3.0后再装sqlxml3.0(sp2)还是提示要安装SOAP Toolkit2.0 sp2呢?
- oracle中如何做个类似于自动编号的字段
- 如何在触发器使用insert语句
- 大虾们帮忙看哈这个代码怎么写
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([票号] [nvarchar](20),[序号] [int],[保管员] [int])
INSERT INTO [tb]
SELECT 'a01c201005020001','1','2001' UNION ALL
SELECT 'a01c201005020001','2','2001' UNION ALL
SELECT 'a01c201005020002','1','2002' UNION ALL
SELECT 'a01c201005020003','1','2001'SELECT * FROM [tb]-->SQL查询如下:
--查询:
select min([票号])over(partition by [保管员]) as 票号,
row_number()over(partition by [保管员] order by 票号) as 序号,
保管员
from tb--更新:
;with t as
(
select *,min([票号])over(partition by [保管员]) as 票号1,
row_number()over(partition by [保管员] order by 票号) as 序号1
from tb
)
update t set 序号=序号1,票号=票号1select * from tb
最好是根据下代码更改declare @i int,@b int,@px int,@n datetime,@nn datetime
set @I = 0
set @px = 0
update a set 序号 = @i
,票号 = left(票号,12)+ right(10000+ltrim(@px),4)
,@I = case when 保管员 = @b then @i else 0 end + 1
,@px = @px + case when 保管员 = @b then 0 else 1 end
,@b = a.保管员 from y a where substring(票号,5,8)='20100501'
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([票号] [nvarchar](20),[序号] [int],[保管员] [int])
INSERT INTO [tb]
SELECT 'a01c201005020001','1','2001' UNION ALL
SELECT 'a01c201005020001','2','2001' UNION ALL
SELECT 'a01c201005020002','1','2002' UNION ALL
SELECT 'a01c201005020003','1','2001'SELECT * FROM [tb]-->SQL2K查询如下:
--查询:
select (select min([票号]) from tb where [保管员]=t.[保管员]) as [票号],
(select count(1) from tb where [保管员]=t.[保管员] and ([票号]<t.[票号] or [票号]=t.[票号] and [序号]<=t.[序号])) as 序号,
保管员
from tb t--更新:
update tb
set [票号]=[票号1],[序号]=[序号1]
from (
select *,(select min([票号]) from tb where [保管员]=t.[保管员]) as [票号1],
(select count(1) from tb where [保管员]=t.[保管员] and ([票号]<t.[票号] or [票号]=t.[票号] and [序号]<=t.[序号])) as 序号1
from tb t
) a
where tb.保管员=a.保管员 and tb.票号=a.票号 and tb.序号=a.序号
select * from tb
declare @i int,@b int,@px int,@n datetime,@nn datetime
set @I = 0
set @px = 0
update a set 序号 = @i
,票号 = left(票号,12)+ right(10000+ltrim(@px),4)
,@I = case when 保管员 = @b then @i else 0 end + 1
,@px = @px + case when 保管员 = @b then 0 else 1 end
,@b = a.保管员 from #t a where substring(票号,5,8)='20100502'select * from #t
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GOtruncate table ysc--数据
insert ysc (票号, 序号 ,保管员)
select 'a01c201005020001', 1 ,2001 union all
select 'a01c201005020001', 1 ,2001 union all
select 'a01c201005020002', 1 ,2002 union all
select 'a01c201005020003', 1 ,2001select * from ysc
/*
票号 序号 保管员
---------------- ----------- --------
a01c201005020001 1 2001
a01c201005020001 1 2001
a01c201005020002 1 2002
a01c201005020003 1 2001 (所影响的行数为 4 行)*/
--如此强制使表update时做排序
declare @i int,@b int,@px int,@n datetime,@nn datetime
set @I = 0
set @px = 0
update a set a.序号 = @i
,a.票号 = left(a.票号,12)+ right(10000+ltrim(@px),4)
,@I = case when a.保管员 = @b then @i else 0 end + 1
,@px = @px + (case when a.保管员 = @b then 0 else 1 end)
,@b = a.保管员 from (select distinct 保管员 from ysc) t , ysc a
where substring(票号,5,8)='20100502'
and a.保管员=t.保管员
select * from ysc
/*
票号 序号 保管员
---------------- ----------- --------
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020002 1 2002
a01c201005020001 3 2001 (所影响的行数为 4 行)
*/