寻找针对表运行的查询,以输出每个用户的用户数和以分号分隔的项数。

huangapple go评论88阅读模式
英文:

Looking for query run against table to output number of users and number of semicolon-separated items per user

问题

我有一个表格,看起来像下面这样:

Id Name FormIds
1 john.doe@blah.co 32132;32323;232323;424323;2323;23232;2323
2 jane.doe@whatever.co 32323;11123;11321
3 joe.blow@yeet.co 22324

我正在寻找一个查询,它将为我提供每个用户的不同FormId数量,并在最后一行显示总用户数和总FormId数:
示例输出:

Names TotalFormIdsPerUser
john.doe@blah.co 7
jane.doe@whatever.co 3
joe.blow@yeet.co 1
---------------------- --------------------------
3 11

非常感谢!

英文:

I have a table that looks something like the following:

|Id|Name                  |FormIds                                           |
|--|----------------------|--------------------------------------------------|
|1 |john.doe@blah.co      |32132;32323;232323;424323;2323;23232;2323         |
|2 |jane.doe@whatever.co  |32323;11123;11321                                 |
|3 |joe.blow@yeet.co      |22324                                             |

I'm looking for a query that will give me the number of distinct FormId's per user and last row showing total users count and total formids:
Example output:

Names                 |TotalFormIdsPerUser                                      
----------------------|--------------------------
john.doe@blah.co      |7         
jane.doe@whatever.co  |3 
joe.blow@yeet.co      |1
----------------------|--------------------------
3                     |11

Thank you in advance!!

答案1

得分: 1

以下是翻译好的部分:

你可以使用 string_split(用于拆分 FormIds)和一个 CTE(通用表达式)

然后,你可以使用 group by 来找到总和和总计。

演示链接

英文:

You can use string_split (For splitting the FormIds) and a CTE

Then you use group by to find the sum and sum total.

with _list as (
		select FormIds,Name,s.value value_
		from Testf a
		cross apply(select * from string_split(a.FormIds,';'))s
)
select Name as Names, c as TotalFormIdsPerUser                                      
from (
select  a.Name,count(a.value_) as c,1 as Type
from  _list a
group by a.Name

union all
select  
		 cast( count(distinct a.Name) as varchar(100)) as name
		,count(a.value_) as c
		,2 as Type
from  _list a
)s
order by Type, c desc

Demo

huangapple
  • 本文由 发表于 2023年6月15日 02:06:31
  • 转载请务必保留本文链接:https://go.coder-hub.com/76476418.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定