我曾想过以下这种办法,但数据量很大时,消耗也很大,所以不能用这种方法
select 奖惩 from
(
select case when 奖惩 like '%小功%' then '小功' when 奖惩 like '%大功%' then '大功'end as 奖惩
) a group by 奖惩请大家给我一个更好的办法,一条SQL语句就能搞定的,不要用签套SQL
select 奖惩 from
(
select case when 奖惩 like '%小功%' then '小功' when 奖惩 like '%大功%' then '大功'end as 奖惩
) a group by 奖惩请大家给我一个更好的办法,一条SQL语句就能搞定的,不要用签套SQL
(奖惩 varchar(20),代号 varchar(20))
insert t
select '记小功1次','01' union all
select '记小功2次','02' union all
select '记大功1次','03' union all
select '记大功2次','04' union all
select '记大功3次','05' select m,count(*)as n from
(
select case when charindex('小功',奖惩)>0 then '小功'
when charindex('大功',奖惩)>0 then '大功' end as m from t
) a group by m
drop table tm n
---- -----------
大功 3
小功 2(所影响的行数为 2 行)
insert into @A select '记小功1次','01'
insert into @A select '记小功2次','02'
insert into @A select '记大功1次','03'
insert into @A select '记大功2次','04'
insert into @A select '记大功3次','05'
select
奖惩=case when charindex('小功',奖惩)>0 then '小功' else '大功' end
from
@A
group by
case when charindex('小功',奖惩)>0 then '小功' else '大功' end
我想,你要不更改一下表的结构,把次数和功过分离里试试!