create database dyeinguse database dyeing create table dye
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
)insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797')insert into dye values(1006,'000798')
insert into dye values(1007,'000798'')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')
想要的结果
编号 单号 条码
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2
怎么得到条码?
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
)insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797')insert into dye values(1006,'000798')
insert into dye values(1007,'000798'')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')
想要的结果
编号 单号 条码
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2
怎么得到条码?
from(
select *,rn=row_number()over(partition by lcddis order by lindtr ) from dye )t
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800') goselect a.*,tiaoma=lcddis+'-'+ltrim((select count(1) from dye where lcddis=a.lcddis and lindtr<=a.lindtr)) from dye a/*
lindtr lcddis tiaoma
----------- ------ -------------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2(所影响的行数为 9 行)*/
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800') select lindtr,lcddis,lcddis+'-'+rtrim((select COUNT(*) from dye where lcddis=t.lcddis and t.lindtr>=lindtr))
from dye t/*
lindtr lcddis
----------- ------ -------------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2
*/
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')select
lindtr as 编号,
lcddis as 单号,
lcddis+'-'+ltrim(rn) as 条码
from
(select *,rn=row_number()over(partition by lcddis order by lindtr ) from dye )tdrop table dye
/*编号 单号 条码
----------- ------ -------------------------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2(9 行受影响)*/
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')
--2000的方法
select lindtr,lcddis,条码=lcddis+'-'+rtrim((select COUNT(*) from dye where lcddis=t.lcddis and t.lindtr>=lindtr))
from dye t
--2005
select lindtr,lcddis,条码=lcddis+'-'+ltrim(id)
from(
select *,id=row_number()over(partition by lcddis order by lindtr ) from dye )t/*
lindtr lcddis 条码
----------- ------ -------------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2
*/
from (
select *,(select count(*) from dye where lindtr<=t.lindtr) as id
from dye as t
) tb
create table dye
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800') select
lindtr as 编号 ,
lcddis as 单号 ,
lcddis+'-'+rtrim((select COUNT(*) from dye where lcddis=t.lcddis and t.lindtr>=lindtr)) as 条码
from
dye t
/*编号 单号 条码
----------- ------ -------------------------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2(9 行受影响)*/
if object_id('dye') is not null drop table dye
go
create table dye
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')
select lindtr as 编号,lcddis as 单号,条码=lcddis+'-'+cast((select count(1)+1 from dye where lcddis=t.lcddis and lindtr<t.lindtr)as varchar(10)) from dye t 编号 单号 条码
----------- ------ -----------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2(9 行受影响)
if object_id('dye') is not null drop table dye
go
create table dye
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797')
insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800')
select lindtr as 编号,lcddis as 单号,
条码=lcddis+'-'+cast((select count(1) from dye where lcddis=t.lcddis and lindtr<=t.lindtr)as varchar(10))
from dye t 编号 单号 条码
----------- ------ -----------------
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2(9 行受影响)
(
lindtr int not null PRIMARY key,
lcddis varchar(6)
) insert into dye values(1001,'000797')
insert into dye values(1002,'000797')
insert into dye values(1003,'000797')
insert into dye values(1004,'000797')
insert into dye values(1005,'000797') insert into dye values(1006,'000798')
insert into dye values(1007,'000798')
insert into dye values(1008,'000800')
insert into dye values(1009,'000800') --2005-2008
select *,条码=lcddis+'-'+CAST(rank() over(partition by lcddis order by lindtr ASC) AS VARCHAR)
from dye
--2005-2008-2
select *,条码=lcddis+'-'+CAST(ROW_NUMBER() over(partition by lcddis order by lindtr ASC) AS VARCHAR)
from dye
--2000
select lindtr,lcddis,lcddis+'-'+ltrim(rtrim((select COUNT(1) from dye where lcddis=t.lcddis and t.lindtr>=lindtr)))
from dye t
/*
lindtr lcddis 条码
1001 000797 000797-1
1002 000797 000797-2
1003 000797 000797-3
1004 000797 000797-4
1005 000797 000797-5
1006 000798 000798-1
1007 000798 000798-2
1008 000800 000800-1
1009 000800 000800-2
*/