T-SQL - GROUP BY 和 LIKE - 这可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1647736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL - GROUP BY with LIKE - is this possible?
提问by fieldingmellish
Is there a way to include a LIKE expression in a GROUP BY query? For example:
有没有办法在 GROUP BY 查询中包含 LIKE 表达式？例如：
SELECT Count(*)
FROM tblWhatever
GROUP BY column_x [LIKE %Fall-2009%]
column_x:
列_x：
--------
BIOL-Fall_2009
HIST Fall_2009
BIOL Spring_2009
Result:
结果：
------
Fall_2009 2
Spring_2009 1
回答by Peter Radocchia
You need an expression that returns "Fall_2009" or "Spring_2009", and then group on that expression. eg:
您需要一个返回“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
or
或者
-- 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),'')
or
或者
-- 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
回答by OMG Ponies
No, the LIKE function is not supported in the GROUP BYclause. You'd need to use:
不，GROUP BY子句中不支持 LIKE 函数。你需要使用：
SELECT x.term,
COUNT(*)
FROM (SELECT CASE
WHEN CHARINDEX('Fall_2009', t.column) > 0 THEN
SUBSTRING(t.column, CHARINDEX('Fall_2009', t.column), LEN(t.column))
WHEN CHARINDEX('Spring_2009', t.column) > 0 THEN
SUBSTRING(t.column, CHARINDEX('Spring_2009', t.column), LEN(t.column))
ELSE
NULL
END as TERM
FROM TABLE t) x
GROUP BY x.term
回答by cdonner
LIKE does not make sense in your context, as it either matches or it does not, but it does not establish groups. You will have to use string functions that parse the column values into what makes sense for your data.
LIKE 在您的上下文中没有意义，因为它要么匹配要么不匹配，但它不建立组。您将必须使用字符串函数将列值解析为对您的数据有意义的值。
回答by keithwarren7
I dont believe so, LIKE is effectively a binary state - something is LIKE or NOT LIKE, there are not logical degrees of 'likeness' that could be grouped together. Then again, I could be off my rocker.
我不这么认为，LIKE 实际上是一个二元状态 - 某些东西是 LIKE 或 NOT LIKE，没有可以组合在一起的“相似”的逻辑程度。再说一次，我可以离开我的摇杆了。
If what you really want is to express filtering over your grouped data take a look at the HAVING clause.
如果您真正想要的是对分组数据进行过滤，请查看 HAVING 子句。
回答by Andomar
If your courses always take five letters, you can keep it really simple:
如果你的课程总是需要五个字母，你可以保持简单：
SELECT substring(column_x,5,100), count(*)
FROM YourTable
GROUP BY substring(column_x,5,100)
Otherwise, check Peters or Rexem's answer.
否则，请查看 Peters 或 Rexem 的答案。
回答by Brad
How about this:
这个怎么样：
SELECT MAX(column_x) AS column_x
FROM (
SELECT column_x
FROM tblWhatever
WHERE (UPPER(column_x) LIKE '%Fall-2009%')
) AS derivedtbl_1 GROUP BY column_x
回答by Larry Lustig
Unfortunately, you have a badly structured database, having combined SUBJECT and TERM into the same column. When you use GROUP BY it treats each unique value in the column as a group in the result set. You'd be best advised to restructure the database if at all possible — you probably want three columns here (SUBJECT, TERM, SCHOOL_YEAR) but two might possibly be appropriate.
不幸的是，您的数据库结构很差，将 SUBJECT 和 TERM 合并到同一列中。当您使用 GROUP BY 时，它将列中的每个唯一值视为结果集中的一个组。如果可能的话，最好建议您重构数据库——您可能需要三列（SUBJECT、TERM、SCHOOL_YEAR），但两列可能更合适。
If you can't restructure the database you'll need to parse the column to extract the term. Rexem showed you one way to do this, you could also use a stored procedure.
如果您无法重构数据库，则需要解析该列以提取术语。Rexem 向您展示了一种方法，您也可以使用存储过程。
回答by o.k.w
You can have it this way, but like the others said, doesn't really make sense:
你可以这样，但就像其他人说的那样，并没有真正的意义：
SELECT COUNT(*)
FROM tblWhatever
GROUP BY column_x
HAVING column_x LIKE '%Fall-2009%'
