MySQL SQL函数获取列中出现多少次字符串的计数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3031526/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL function to get count of how many times string appears in column?
提问by Kristopher Ives
Is there a function for MySQL that will count the number of times a string occurs in another string or column? Basically I want:
MySQL 是否有一个函数可以计算一个字符串在另一个字符串或列中出现的次数？基本上我想要：
SELECT
SUB_COUNT('my word', `my_column`) AS `match_count`
FROM `table`
Thanks!
谢谢！
EDIT:I need to know how many times the string appears in a column for each row in a SELECT.
编辑：我需要知道字符串在SELECT.
回答by Martin Smith
回答by Amadan
Depends what you mean by "string occurs" - as a substring or as full value?
取决于您所说的“字符串出现”是什么意思——作为子字符串还是作为完整值？
Full value case:
全价值案例：
SELECT COUNT(*) FROM my_table WHERE my_column = 'my word';
Substring case:
子串情况：
SELECT COUNT(*) FROM my_table WHERE my_column LIKE '%my word%';
回答by Aaron
I think you may be able to use the following example. I was trying to count the number of times a particular carton type was used when shipping.
我认为您可以使用以下示例。我试图计算运输时使用特定纸箱类型的次数。
SELECT carton_type, COUNT(carton_type) AS match_count
FROM carton_hdr
WHERE whse ='wh1'
GROUP BY "carton_type"
Your scenario:
你的场景：
SELECT my_column COUNT(my_column)
FROM my_table
WHERE my_column = 'my_word'
GROUP BY my_column
If you take out the "where" function, it will count the number of times each distinct entry appears in "my_column".
如果去掉“where”函数，它会计算每个不同条目出现在“my_column”中的次数。
回答by Chris Wuestefeld
I just needed to do something similar, but took a different approach. I copied the relevant string into a temp table where I can add columns to track an index through each occurrence on each row.
我只需要做一些类似的事情，但采取了不同的方法。我将相关字符串复制到一个临时表中，我可以在其中添加列以跟踪每行每次出现的索引。
In my example, I'm looking for substrings " - " (space-dash-space) in product descriptions, with the intent of eventually chopping those apart to show as bullet points, and I'm analyzing the data like this to see how many "bullets" products typically have.
在我的示例中，我正在产品描述中查找子字符串“-”（空格-破折号-空格），目的是最终将它们分开以显示为要点，我正在分析这样的数据以了解如何许多“子弹”产品通常都有。
I suspect this is more efficient than repeatedly re-writing string values, but I haven't actually benchmarked.
我怀疑这比反复重写字符串值更有效，但我实际上还没有进行基准测试。
SELECT
ccp.ProductID, p.ProductDescription, descrlen=LEN(p.ProductDescription),
bulletcnt=0, indx=0, lastmatchat=0
INTO #DescrBullets
FROM Private.CompositeCatalogProduct AS ccp WITH(NOLOCK)
INNER JOIN Products.Product AS p WITH(NOLOCK) ON p.ProductId = ccp.ProductID
WHERE ccp.CompositeCatalogID=53
DECLARE @rows INT = 1
WHILE @rows>0
BEGIN
-- find the next occurrence on each row that's still in play
UPDATE #DescrBullets
SET lastmatchat = PATINDEX('% - %',RIGHT(ProductDescription,descrlen-indx))
WHERE indx<descrlen
-- anywhere that a match was found, increment my counter, and move my
-- index "cursor" past it
UPDATE #DescrBullets
SET bulletcnt = bulletcnt + 1,
indx = indx + lastmatchat + 2
WHERE lastmatchat>0
SET @rows = @@ROWCOUNT
-- for all the ones that didn't have a match, advance indx past the end
-- so we don't need to reprocess on next iterations
UPDATE #DescrBullets
SET indx=descrlen
WHERE lastmatchat=0
RAISERROR('processing, %d products still have bullets', 0, 1, @rows) WITH NOWAIT
END
SELECT db.bulletcnt, occurs=COUNT(*)
FROM #DescrBullets AS db
GROUP BY db.bulletcnt
ORDER BY 1
