select (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)as votevaluestr,sum(ct) from tb group by (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)
create table tb(votevaluestr nvarchar(10),ct int) insert into tb select '否',3 insert into tb select '很差',1 insert into tb select '很好',1 insert into tb select '较差',1 insert into tb select '较好',7 insert into tb select '一般',2 go select (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)as votevaluestr,sum(ct)ct from tb group by (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end) /* votevaluestr ct ------------ ----------- 差 2 否 3 好 8 一般 2(4 行受影响) */ go drop table tb
select sum(ct) from table where VoteValueStr="较好" or VoteValueStr="很好" select sum(ct) from table where VoteValueStr="较差" or VoteValueStr="很差"
--创建测试表 create table kb(votevaluestr nvarchar(10),ct int) --插入数据 insert into kb select N'否',3 union all select N'很差',1 union all select N'很好',1 union all select N'较差',1 union all select N'较好',7 union all select N'一般',2 select vaotevaluestr,sum(ct) ct from (select case when votevaluestr like '%差%' then '差' when votevaluestr like '%好%' then '好' else votevaluestr end vaotevaluestr,ct from kb) b group by vaotevaluestr 差 2 否 3 好 8 一般 2
结合case when和聚集函数来写 一楼的答案就很不错
借用一下4楼的造数语句----创建测试表 --create table kb(votevaluestr nvarchar(10),ct int) ----插入数据 --insert into kb --select N'否',3 --union all --select N'很差',1 --union all --select N'很好',1 --union all --select N'较差',1 --union all --select N'较好',7 --union all --select N'一般',2
SELECT CASE WHEN votevaluestr LIKE '%好%' THEN '好' WHEN votevaluestr LIKE '%差%' THEN '差' ELSE votevaluestr END VoteValueStr , SUM(ct) ct FROM KB GROUP BY CASE WHEN votevaluestr LIKE '%好%' THEN '好' WHEN votevaluestr LIKE '%差%' THEN '差' ELSE votevaluestr END /* VoteValueStr ct ------------ ----------- 差 2 否 3 好 8 一般 2(4 行受影响)*/
--创建表 create table test ( VoteValuestr varchar(4) primary key, ct smallint ); --插入数据 insert into test select '否',3 union all select '很差',1 union all select '很好',1 union all select '较差',1 union all select '较好',7 union all select '一般',2;--执行查询 select case when VoteValuestr='否' then '否' when VoteValuestr in('很好','较好') then '好' when VoteValuestr='一般' then '一般' when VoteValuestr in('较差','很差') then '差' end as VoteValuestr,sum(ct)as ct from test group by case when VoteValuestr='否' then '否' when VoteValuestr in('很好','较好') then '好' when VoteValuestr='一般' then '一般' when VoteValuestr in('较差','很差') then '差' end -------------------------------------------- 差 2 否 3 好 8 一般 2
if OBJECT_ID('test')is not null drop table test go create table test(vote_value varchar(10),ct int) insert into test select '否',3 union all select '很差',1 union all select '很好',1 union all select '较差',1 union all select '较好',7 union all select '一般',2 ;with sel as( select vote=case when vote_value='很差' or vote_value='较差' then '差' when vote_value='很好' or vote_value='较好' then '好' else vote_value end,ct from test ) select vote,SUM(ct) from sel group by vote
你这里的否和一般,其实不需要放到case when里面。反而增加了判断的开销
稍微改进一下 呵呵 select VoteValuestr=case when VoteValuestr in('很好','较好') then '好' when VoteValuestr in('较差','很差') then '差' else VoteValuestr end,sum(ct)as ct from test group by case when VoteValuestr in('很好','较好') then '好' when VoteValuestr in('较差','很差') then '差' else VoteValuestr end
6楼和10楼哪个性能更好?? 是用like 还是用in??
在类型较少的时候,单你这个例子,in好一点。因为是='xx' or ='xx' 而我那个是模糊匹配。但是当类型有很多的时候,in可能需要写很多个,性能也未必好。
from tb
group by (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)
insert into tb select '否',3
insert into tb select '很差',1
insert into tb select '很好',1
insert into tb select '较差',1
insert into tb select '较好',7
insert into tb select '一般',2
go
select (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)as votevaluestr,sum(ct)ct
from tb
group by (case when right(votevaluestr,1) in('好','差') then right(votevaluestr,1) else votevaluestr end)
/*
votevaluestr ct
------------ -----------
差 2
否 3
好 8
一般 2(4 行受影响)
*/
go
drop table tb
select sum(ct) from table where VoteValueStr="较差" or VoteValueStr="很差"
create table kb(votevaluestr nvarchar(10),ct int)
--插入数据
insert into kb
select N'否',3
union all
select N'很差',1
union all
select N'很好',1
union all
select N'较差',1
union all
select N'较好',7
union all
select N'一般',2
select vaotevaluestr,sum(ct) ct from (select case when votevaluestr like '%差%' then '差' when votevaluestr like '%好%' then '好' else votevaluestr end vaotevaluestr,ct from kb) b group by vaotevaluestr
差 2
否 3
好 8
一般 2
一楼的答案就很不错
--create table kb(votevaluestr nvarchar(10),ct int)
----插入数据
--insert into kb
--select N'否',3
--union all
--select N'很差',1
--union all
--select N'很好',1
--union all
--select N'较差',1
--union all
--select N'较好',7
--union all
--select N'一般',2
SELECT CASE WHEN votevaluestr LIKE '%好%' THEN '好'
WHEN votevaluestr LIKE '%差%' THEN '差'
ELSE votevaluestr
END VoteValueStr ,
SUM(ct) ct
FROM KB
GROUP BY CASE WHEN votevaluestr LIKE '%好%' THEN '好'
WHEN votevaluestr LIKE '%差%' THEN '差'
ELSE votevaluestr
END
/*
VoteValueStr ct
------------ -----------
差 2
否 3
好 8
一般 2(4 行受影响)*/
create table test
(
VoteValuestr varchar(4) primary key,
ct smallint
);
--插入数据
insert into test
select '否',3 union all
select '很差',1 union all
select '很好',1 union all
select '较差',1 union all
select '较好',7 union all
select '一般',2;--执行查询
select case when VoteValuestr='否' then '否'
when VoteValuestr in('很好','较好') then '好'
when VoteValuestr='一般' then '一般'
when VoteValuestr in('较差','很差') then '差'
end as VoteValuestr,sum(ct)as ct
from test
group by case when VoteValuestr='否' then '否'
when VoteValuestr in('很好','较好') then '好'
when VoteValuestr='一般' then '一般'
when VoteValuestr in('较差','很差') then '差'
end
--------------------------------------------
差 2
否 3
好 8
一般 2
if OBJECT_ID('test')is not null
drop table test
go
create table test(vote_value varchar(10),ct int)
insert into test
select '否',3 union all
select '很差',1 union all
select '很好',1 union all
select '较差',1 union all
select '较好',7 union all
select '一般',2
;with sel as(
select vote=case when vote_value='很差' or vote_value='较差' then '差' when vote_value='很好' or
vote_value='较好' then '好' else vote_value end,ct from test
)
select vote,SUM(ct) from sel group by vote
呵呵
select VoteValuestr=case when VoteValuestr in('很好','较好') then '好'
when VoteValuestr in('较差','很差') then '差'
else VoteValuestr
end,sum(ct)as ct
from test
group by case when VoteValuestr in('很好','较好') then '好'
when VoteValuestr in('较差','很差') then '差'
else VoteValuestr
end
当数据较少的情况in还是比较好的,但是数据一多起来性能未必就好了,可以考虑其他的方法,比如exists
其实数据库原始数据是这样的。应该可以根据VoteValue来排序吧?? 谢谢。。不知道怎么写。。
我想得到这样的结果
好 13
一般 3
差 4