盒子论坛 v2.1

求一条SQL语句能统计PID字段内容(以;分隔)出现次数

表A:
id      pid
77     p1;p2;p3;
140        p1;p4;
6     p3;p4;
2     p3;p11;
3          p100;p201;
10         p500;p1000;
11         p1001;

要求分类统计pid 出现次数,结果如下:

p1        2
p2        1
p3        3
p4        2
p5        1
p11       1
p100      1
p201      1
p500      1
p1000     1
p1001     1

//////////
----------------------------------------------
-

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2022/4/12 15:22:50

1楼：

如果是sql server的话，只用一个语句肯定实现不了
其他数据库不太清楚
----------------------------------------------
-

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2022/4/12 15:58:18

2楼：

alter procedure split
  @str varchar(128)
as
begin
  declare @p integer, @len integer
  declare @word varchar(128)
  declare @result table(word varchar(128))
  set @len = len(@str)
  set @p = (select charindex(';', @str))
  while @p > 0
  begin
    set @word = substring(@str, 1, @p-1)
    set @word = rtrim(ltrim(@word))
    if (@word <> '')
    begin
      insert into @result values (@word)
    end
    set @str = substring(@str, @p+1, @len)
    set @p = (select charindex(';', @str))
  end
  set @word = rtrim(ltrim(@word))
  if (@word <> '')
  begin
    insert into @result values (@word)
  end
  select * from @result
end;
go

declare @src table(id integer, pid varchar(128))

insert into @src values
  (77 , 'p1;p2;p3;'),
  (140, 'p1;p4;'),
  (6  , 'p3;p4;'),
  (2  , 'p3;p11;'),
  (3  , 'p100;p201;'),
  (10 , 'p500;p1000;'),
  (11 , 'p1001;')

declare @result table(pid varchar(128))
declare @pid varchar(128)
declare @id int = (select min(id) from @src)
while @id is not null
begin
  set @pid = (select pid from @src where id=@id)
  insert into @result exec split @pid
  set @id = (select min(id) from @src where id>@id)
end
select pid, cnt=count(*) from @result group by pid
----------------------------------------------
-

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2022/4/12 16:01:18

3楼：

查询结果

此帖子包含附件:

大小:7.4K

----------------------------------------------
-

作者：

gaono1 (晕乎)	▲▲▲▲▲	-
	普通会员

2022/4/12 17:28:12

4楼：

临时抱佛脚，查资料拼凑了一条SQL，只为对上答案。数据库：MySQL 8.0.28
with recursive cte0(id) as (select 1 union all select id + 1 from cte0 where id < 5),
cte1(str1) as (values row('p1;p2;p3;'),row('p1;p4;'),row('p3;p4;'),row('p3;p11;'),row('p100;p201;'),row('p500;p1000;'),row('p1001;')),
cte2 as (
select substring_index(substring_index(cte1.str1,';',cte0.id),';',-1) as str2
from cte0,cte1
where cte0.id < length(cte1.str1) - length(replace(cte1.str1,';','')) + 1
)
select str2,count(*)as cnt from cte2 group by str2 order by length(str2),str2;
运行结果：
+-------+-----+
| str2  | cnt |
+-------+-----+
| p1    |   2 |
| p2    |   1 |
| p3    |   3 |
| p4    |   2 |
| p11   |   1 |
| p100  |   1 |
| p201  |   1 |
| p500  |   1 |
| p1000 |   1 |
| p1001 |   1 |
+-------+-----+
----------------------------------------------
-

作者：

xlonger (xlonger)	★☆☆☆☆	-
	普通会员

2022/4/12 19:51:37

5楼：

我是不会这么复杂的sql的。

如果我遇到这样的问题。就先把pid的内容，每条记录读取出来，每条记录拆分好，再保存都一个新的数据集pid字段里，然后group一下估计可以了。或者保存到json里，感觉也能实现统计的效果。
----------------------------------------------
我打的是酱油，而不是别的什么油。
我灌的是口水，而不是别的什么水。
我聊的折腾不是那个不折腾的折腾。
我说的阿娇不是那个邓玉娇的阿娇。
3个代表,6个为什么,9个肠胃炎。
D性强的领导干部都不喜欢热比娅。
我特别要讲的是,屁民网黄色论坛是我经常上网必选的网站之一

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2022/4/12 22:36:34

