如何在主键中自动生成ID，如TS-2018-1，TS-2018-2等格式 [英] How do I autogenerate ID which in primary key like format of like TS-2018-1, TS-2018-2

问题描述

我想自动生成如果ticket_Code id如TS-2018-1

TS-2018-2

TS-2018-3



我尝试了什么：



CREATE TABLE [dbo]。 [票证]（

[Ticket_Code] INT IDENTITY（1,1）NOT NULL，

CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED（[Ticket_Code] ASC）

）;

I want to auto generate if ticket_Code id like TS-2018-1
TS-2018-2
TS-2018-3

What I have tried:

CREATE TABLE [dbo].[Ticket] (
[Ticket_Code] INT IDENTITY (1, 1) NOT NULL,
CONSTRAINT [PK_Ticket] PRIMARY KEY CLUSTERED ([Ticket_Code] ASC)
);

推荐答案

这是正确的方法是sql，并执行查询。

[S.No.] INT IDENTITY（1,1）NOT NULL，

[Ticket_Code] AS（'TS - '+ right（' - 2018 - '+ CONVERT（[varchar]（10），[S.No。] ，（0）），（6）））PERSISTED，



工作!!!!!

This is the right way is sql , And Query Executed.
[S.No.] INT IDENTITY (1, 1) NOT NULL,
[Ticket_Code] AS ('TS-'+right('-2018-'+CONVERT([varchar](10),[S.No.],(0)),(6))) PERSISTED,

Worked !!!!!

正如JörgenAndersson所说指出，您可能希望将硬编码的2018替换为从某些东西中提取的一年。它看起来好像你可能想要开始每年的重新编号。



这样的东西有效（但是假设没有记录会被删除）..

我为演示创建并填充了临时表：

As Jörgen Andersson has pointed out, you probably want to replace the hard-coded '2018' with a year extracted from something. It also looks as if you might want to start the renumbering for each year.

Something like this works (BUT does assume that no records will be deleted)..
I created and populated a temp table for the demo thus:

create table #ticket 
(
	[No] int identity(1,1),
	[year] int
)
insert into #ticket ([year]) values
(2017),(2017),(2018),(2018),(2018),(2019),(2019)

-- NB - and some more
insert into #ticket ([year]) values
(2017),(2017),(2018),(2018),(2018),(2019),(2019)

请注意年 s如果您仅通过 [No] 列进行订购，则不会出现在正确的订单中。



查询像这样：

Note that the years do not appear in the "correct" order if you order just by the [No] column.

A query like this:

select [No], [Year], 'TS-' + cast([year] as char(4)) + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY [year] ORDER BY [No]) as varchar)
from #ticket order by [year], [No]

给出我将这些结果：

gave me these results:

1	2017	TS-2017-1
2	2017	TS-2017-2
8	2017	TS-2017-3
9	2017	TS-2017-4
3	2018	TS-2018-1
4	2018	TS-2018-2
5	2018	TS-2018-3
10	2018	TS-2018-4
11	2018	TS-2018-5
12	2018	TS-2018-6
6	2019	TS-2019-1
7	2019	TS-2019-2
13	2019	TS-2019-3
14	2019	TS-2019-4

或稍微好一些（在我看来）

or the slightly nicer (in my opinion)

;with c as ( 
   select [No], [Year], 
   ROW_NUMBER() OVER (PARTITION BY [year] ORDER BY [No]) as yno from #ticket
)
select [No], [Year], Format([Year], 'TS-####') + Format(yno, '-####') from c

但正如我之前所说，经常这种操作（格式化文本和数字）最好留给UI层

But as I said earlier, often this sort of manipulation (formatting text and numbers) is best left to the UI layer

这篇关于如何在主键中自动生成ID，如TS-2018-1，TS-2018-2等格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！