sql - Count the number of rows that match a particular substring in another column - Stack Overflow

admin管理员组

文章数量:1122832

I am building a small Rust app which makes use of SQLite.

I have a table with names and a string containing a code:

├───────────────────────────────────┼────────────────┼
│ "Abaddon the Despoiler"           │ "UBR"          │
├───────────────────────────────────┼────────────────┼
│ "Admiral Beckett Brass"           │ "UBR"          │
├───────────────────────────────────┼────────────────┼
│ "Arcades, the Strategist"         │ "WUG"          │ 
├───────────────────────────────────┼────────────────┼

I also have a table with the letters that make up the code. The goal is to obtain a table that looks like the following (example taken with the three rows above):

┌───────┬───────┐
│ Color │ Count │
├───────┼───────┤
│ "B"   │ 2     │
├───────┼───────┤
│ "C"   │ 0     │
├───────┼───────┤
│ "G"   │ 1     │
├───────┼───────┤
│ "R"   │ 2     │
├───────┼───────┤
│ "U"   │ 3     │
├───────┼───────┤
│ "W"   │ 1     │
└───────┴───────┘

As you can see, the letter 'U' appears three times (once in each row of the first table), so the count is three. The letter 'B' similarly appears twice, and so on, for each of the six letters of the second table.

How would one write a SELECT request which counts the occurences of each of the characters in the left-hand column of the second table that appear in the right-hand column of the first table ?

I made an attempt like this:

SELECT color.name AS [Color],
    SUM(CASE WHEN deck.color LIKE '%W%' THEN 1 ELSE 0 END) AS [Count]
    FROM color, deck
    GROUP BY color.name ORDER BY [Count], [Color]

But it only works for one letter at a time. I do not know how to use the LIKE statement with a variable pattern.

I am building a small Rust app which makes use of SQLite.

I have a table with names and a string containing a code:

├───────────────────────────────────┼────────────────┼
│ "Abaddon the Despoiler"           │ "UBR"          │
├───────────────────────────────────┼────────────────┼
│ "Admiral Beckett Brass"           │ "UBR"          │
├───────────────────────────────────┼────────────────┼
│ "Arcades, the Strategist"         │ "WUG"          │ 
├───────────────────────────────────┼────────────────┼

I also have a table with the letters that make up the code. The goal is to obtain a table that looks like the following (example taken with the three rows above):

┌───────┬───────┐
│ Color │ Count │
├───────┼───────┤
│ "B"   │ 2     │
├───────┼───────┤
│ "C"   │ 0     │
├───────┼───────┤
│ "G"   │ 1     │
├───────┼───────┤
│ "R"   │ 2     │
├───────┼───────┤
│ "U"   │ 3     │
├───────┼───────┤
│ "W"   │ 1     │
└───────┴───────┘

As you can see, the letter 'U' appears three times (once in each row of the first table), so the count is three. The letter 'B' similarly appears twice, and so on, for each of the six letters of the second table.

How would one write a SELECT request which counts the occurences of each of the characters in the left-hand column of the second table that appear in the right-hand column of the first table ?

I made an attempt like this:

SELECT color.name AS [Color],
    SUM(CASE WHEN deck.color LIKE '%W%' THEN 1 ELSE 0 END) AS [Count]
    FROM color, deck
    GROUP BY color.name ORDER BY [Count], [Color]

But it only works for one letter at a time. I do not know how to use the LIKE statement with a variable pattern.

• sql
• sqlite

Share Improve this question Follow edited yesterday Jmb 23k22 gold badges3535 silver badges6666 bronze badges asked yesterday rickastleyrickastley 4366 bronze badges 1

• I am sorry, but you question is a little bit unclear. Could you please clarify, from what table you want to achieve the result and the required logic – Sergey Commented yesterday

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 1

Since you need all letters, so we take the letter table containing individual letters, I have named it as letters as an example. So assuming letters table have

letter
B
C
G
R
U
W

Then we can just take letters and LEFT JOIN with your code table to find if the letters are present in the code table

SELECT
    l.letter,
    COUNT(n.name) AS Count
FROM
    letters l
LEFT JOIN  names_codes n ON n.code LIKE '%' || l.letter || '%'
GROUP BY  l.letter 
ORDER BY l.letter;

Example Fiddle

Output

letter	Count
B	2
C	0
G	1
R	2
U	3
W	1

Looks like you just need to join

SELECT
    color.name AS [Color],
    COUNT(d.color) AS [Count]
FROM color c
LEFT JOIN deck d ON d.color LIKE '%' || c.name || '%'
GROUP BY
    color.name
ORDER BY [Count], [Color]

Building on Samhita's answer, you don't need to actually create a table. You can use a recursive CTE to dynamically generate all the letters and then join on it:

WITH recursive alphabet(value) as (
    select 'A'
    union all select CHAR(UNICODE(value) + 1)
    from alphabet
    where value < 'Z')
SELECT value, COUNT(color)
FROM alphabet
LEFT JOIN deck ON deck.color LIKE '%' || value || '%'
GROUP BY value

SQLFiddle demo

EDIT:
Modern SQLite versions have a generate_series extension that can replace this CTE, but I preferred not to base my answer on an extension that you need to load.

Create CTE with all letters or use yor table with letters and CROSS JOIN with your table.
Count cases where letter is in string.

See example

with colors(cv) as(
  values ('B'),('C'),('G'),('R'),('U'),('W')
)
select cv letter,sum(cc) cnt
from(
select cv,t1.scolor
  ,case when  substring(t1.scolor,1,1)=c.cv
         or substring(t1.scolor,2,1)=c.cv
         or substring(t1.scolor,3,1)=c.cv
      then 1 
    else 0 end cc
from colors c, test t1 
)a
group by cv

Fiddle

本文标签： sqlCount the number of rows that match a particular substring in another columnStack Overflow