sql - T-SQL - GROUP BY 和 LIKE - 这可能吗？

Question

有没有办法在 GROUP BY 查询中包含 LIKE 表达式？例如:

SELECT Count(*) 
FROM tblWhatever
GROUP BY column_x [LIKE %Fall-2009%]

列_x:

--------
BIOL-Fall_2009
HIST Fall_2009
BIOL Spring_2009

结果:

------
Fall_2009   2
Spring_2009 1

Answer 1

您需要一个返回“Fall_2009”或“Spring_2009”的表达式，然后对该表达式进行分组。例如:

-- identify each pattern individually w/ a case statement
SELECT
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END AS group_by_value
, COUNT(*) AS group_by_count
FROM Table1 a
GROUP BY 
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END

或

-- strip all characters up to the first space or dash
SELECT 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'') AS group_by_value
, COUNT(*) as group_by_count
FROM Table1 a
GROUP BY 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'')

或

-- join to a (pseudo) table of pattern masks
SELECT b.Label, COUNT(*)
FROM Table1 a
JOIN (
  SELECT '%Fall[_]2009'  , 'Fall, 2009' UNION ALL
  SELECT '%Spring[_]2009', 'Spring, 2009'
  ) b (Mask, Label) ON a.column_x LIKE b.Mask
GROUP BY b.Label