6楼：

更正一下：
create procedure split
  @str varchar(128)
as
begin
  declare @p integer, @len integer
  declare @word varchar(128)
  declare @result table(word varchar(128))
  set @len = len(@str)
  set @p = (select charindex(';', @str))
  while @p > 0
  begin
    set @word = substring(@str, 1, @p-1)
    set @word = rtrim(ltrim(@word))
    if (@word <> '')
    begin
      insert into @result values (@word)
    end
    set @str = substring(@str, @p+1, @len)
    set @p = (select charindex(';', @str))
  end
  set @str = rtrim(ltrim(@str))
  if (@str <> '')
  begin
    insert into @result values (@str)
  end
  select * from @result
end;
go

declare @src table(id integer, pid varchar(128))

insert into @src values
  (77 , 'p1;p2;p3;'),
  (140, 'p1;p4;'),
  (6  , 'p3;p4;'),
  (2  , 'p3;p11;'),
  (3  , 'p100;p201;'),
  (10 , 'p500;p1000;'),
  (11 , 'p1001;')

declare @result table(pid varchar(128))
declare @pid varchar(128)
declare @id int = (select min(id) from @src)
while @id is not null
begin
  set @pid = (select pid from @src where id=@id)
  insert into @result exec split @pid
  set @id = (select min(id) from @src where id>@id)
end
select pid, cnt=count(*) from @result group by pid

pid  cnt
p1  2
p100  1
p1000  1
p1001  1
p11  1
p2  1
p201  1
p3  3
p4  2
p500  1
----------------------------------------------
-

作者：

wk_knife (wk_knife)	★☆☆☆☆	-
	盒子活跃会员

2022/4/12 23:45:24

7楼：

不考虑具体的数据库语言的话，把字符串中的；替换成空，然后用原始的长度减掉替换后的字符串长度，得到的差就是字符；的个数。
考虑具体的语言，可以用语言的特性，如现在的数据库都支持正则表达式。
----------------------------------------------
-

作者：

grj (grj)	★☆☆☆☆	-
	盒子活跃会员

2022/4/13 12:36:08

8楼：

楼上是对的，直接一条SQL不香吗

select id,pid,len(pid)-len(replace(pid,';','')) tempcount from @src
----------------------------------------------
ABFrameWork（307455944）、ABMeeting（644245859提问）作者，QQ 16187001（有问题请加群提问或私信，谢谢）

作者：

xiejiating (牛力)	★☆☆☆☆	-
	盒子活跃会员

2022/4/13 12:52:43

9楼：

--mssql

declare @src table(id integer, pid varchar(128))

insert into @src values (77 , 'p1;p2;p3;')
insert into @src values (140, 'p1;p4;')
insert into @src values (6  , 'p3;p4;')
insert into @src values (2  , 'p3;p11;')
insert into @src values (3  , 'p100;p201;')
insert into @src values (10 , 'p500;p1000;')
insert into @src values (11 , 'p1001;')

--统计“;”在字段pid中出现的次数
select *,DataLength(pid)-DataLength(replace(pid,';','')) as fnCount from @src
----------------------------------------------
-

作者：

dbcoder (dbcoder)	▲▲△△△	-
	普通会员

2022/4/13 17:36:22

10楼：

7,8,9楼的只统计;出现次数,并没有分类统计.要求是这样的:

p1        2
p2        1
p3        3
p4        2
p5        1
p11       1
p100      1
p201      1
p500      1
p1000     1
p1001     1
----------------------------------------------
-

作者：

grj (grj)	★☆☆☆☆	-
	盒子活跃会员

2022/4/13 19:39:33

11楼：

没仔细看你的要求，其实简单的就是搞个函数分解再转行

