SQLSERVER 查询字符串截取, 查询结果合并

[道玄希言]

求一高效点的字符串截取查询函数

表:
;WITH tbl(id, Code, Name) AS
(
SELECT 1, '00', 'AA' UNION ALL
SELECT 2, '0000', 'BB' UNION ALL
SELECT 3, '000000', 'CC' UNION ALL
SELECT 4, '00000000', 'DD' UNION ALL
SELECT 5, '0000000000', 'EE' UNION ALL
SELECT 6, '000000000000', 'FF' UNION ALL
SELECT 7, '00000000000000', 'GG' UNION ALL
SELECT 8, '0000000000000000', 'HH'
)

输入参数: id 2, 6
输出结果: BB-CC-DD-EE-FF

输入参数: id 4, 8
输出结果: DD-EE-FF-GG-HH

输入参数: id 6, 6
输出结果: FF

[二月十六]

DECLARE @str NVARCHAR(max)='2,6'
;WITH tbl(id, Code, Name) AS
(
    SELECT 1, '00',       'AA'     UNION ALL 
    SELECT 2, '0000',      'BB'     UNION ALL 
    SELECT 3, '000000',      'CC'     UNION ALL 
    SELECT 4, '00000000',      'DD'     UNION ALL 
    SELECT 5, '0000000000',      'EE'     UNION ALL 
    SELECT 6, '000000000000',      'FF'     UNION ALL 
    SELECT 7, '00000000000000',     'GG'     UNION ALL 
    SELECT 8, '0000000000000000',    'HH'
)
SELECT STUFF((
SELECT  '-'+tbl.Name
FROM    tbl
WHERE   id BETWEEN SUBSTRING(@str, 0, CHARINDEX(',', @str))
           AND     SUBSTRING(@str, CHARINDEX(',', @str)+1,
                             LEN(@str) - CHARINDEX(',', @str)) FOR XML PATH('')),1,1,'')

[SWAT特训营]

尽量不要在数据库里面实现这些功能，高并发会导致CPU超高，而数据库扩展非常困难，建议在程序端去实现这些功能

[道玄希言]

引用 2 楼 SWAT_TXY 的回复:

尽量不要在数据库里面实现这些功能，高并发会导致CPU超高，而数据库扩展非常困难，建议在程序端去实现这些功能

因为在数据库中, 父级结构的设计, 是通过 Code 来构造的, 无法通过递归来查找子节点对应的父级节点, 如果到程序中实现, 则需要多次查询数据库才能实现 我需要首先根据传入的设备号查到对应的子级节点, 然后需要根据传入的用户ID,查询到对应的顶级父节点, 然后再在程序中拆解字符串, 然后找出对应的各个父级的名称. 数据库总记录数据在10w以内, 子级节点不会超过 16级, 我目前也没测试过到底哪种方式更加合理.

[道玄希言]

引用 1 楼 sinat_28984567 的回复:

DECLARE @str NVARCHAR(max)='2,6'
;WITH tbl(id, Code, Name) AS
(
    SELECT 1, '00',       'AA'     UNION ALL 
    SELECT 2, '0000',      'BB'     UNION ALL 
    SELECT 3, '000000',      'CC'     UNION ALL 
    SELECT 4, '00000000',      'DD'     UNION ALL 
    SELECT 5, '0000000000',      'EE'     UNION ALL 
    SELECT 6, '000000000000',      'FF'     UNION ALL 
    SELECT 7, '00000000000000',     'GG'     UNION ALL 
    SELECT 8, '0000000000000000',    'HH'
)
SELECT STUFF((
SELECT  '-'+tbl.Name
FROM    tbl
WHERE   id BETWEEN SUBSTRING(@str, 0, CHARINDEX(',', @str))
           AND     SUBSTRING(@str, CHARINDEX(',', @str)+1,
                             LEN(@str) - CHARINDEX(',', @str)) FOR XML PATH('')),1,1,'')

不好意思, 版主, 您这里刚好利用了这个序号, 在实际的数据库中, 序号是没有规律的 唯一有规律的只有 Code 字段, 父级 到下一子级会 增加 两位, 判断是否为父级时, 截取 Code 长度 - 2 位, 对应的就是其父级.

[道玄希言]

不好意思, 是我没描述清楚 Code 的规律

[zjcxc]

DECLARE @code varchar(100), @re varchar(500);
DECLARE @id1 int, @id2 int;
SELECT @id1=2, @id2=8;

