sql - distinct and sum if like

Question

I have a table as the following

name
-----------
1@apple@1
2@apple@2
3@apple@4
4@box@4
5@box@5

and I want to get the result as:

name
--------------
apple 3
box   2

Thanks in advance for your help

标签： sqlsql-servercountdistinct-valuescharindex

Answer 1

这就是你需要的。

select
  SUBSTRING(
    name,
    CHARINDEX('@', name) + 1,
    LEN(name) - (
      CHARINDEX('@', REVERSE(name)) + CHARINDEX('@', name)
    )
  ),
  count(1)
from
  tbl
group by
  SUBSTRING(
    name,
    CHARINDEX('@', name) + 1,
    LEN(name) - (
      CHARINDEX('@', REVERSE(name)) + CHARINDEX('@', name)
    )
  )