SQL中使用CONCAT时统计字符串并按规则计算得分的方法咨询

Great question! Instead of working with the concatenated string (which can be error-prone if your values ever have formatting variations), it's cleaner to count the complete values directly from your original four columns. Here's a straightforward approach:

1. Count from Original Columns (Recommended) #

This method avoids string parsing entirely by checking each column individually:

SELECT
  -- Calculate how many columns are 'complete'
  (
    CASE WHEN col1 = 'complete' THEN 1 ELSE 0 END +
    CASE WHEN col2 = 'complete' THEN 1 ELSE 0 END +
    CASE WHEN col3 = 'complete' THEN 1 ELSE 0 END +
    CASE WHEN col4 = 'complete' THEN 1 ELSE 0 END
  ) AS total_complete,
  -- Map the count to your desired score
  CASE
    WHEN total_complete = 4 THEN '100%'
    WHEN total_complete = 3 THEN '60%'
    WHEN total_complete = 2 THEN '30%' -- Adjust this value to match your requirements
    WHEN total_complete = 1 THEN '10%' -- Adjust this value to match your requirements
    ELSE '0%' -- For 0 complete values
  END AS score
FROM your_table;

How it works: #

• Each CASE statement checks if a single column equals complete and returns 1 if true, 0 otherwise. Summing these gives your total count of complete values.
• The outer CASE maps the total count to your predefined score percentages. Feel free to tweak the values for 2, 1, or 0 complete entries to fit your exact scoring rules.

2. If You Must Work with the Concatenated String #

If you only have access to the concatenated result (e.g., (complete,complete,incomplete,incomplete)), you can use string functions to count occurrences of complete. The exact syntax varies by SQL database:

For MySQL/MariaDB: #

Use REGEXP_COUNT to count matches:

SELECT
  REGEXP_COUNT(concatenated_col, 'complete') AS total_complete,
  CASE
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 4 THEN '100%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 3 THEN '60%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 2 THEN '30%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 1 THEN '10%'
    ELSE '0%'
  END AS score
FROM your_table;

For SQL Server: #

Since SQL Server doesn’t have REGEXP_COUNT by default, use this trick with REPLACE and LEN:

SELECT
  (LEN(concatenated_col) - LEN(REPLACE(concatenated_col, 'complete', ''))) / LEN('complete') AS total_complete,
  CASE
    WHEN (LEN(concatenated_col) - LEN(REPLACE(concatenated_col, 'complete', ''))) / LEN('complete') = 4 THEN '100%'
    WHEN (LEN(concatenated_col) - LEN(REPLACE(concatenated_col, 'complete', ''))) / LEN('complete') = 3 THEN '60%'
    WHEN (LEN(concatenated_col) - LEN(REPLACE(concatenated_col, 'complete', ''))) / LEN('complete') = 2 THEN '30%'
    WHEN (LEN(concatenated_col) - LEN(REPLACE(concatenated_col, 'complete', ''))) / LEN('complete') = 1 THEN '10%'
    ELSE '0%'
  END AS score
FROM your_table;

For PostgreSQL: #

Use REGEXP_COUNT (similar to MySQL):

SELECT
  REGEXP_COUNT(concatenated_col, 'complete') AS total_complete,
  CASE
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 4 THEN '100%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 3 THEN '60%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 2 THEN '30%'
    WHEN REGEXP_COUNT(concatenated_col, 'complete') = 1 THEN '10%'
    ELSE '0%'
  END AS score
FROM your_table;

内容的提问来源于stack exchange，提问作者Ramin Rafat