我有一个表,现在是用的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
----------------------------
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
FROM wf_cipin
GROUP BY case term when 'ACUTELY' then 'ACUTELYS' else term end
ORDER BY SUM(counts) desc
??
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
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
*/
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'
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 ACUTE
31 AD
6 ACUTELYS
1 ACUSAN(4 行受影响)
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