【有点难】如果在group by的时候把指定的两个值加在一起，其他不变
我有一个表，现在是用的Group by 统计单词出现的次数

SELECT top 10 SUM(counts) AS Number, term as Log_Item1 FROM wf_cipin GROUP BY Term ORDER BY SUM(counts) desc

但是希望能够指定其中的某两个词或多个词的Count加成一个值，比如下面的ACUTELY和ACUTELYS最后总数是6，而别的值是多少还是多少，不知如何去写，谢谢大家！



term counts date
----------------------------
ACUSAN12011-02
ACUTE12010-11
ACUTE132010-09
ACUTE322011-02
ACUTE52010-12
ACUTE92011-04
ACUTELY52010-12
ACUTELYS12011-02
AD12010-09
AD12010-12
AD292011-02

[解决办法]

SQL code

SELECT top 10 SUM(counts) AS Number, case term when 'ACUTELY' then 'ACUTELYS' else term end as Log_Item1 FROM wf_cipin GROUP BY case term when 'ACUTELY' then 'ACUTELYS' else term end ORDER BY SUM(counts) desc
[解决办法]
SQL code
SELECT top 10 SUM(counts) AS Number,    case term when 'ACUTE' then 'ACUTELYS' else term end as Log_Item1 FROM wf_cipin GROUP BY case term when 'ACUTE' then 'ACUTELYS' else term end ORDER BY SUM(counts) desc
[解决办法]
SQL code
IF OBJECT_ID('tempdb..#temp') IS NOT NULL    DROP TABLE #tempGOCREATE TABLE #temp(    word VARCHAR(50),    [count] INT,    [month] CHAR(7))INSERT #tempselect 'ACUSAN', '1', '2011-02' union allselect 'ACUTE', '1', '2010-11' union allselect 'ACUTE', '13', '2010-09' union allselect 'ACUTE', '32', '2011-02' union allselect 'ACUTE', '5', '2010-12' union allselect 'ACUTE', '9', '2011-04' union allselect 'ACUTELY', '5', '2010-12' union allselect 'ACUTELYS', '1', '2011-02' union allselect 'AD', '1', '2010-09' union allselect 'AD', '1', '2010-12' union allselect 'AD', '29', '2011-02'GO--SQL:DECLARE @word1 VARCHAR(100)SET @word1 = '|ACUTELY|ACUTELYS|';WITH cte AS(    SELECT word, [count]=SUM([count]) FROM #temp GROUP BY word)SELECT    word = CASE WHEN CHARINDEX('|'+word+'|', @word1) > 0 THEN @word1 ELSE word END,    [count]=SUM([count])FROM cteGROUP BY CASE WHEN CHARINDEX('|'+word+'|', @word1) > 0 THEN @word1 ELSE word ENDORDER BY [count] DESC--RESULT:/*word    countACUTE    60AD    31|ACUTELY|ACUTELYS|    6ACUSAN    1*/
[解决办法]
SQL code
CREATE TABLE #temp(    word VARCHAR(50),    [count] INT,    [month] CHAR(7))INSERT #tempselect 'ACUSAN', '1', '2011-02' union allselect 'ACUTE', '1', '2010-11' union allselect 'ACUTE', '13', '2010-09' union allselect 'ACUTE', '32', '2011-02' union allselect 'ACUTE', '5', '2010-12' union allselect 'ACUTE', '9', '2011-04' union allselect 'ACUTELY', '5', '2010-12' union allselect 'ACUTELYS', '1', '2011-02' union allselect 'AD', '1', '2010-09' union allselect 'AD', '1', '2010-12' union allselect 'AD', '29', '2011-02'GOSELECT top 10 SUM(count) AS Number,    case word when 'ACUTELY' then 'ACUTELYS' else word end as Log_Item1 FROM #temp GROUP BY case word when 'ACUTELY' then 'ACUTELYS' else word end ORDER BY SUM(count) descDROP TABLE #temp/*******************************Number      Log_Item1----------- --------------------------------------------------60          ACUTE31          AD6           ACUTELYS1           ACUSAN(4 行受影响)
[解决办法]
SQL code
SELECT top 10 SUM(counts) AS Number,  case term when 'ACUTELY' then 'ACUTELYS' else term end as Log_Item1 FROM wf_cipin GROUP BY case term when 'ACUTELY' then 'ACUTELYS' else term end ORDER BY SUM(counts) desc