不知道你为什么一定要一条SQL搞定，如果真要一条SQL搞定，也不是没有办法的，就是折腾下
----------------------------------------------
ABFrameWork（307455944）、ABMeeting（644245859提问）作者，QQ 16187001（有问题请加群提问或私信，谢谢）

作者：

grj (grj)	★☆☆☆☆	-
	盒子活跃会员

2022/4/13 19:46:05

12楼：

SELECT tempItem,count(*) tempItemCount
FROM   (
        SELECT [value] = CONVERT(XML, '<v>' + REPLACE((select (select ''+pid from @src  for xml path(''))),';', '</v><v>') + '</v>')
        ) aa
        OUTER APPLY(
    SELECT tempItem = N.v.value('.', 'nvarchar(100)')
    FROM  aa.[value].nodes('/v') N(v)
) bb
where tempItem<>''
group by tempItem
order by len(tempItem),tempItem
----------------------------------------------
ABFrameWork（307455944）、ABMeeting（644245859提问）作者，QQ 16187001（有问题请加群提问或私信，谢谢）

作者：

grj (grj)	★☆☆☆☆	-
	盒子活跃会员

2022/4/13 19:46:54

13楼：

这样还有问题吗
----------------------------------------------
ABFrameWork（307455944）、ABMeeting（644245859提问）作者，QQ 16187001（有问题请加群提问或私信，谢谢）

作者：

bluestorm8 (bluestorm)	▲▲△△△	-
	普通会员

2022/4/13 23:19:05

14楼：

DECLARE @src table(id integer, pid varchar(128))
INSERT into @src values
  (77 , 'p1;p2;p3;'),
  (140, 'p1;p4;'),
  (6  , 'p3;p4;'),
  (2  , 'p3;p11;'),
  (3  , 'p100;p201;'),
  (10 , 'p500;p1000;'),
  (11 , 'p1001;')

;WITH cte AS (select stuff((select ';'+pid from @src for xml path('')), 1, 1, '') AS list),
      E1        AS ( SELECT 1 AS N UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
          UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
          UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1),
      E2        AS (SELECT 1 AS N FROM E1 a, E1 b),
      E4        AS (SELECT 1 AS N FROM E2 a, E2 b),
      E42       AS (SELECT 1 AS N FROM E4 a, E2 b),
      cteTally  AS (SELECT 0 AS N UNION ALL SELECT TOP (DATALENGTH(ISNULL((SELECT list FROM cte),1)))
          ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
      cteStart AS (SELECT t.N+1  AS N1 FROM cteTally t, cte
          WHERE (SUBSTRING(cte.list,t.N,1) = ';' OR t.N = 0)),
      ids AS (SELECT id = SUBSTRING(cte.list, s.N1, ISNULL(NULLIF(CHARINDEX(';',cte.list,s.N1),0)-s.N1,8000))
          FROM cteStart s, cte)
      SELECT id, COUNT(*) AS cnt FROM ids WHERE id<>'' GROUP BY id ORDER BY LEN(id), ids.id

id  cnt
p1  2
p2  1
p3  3
p4  2
p11  1
p100  1
p201  1
p500  1
p1000  1
p1001  1
----------------------------------------------
-

作者：

luckyrandom (luckyrandom)	★☆☆☆☆	-
	普通会员

2022/4/14 8:48:55

15楼：

sql server 2016开始支持string_split，cross apply一次就出来了
----------------------------------------------
SQL SERVER DBA QQ:315054403 曾经的Delphier 缘在上海

作者：

roadrunner (roadrunner)	★☆☆☆☆	-
	盒子活跃会员

2022/4/14 9:11:36

16楼：

这种计算会消耗大量的数据库服务器资源

你要换个角度去思考，当初的表设计成这个样子，就是为了在客户端进行这些处理。
----------------------------------------------
-

作者：

luckyrandom (luckyrandom)	★☆☆☆☆	-
	普通会员

2022/4/14 11:11:09

17楼：

多数系统数据量小/并发小。。对性能/质量要求并不高
----------------------------------------------
SQL SERVER DBA QQ:315054403 曾经的Delphier 缘在上海

信息

登陆以后才能回复