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

Question

有没有一种方法可以在GROUP BY查询中包含LIKE表达式？例如：

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

column_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