DELPHI盒子
!实时搜索: 盒子论坛 | 注册用户 | 修改信息 | 退出
检举帖 | 全文检索 | 关闭广告 | 捐赠
技术论坛
 用户名
 密  码
自动登陆(30天有效)
忘了密码
≡技术区≡
DELPHI技术
移动应用开发
Web应用开发
数据库专区
报表专区
网络通讯
开源项目
论坛精华贴
≡发布区≡
发布代码
发布控件
文档资料
经典工具
≡事务区≡
网站意见
盒子之家
招聘应聘
信息交换
论坛信息
最新加入: lijian100
今日帖子: 2
在线用户: 2
导航: 论坛 -> 数据库专区 斑竹:liumazi,waterstone  
作者:
男 dbcoder (dbcoder) ▲△△△△ -
注册会员
2022/4/12 13:22:20
标题:
求一条SQL语句能统计字段内容(以;分隔)出现次数 浏览:842
加入我的收藏
楼主: 求一条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楼: 查询结果
此帖子包含附件:
PNG 图像
大小: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  缘在上海
信息
登陆以后才能回复
Copyright © 2CCC.Com 盒子论坛 v2.1 版权所有 页面执行80.07813毫秒 RSS