英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论