如何使用逗号分隔符获得与group concatinate不同的总和 [英] How to get sum of distinct from group concatinate using comma separator

问题描述

这是我的示例表，我的预期输出来自totalchar：

This is my sample table, and my expected output from totalchar:

+--------+--------------+-----------+
+---No---+-----letter---+-totalcha--+
+--------+--------------+-----------+
+---111--+--a,a,b,b,c,c-+-----3-----+  -> count distinct values only
+---222--+--e,e,f,g,h,i-+-----5-----+  -> count distinct values only
+--------+--------------+-----------+

我使用此查询：

Im using this query:

GROUP_CONCAT(distinct(letter) SEPARATOR ', ') as letter, sum(CHAR_LENGTH(letter) - CHAR_LENGTH(REPLACE((letter), ',', '')) + 1) as totalchar

我的输出是这些，这是错误的，任何人都可以帮忙吗？在此先感谢：

And my output is these, which is wrong, can any body can help? thanks in advance:

+--------+--------------+-----------+
+---No---+-----letter---+-totalcha--+
+--------+--------------+-----------+
+---111--+--a,a,b,b,c,c-+-----6-----+  -> count distinct values only
+---222--+--e,e,f,g,h,i-+-----6-----+  -> count distinct values only
+--------+--------------+-----------+

推荐答案

尝试 CHAR_LENGTH（REPLACE（GROUP_CONCAT（distinct（letter）SEPARATOR'，'），'，' ，''））

您好

Dude



试试此解决方案

Hi
Dude

Try this Solution

Declare @Temp as table (Sno int,Letter nvarchar(50),TotalChar int)
insert INTO @Temp VALUES(1,'a,a,b,b,c,c',0)
insert INTO @Temp VALUES(2,'e,e,f,g,h,i',0)

SELECT sno,Letter,
(SELECT count(DISTINCT Data) from dbo.Split((Letter),','))as Count
from @Temp

执行前（分割功能）



在数据库中创建此功能



创建功能[dbo]。[拆分]

（

@Line nvar char（MAX），

@SplitOn nvarchar（5）='，'

）

退回@RtnValue表

（

Id INT NOT NULL IDENTITY（1,1）PRIMARY KEY CLUSTERED，

数据nvarchar（100）NOT NULL

）

AS

BEGIN

IF @Line IS NULL RETURN



DECLARE @split_on_len INT = LEN（@SplitOn）

DECLARE @start_at INT = 1

DECLARE @end_at INT

DECLARE @data_len INT



WHILE 1 = 1

BEGIN

SET @end_at = CHARINDEX（@ SplitOn，@ Line，@ start_at）

SET @data_len = CASE @end_at WHEN LEN（@Line）ELSE @ end_at- @ start_at END

INSERT INTO @RtnValue（data）VALUES（SUBSTRING（@ Line，@ start_at，@ data_len））;

IF @end_at = 0 BREAK;

SET @start_at = @end_at + @split_on_len

END



返回

结束

Before Execution (Split Function)

Create this function in your DB

CREATE FUNCTION [dbo].[Split]
(
@Line nvarchar(MAX),
@SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Data nvarchar(100) NOT NULL
)
AS
BEGIN
IF @Line IS NULL RETURN

DECLARE @split_on_len INT = LEN(@SplitOn)
DECLARE @start_at INT = 1
DECLARE @end_at INT
DECLARE @data_len INT

WHILE 1=1
BEGIN
SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at)
SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END
INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
IF @end_at = 0 BREAK;
SET @start_at = @end_at + @split_on_len
END

RETURN
END

这篇关于如何使用逗号分隔符获得与group concatinate不同的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！