27,579
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([名称] nvarchar(36),[规格] nvarchar(33),[名称码] nvarchar(26),[规格码] nvarchar(23))
Insert #1
select N'硫酸阿米卡星注射液',N'2ml:0.2g*10支',N'005004',N'001' union all
select N'脂肪乳注射液',N'250ml',N'007007',N'001' union all
select N'盐酸甲氧氯普胺注射液',N'1ml:10mg*10支',N'011012',N'001' union all
select N'葡萄糖酸钙注射液',N'10ml:1g*5支',N'013015',N'001' union all
select N'0.9%氯化钠注射液',N'500ml(玻璃瓶)',N'013017',N'001' union all
select N'0.9%氯化钠注射液',N'100ml:0.9g(塑)',N'013017',N'002' union all
select N'0.9%氯化钠注射液',N'250ml',N'013017',N'003' union all
select N'0.9%氯化钠注射液',N'500ml(玻璃瓶)',N'013017',N'001' union all
select N'0.9%氯化钠注射液',N'100ml:0.9g(塑)',N'013017',N'002' union all
select N'0.9%氯化钠注射液',N'10ml',N'013017',N'003' union all
select N'硝酸甘油注射液',N'1ml:5mg*10支',N'014021',N'001' union all
select N'硝酸异山梨酯片',N'5mg*100片',N'014020',N'001' union all
select N'盐酸曲美他嗪片',N'20mg*30片',N'014026',N'001' union all
select N'单唾液酸四己糖神经节苷脂钠注射液',N'2ml:20mg',N'015027',N'001' union all
select N'注射用还原型谷胱甘肽',N'0.6g*6支',N'016034',N'001' union all
select N'注射用还原型谷胱甘肽',N'0.6g*10支',N'016034',N'002'
GO
;WITH CTET
AS
(Select *,ROW_NUMBER()OVER( ORDER BY RAND()) AS RN FROM #1)
,CTET2
AS
(
SELECT [名称],[规格],RN FROM CTET AS t WHERE NOT EXISTS(SELECT 1 FROM CTET WHERE 名称=t.名称 AND [规格]=t.[规格] AND RN<t.RN)
)
SELECT t1.*,生成编码=RIGHT(1000+DENSE_RANK()OVER(PARTITION BY T2.名称 ORDER BY t2.RN),3) FROM CTET AS t1 INNER JOIN CTET2 AS t2 ON t1.名称=t2.名称 AND t1.规格=t2.规格 ORDER BY t1.RN