如何查询大小写不敏感的重复列值及其重复次数？

Got it, let's work through this problem step by step. Since your my_column uses a case-sensitive collation (SQL_Latin1_General_CP1_CS_AS), direct grouping would treat bar and BAR as separate values—but we need to group by their lowercase equivalent to spot duplicates across case variations.

Basic Query: Get Duplicate Lowercase Values and Their Counts #

This query will return each lowercase value that has duplicates, plus how many times it appears (counting all case variations):

SELECT
    LOWER(my_column) AS lower_case_value,
    COUNT(*) AS duplicate_count
FROM my_table
GROUP BY LOWER(my_column)
HAVING COUNT(*) > 1;

For your sample data, this would output:

Enhanced Query: Include Original Case Variations #

If you want to see all the original case-sensitive values that make up each duplicate group, use STRING_AGG (SQL Server-specific) to concatenate them into a single list:

SELECT
    LOWER(my_column) AS lower_case_value,
    COUNT(*) AS duplicate_count,
    STRING_AGG(my_column, ', ') AS original_values
FROM my_table
GROUP BY LOWER(my_column)
HAVING COUNT(*) > 1;

This would give you a more complete picture:

How This Works #

• LOWER(my_column) standardizes all values to lowercase, so case variations like bar and BAR are grouped together.
• GROUP BY LOWER(my_column) aggregates rows based on this standardized value.
• HAVING COUNT(*) > 1 filters out groups with only one unique case variation (like foo and baz in your sample), leaving only the duplicates we care about.

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