How to create a idendity for each id

2018-07-04 08:30:13

Is it possible to create a composite key in sql 2000

code    id
abc      1
abc      2
abc      3
def      1
def      2
ghi      1

where the id restarts the count at each change of code. I need the numbering to be exactly like that either by creating a table or other SELECT statement trickery.

how to do this in sql server 2000

Need Query Help

Here is one way to retrieve this data at runtime, without having to actually store it in the table, which is incredibly cumbersome to try and maintain. I'm using a #temp table here but you can pretend #a is your permanent table. As is, this will support up to 256 duplicates. If you need more, it can be adjusted.

CREATE TABLE #a(code VARCHAR(32));

INSERT #a SELECT 'abc'
UNION ALL SELECT 'abc'
UNION ALL SELECT 'abc'
UNION ALL SELECT 'def'
UNION ALL SELECT 'def'
UNION ALL SELECT 'ghi';
GO

SELECT x.code, id = y.number FROM 
(
  SELECT code, maxid = COUNT(*) FROM #a GROUP BY code
) AS x
CROSS JOIN 
(
  SELECT DISTINCT number FROM master..spt_values
  WHERE number BETWEEN 1 AND 256
) AS y
WHERE x.maxid >= y.number;

DROP TABLE #a;

你可以试试这个

INSERT INTO TABLENAME (code, id) VALUES( 'code',
(Select ISNULL(MAX(id), 0) FROM TableName where code = 'code')+1)

链接地址: http://www.djcxy.com/p/95670.html

上一篇: 用多行更新链接表

下一篇: 如何为每个ID创建一个标识