如下表
CREATE 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数据格式是这样的
票号 序号 产品编号 金额 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020002 1 2002
a01c201005020003 1 2001
请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
结果票号 序号 产品编号 金额 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002原代码如下,不知道为何不好用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'
CREATE 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数据格式是这样的
票号 序号 产品编号 金额 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020002 1 2002
a01c201005020003 1 2001
请问如何这个保管员整理成唯一的一张票号,序号自动累计,其它保管员用到的票号也顺序增加
结果票号 序号 产品编号 金额 保管员
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002原代码如下,不知道为何不好用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'
解决方案 »
- 如何更改exe文件的标题
- 请各位帮我指点关于inner join,full outer join,left join,right jion的具体实现含义.
- openrowset 打开其它计算机上的共享文件时说找不到文件,怎么会这样?
- 注册表
- 大哥们帮我看一下。。。在线等你们回答!~~~~
- 给大家散分,顺带帮我解决一个游标中截取字符串的问题。
- [请教] 消息 8152 这个错误应该怎么解决啊?附原问题
- 请问OCI更Pro*C有什么关系啊?
- 在ORACLE中,什么是服务名?它有什么用?
- 郁闷 在将SQL SERVER培植为使用Microsoft Indexing Server Service总有问题
- 菜鸟跪求一个简单的问题!"一个字段的值等于另外几个字段相加减"
- 请教数据存放格式
insert ysc (票号, 序号 ,保管员)
select 'a01c201005020001', 1 ,2001 union all
select 'a01c201005020001', 1 ,2001 union all
select 'a01c201005020002', 1 ,2002 union all
select 'a01c201005020003', 1 ,2001--如此强制使表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 left join ysc a on a.保管员=t.保管员
where substring(票号,5,8)='20100502'
/*
票号 序号 小号 产品编号 库房 批号 效期 标识 库房数量 数量 件数 保管员 审核时间 业务1 业务2 业务3 业务4 业务5 单价 金额 毛利 退货数量 退货人 退货时间 减差价 减差额
---------------- ----------- ----------- ------------- ---------- ---------------- ------------------------------------------------------ ---------------- -------------------- -------------------- ----------------------------------------------------- -------- ------------------------------------------------------ ---- ---- ---- ---- ---- ----------------------------------------------------- -------------------- -------------------- -------------------- ---------- ------------------------------------------------------ ----------------------------------------------------- --------------------
a01c201005020001 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
a01c201005020001 2 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
a01c201005020002 1 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2002 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
a01c201005020001 3 NULL NULL NULL NULL NULL NULL NULL NULL NULL 2001 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL(所影响的行数为 4 行)*/
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002请问要怎么改?加order by ?
(
id int,
rn int
)
insert #k select
1,1 union all select
1,2 union all select
1,1 union all select
2,1 union all select
2,3 union all select
2,7
go
declare @rn int,@id int
set @rn=1 update #k
set @rn=case when ID=@id then @rn+1 else 1 end,
@id=id,
rn=@rn
select * from #kdrop table #k
/*
id rn
----------- -----------
1 1
1 2
1 3
2 1
2 2
2 3*/给你个样板 ~
go
create table [a]([票号] varchar(16),[序号] int,[保管员] int)
insert [a]
select 'a01c201005020001',1,2001 union all
select 'a01c201005020001',2,2001 union all
select 'a01c201005020002',1,2002 union all
select 'a01c201005020003',1,2001
go-->查询
select
票号=left(票号,12)+
right('0000'+ltrim((select count(distinct 保管员) from a where 保管员<t.保管员)+1),4),
序号=row_number() over(partition by 保管员 order by getdate()),
保管员
from a t/**
票号 序号 保管员
------------------------ -------------------- -----------
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002(4 行受影响)
**/
'row_number' 不是可以识别的 函数名。
go
create table [a]([票号] varchar(16),[序号] int,[保管员] int)
insert [a]
select 'a01c201005020001',1,2001 union all
select 'a01c201005020001',2,2001 union all
select 'a01c201005020002',1,2002 union all
select 'a01c201005020003',1,2001
goalter table a add tid int identity(1,1)
goselect
票号=left(票号,12)+
right('0000'+ltrim((select count(distinct 保管员) from a where 保管员<t.保管员)+1),4),
序号=(select count(1)+1 from a where 保管员=t.保管员 and tid<t.tid),
保管员
from a t
order by 1,2
go
/**
票号 序号 保管员
------------------------ ----------- -----------
a01c201005020001 1 2001
a01c201005020001 2 2001
a01c201005020001 3 2001
a01c201005020002 1 2002(4 行受影响)
**/alter table a drop column tid;
go
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'--更新:
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
CREATE TABLE [dbo].[ysc] (
[票号] [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 行)
*/