SQL给有相同列的数据添加一个递增的编号

最新推荐文章于 2024-10-14 13:48:13 发布

djk8888

最新推荐文章于 2024-10-14 13:48:13 发布

阅读量8.3k

点赞数 2

分类专栏： SQL代码文章标签： sql

本文链接：https://blog.csdn.net/djk8888/article/details/88999970

版权

SQL代码专栏收录该内容

31 篇文章 0 订阅

订阅专栏

参考于：https://zhidao.baidu.com/question/229402549.html

1、表中有自增列row：

select (select count(*) from table1 where [user]=t1.[user] and row>=t1.row) as id,[user] from table1 as t1

order by [user],id

2、表中没有自增列，但有其它列，其中有一列可以区分开每一行数据，假设为utime：

select (select count(*) from table1 where [user]=t1.[user] and utime>=t1.utime) as id,[user] from table1 as t1

order by [user],id

实际运用：

SELECT AP.ArticlePurchaseID 物品id,AP.ArticleTypeID 类别id,[ArticleTypeName] 类别名称,
AT.[ArticleTypeName]+'-'+CAST((select count(*) from [t_ArticlePurchase] where ArticleTypeID=AP.ArticleTypeID and ArticlePurchaseID<=AP.ArticlePurchaseID) AS varchar(MAX)) 物品名称
FROM t_ArticlePurchase AP 
LEFT JOIN t_ArticleType AT ON AT.ArticleTypeID = AP.ArticleTypeID
WHERE AP.IsValid=1
ORDER BY AP.ArticleTypeID ASC,AP.ArticlePurchaseID asc

主要是这句：

ISNULL(ArticleName,[ArticleTypeName]+'-'+CAST((select count(*) from [t_ArticlePurchase] where ArticleTypeID=AP.ArticleTypeID and ArticlePurchaseID<=AP.ArticlePurchaseID) AS varchar(MAX)))物品名称,

实际效果：

说明:

根据类别的名称，给物品命名，格式是：类别名称+'-'+自增的编号

还可以用来修复数据：

UPDATE  AP
SET [ArticleName]=AT.[ArticleTypeName]+'-'+CAST((select count(*) from [t_ArticlePurchase] where ArticleTypeID=AP.ArticleTypeID and ArticlePurchaseID<=AP.ArticlePurchaseID) AS varchar(MAX))
FROM  [t_ArticlePurchase] AP 
LEFT JOIN t_ArticleType AT
ON AT.ArticleTypeID = AP.ArticleTypeID
WHERE AP.IsValid=1;

随手记：

SELECT b.InventoryName, b.InventoryName+CAST((select count(*) from [t_FixedAssetsItems] where CID=a.CID and FID<=a.FID) AS varchar(MAX))
FROM [dbo].[t_FixedAssetsItems] a
LEFT JOIN t_Inventory AS b ON b.InventoryID=a.CID
ORDER BY a.FID ASC,a.CID asc

UPDATE a SET a.FName= b.InventoryName+CAST((select count(*) from [t_FixedAssetsItems] where CID=a.CID and FID<=a.FID) AS varchar(50))
FROM [T_FixedAssetsItems] a
LEFT JOIN t_Inventory AS b ON b.InventoryID=a.CID;  

UPDATE a SET a.InventoryName=b.InventoryName+CAST((select count(*) from [T_StorageInfo] where InventoryID=a.InventoryID and FID<=a.FID) AS varchar(50))
FROM [T_StorageInfo] a
LEFT JOIN t_Inventory AS b ON b.InventoryID=a.InventoryID;