【有点难】如果在group by的时候把指定的两个值加在一起,其他不变,该如何解决
【有点难】如果在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
----------------------------
ACUSAN 1 2011-02
ACUTE 1 2010-11
ACUTE 13 2010-09
ACUTE 32 2011-02
ACUTE 5 2010-12
ACUTE 9 2011-04
ACUTELY 5 2010-12
ACUTELYS 1 2011-02
AD 1 2010-09
AD 1 2010-12
AD 29 2011-02
------解决方案--------------------
我有一个表,现在是用的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
----------------------------
ACUSAN 1 2011-02
ACUTE 1 2010-11
ACUTE 13 2010-09
ACUTE 32 2011-02
ACUTE 5 2010-12
ACUTE 9 2011-04
ACUTELY 5 2010-12
ACUTELYS 1 2011-02
AD 1 2010-09
AD 1 2010-12
AD 29 2011-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 #temp GO CREATE TABLE #temp ( word VARCHAR(50), [count] INT, [month] CHAR(7) ) INSERT #temp select 'ACUSAN', '1', '2011-02' union all select 'ACUTE', '1', '2010-11' union all select 'ACUTE', '13', '2010-09' union all select 'ACUTE', '32', '2011-02' union all select 'ACUTE', '5', '2010-12' union all select 'ACUTE', '9', '2011-04' union all select 'ACUTELY', '5', '2010-12' union all select 'ACUTELYS', '1', '2011-02' union all select 'AD', '1', '2010-09' union all select 'AD', '1', '2010-12' union all select '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 cte GROUP BY CASE WHEN CHARINDEX('|'+word+'|', @word1) > 0 THEN @word1 ELSE word END ORDER BY [count] DESC --RESULT: /* word count ACUTE 60 AD 31 |ACUTELY|ACUTELYS| 6 ACUSAN 1 */
------解决方案--------------------
- SQL code
CREATE TABLE #temp ( word VARCHAR(50), [count] INT, [month] CHAR(7) ) INSERT #temp select 'ACUSAN', '1', '2011-02' union all select 'ACUTE', '1', '2010-11' union all select 'ACUTE', '13', '2010-09' union all select 'ACUTE', '32', '2011-02' union all select 'ACUTE', '5', '2010-12' union all select 'ACUTE', '9', '2011-04' union all select 'ACUTELY', '5', '2010-12' union all select 'ACUTELYS', '1', '2011-02' union all select 'AD', '1', '2010-09' union all select 'AD', '1', '2010-12' union all select 'AD', '29', '2011-02' GO SELECT 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) desc DROP TABLE #temp /******************************* Number Log_Item1 ----------- -------------------------------------------------- 60 ACUTE 31 AD 6 ACUTELYS 1 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