大家好!
如何用SQL语句实现标题功能.见下图:
传入:000001001001001 传出101100001 成品传感器SSS
即当MA001='***'时，自动按3位进行自动生成序号功能。
传入:000001001001 传出101100 成品传感器SSS
谢谢！
------解决方案--------------------
SELECT [id]
,[name]
,[nodeid]
,[fnodeid]
FROM [mydb].[dbo].[TEST_TABLE]
--内容
id name nodeid fnodeid
1 成品 000001 000
2 半成品 000002 000
3 原材料 000003 000
01 传感器 000001001 000001
100 SSS 000001001001 000001001
--查询
with temp_table(id, name, nodeid, fnodeid, levell)
as
(
select CAST(ID AS VARCHAR) AS ID, CAST (NAME AS VARCHAR) AS NAME, nodeid, fnodeid, 1 AS LEVELL
from test_table WHERE NODEID = '000001001001'
union all
select CAST(T.ID + VT.ID AS VARCHAR) AS ID, cast (T.NAME + VT.NAME as varchar) AS NAME, T.NODEID, T.FNODEID, VT.LEVELL + 1 AS LEVELL
FROM TEST_TABLE T, TEMP_TABLE VT
WHERE T.NODEID = VT.FNODEID
)
SELECT TOP 1 ID, name FROM TEMP_TABLE ORDER BY LEVELL DESC
--内容
ID name
101100 成品传感器SSS