;WITH tbl(id, Code, Name) AS
(
    SELECT 1, '00',       'AA'     UNION ALL 
    SELECT 2, '0000',      'BB'     UNION ALL 
    SELECT 3, '000000',      'CC'     UNION ALL 
    SELECT 4, '00000000',      'DD'     UNION ALL 
    SELECT 5, '0000000000',      'EE'     UNION ALL 
    SELECT 6, '000000000000',      'FF'     UNION ALL 
    SELECT 7, '00000000000000',     'GG'     UNION ALL 
    SELECT 8, '0000000000000000',    'HH'
)
SELECT
	@re = CASE
			WHEN id = @id2 THEN Name
			WHEN code = @code THEN name + '-' + @re
			ELSE @re
		END,
	@code = CASE
		WHEN id = @id1 THEN NULL
		WHEN id = @id2 THEN LEFT(code, LEN(code) -2)
		ELSE LEFT(code, LEN(code) -2)
	END		
FROM tbl 
ORDER BY code DESC
;
SELECT @re

[zjcxc]

DECLARE @re varchar(100);
SET @re = '';

WITH tbl(id, Code, Name) AS
(
    SELECT 1, '00',       'AA'     UNION ALL 
    SELECT 2, '0000',      'BB'     UNION ALL 
    SELECT 3, '000000',      'CC'     UNION ALL 
    SELECT 4, '00000000',      'DD'     UNION ALL 
    SELECT 5, '0000000000',      'EE'     UNION ALL 
    SELECT 6, '000000000000',      'FF'     UNION ALL 
    SELECT 7, '00000000000000',     'GG'     UNION ALL 
    SELECT 8, '0000000000000000',    'HH'
),
CODE AS(
	SELECT Code = A.code2, LEN(A.code2) as l2, LEN(a.code1) as l1
	FROM(SELECT
		(SELECT code FROM tbl WHERE id = 2) as code1,
		(SELECT code FROM tbl WHERE id = 6) as code2
	)A
),
CODES AS(
	SELECT * FROM CODE
	UNION ALL
	SELECT LEFT(Code, l2 - 2), l2 - 2, l1 FROM CODES WHERE l1 < l2
)
SELECT @re = @re + '-' + DATA.Name
FROM CODES, tbl DATA
WHERE CODES.Code = DATA.Code
ORDER BY DATA.Code
;
SELECT STUFF(@re, 1, 1, '');

数据量大的话，这种性能更好，前面的始终遍历表中的所有数据，而这个只查满足条件的数据（但这个有3次查询，前面那个遍历只有一次查询，所以数据量少时个没优势）

[道玄希言]

引用 7 楼 zjcxc 的回复:

DECLARE @re varchar(100);
SET @re = '';

WITH tbl(id, Code, Name) AS
(
    SELECT 1, '00',       'AA'     UNION ALL 
    SELECT 2, '0000',      'BB'     UNION ALL 
    SELECT 3, '000000',      'CC'     UNION ALL 
    SELECT 4, '00000000',      'DD'     UNION ALL 
    SELECT 5, '0000000000',      'EE'     UNION ALL 
    SELECT 6, '000000000000',      'FF'     UNION ALL 
    SELECT 7, '00000000000000',     'GG'     UNION ALL 
    SELECT 8, '0000000000000000',    'HH'
),
CODE AS(
	SELECT Code = A.code2, LEN(A.code2) as l2, LEN(a.code1) as l1
	FROM(SELECT
		(SELECT code FROM tbl WHERE id = 2) as code1,
		(SELECT code FROM tbl WHERE id = 6) as code2
	)A
),
CODES AS(
	SELECT * FROM CODE
	UNION ALL
	SELECT LEFT(Code, l2 - 2), l2 - 2, l1 FROM CODES WHERE l1 < l2
)
SELECT @re = @re + '-' + DATA.Name
FROM CODES, tbl DATA
WHERE CODES.Code = DATA.Code
ORDER BY DATA.Code
;
SELECT STUFF(@re, 1, 1, '');

数据量大的话，这种性能更好，前面的始终遍历表中的所有数据，而这个只查满足条件的数据（但这个有3次查询，前面那个遍历只有一次查询，所以数据量少时个没优势）

谢谢. 非常感谢. 应该可以解决我的问题, 结贴了.

[zx6091024]

我用#CSDN#这个app发现了有技术含量的博客，小伙伴们求同去《求指点sql server count group by函数》, 一起来围观吧 https://bbs.csdn.net/topics/392495126

[qq_43409914]

我需要扫号程序可以帮我写不