create tb
(col1 varchar(10),
col2 varchar(10),
col2 int)
goinsert tb select 'a','q',10,
union all select 'b','q',20,
union all select 'a','q',30,
union all select 'c','g',40,
union all select 'a','q',50,
union all select 'd','w',60
go实现
col1 col2 col3
a q 90
b q 20
c g 40
d w 60我写的语句是
select col1,col2,sum(case when col1='a',then col3
when col1='b' then col3
when col1='c' then col3
else col3) as col3
from tb
group by col1,col2,col3
可是报错,请问是怎么回事??
(col1 varchar(10),
col2 varchar(10),
col2 int)
goinsert tb select 'a','q',10,
union all select 'b','q',20,
union all select 'a','q',30,
union all select 'c','g',40,
union all select 'a','q',50,
union all select 'd','w',60
go实现
col1 col2 col3
a q 90
b q 20
c g 40
d w 60我写的语句是
select col1,col2,sum(case when col1='a',then col3
when col1='b' then col3
when col1='c' then col3
else col3) as col3
from tb
group by col1,col2,col3
可是报错,请问是怎么回事??
col3) as col3
from tb
group by col1,cOL2
create table tbb
(col1 varchar(10),
col2 varchar(10),
col3 int)
go insert tbb select 'a','q',10
union all select 'b','q',20
union all select 'a','q',30
union all select 'c','g',40
union all select 'a','q',50
union all select 'd','w',60
go
select col1,col2,sum(case when col1='a' then col3
when col1='b' then col3
when col1='c' then col3
else col3 end) as col3
from tbb
group by col1,col2,col3
/*
col1 col2 col3
---------- ---------- -----------
a q 10
a q 30
a q 50
b q 20
c g 40
d w 60(6 行受影响)
*/
when col1='b' then col3
when col1='c' then col3
else col3) as col3
from tb
group by col1,col2,col3
(col1 varchar(10),
col2 varchar(10),
col3 int)
go insert tb select 'a','q',10
union all select 'b','q',20
union all select 'a','q',30
union all select 'c','g',40
union all select 'a','q',50
union all select 'd','w',60
go
--
--DROP TABLE TB
select col1,col2,sum(
col3) as col3
from tb
group by col1,cOL2 col1 col2 col3
---------- ---------- -----------
c g 40
a q 90
b q 20
d w 60(所影响的行数为 4 行)
from tb
group by col1,cOL2
select col1,
col2,
sum(case col1 when 'a' then col3
when 'b' then col3
when 'c' then col3
else col3 end ) as col3
from tb
group by col1, col2
那group by col3 不能直接这么写,需要你吧col3列的语句都带上
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
then 后面要用字符串的,不能使用列名
drop table tb
create table tb
(col1 varchar(10),
col2 varchar(10),
col3 int)
go insert tb select 'a','q',10
union all select 'b','q',20
union all select 'a','q',30
union all select 'c','g',40
union all select 'a','q',50
union all select 'd','w',60
go
select * from tbselect col1,col2,SUM(col3) as col3
from tb
group by col1,col2
(col1 varchar(10),
col2 varchar(10),
col3 int)
go insert tb select 'a','q',10
union all select 'b','q',20
union all select 'a','q',30
union all select 'c','g',40
union all select 'a','q',50
union all select 'd','w',60
go
select *
from tb
order by col1select col1,col2,sum(col3) as col3
from tb
group by col1,col2
order by col1
create table tb
(col1 varchar(10),
col2 varchar(10),
col3 int)
go insert tb select 'a','q',10
union all select 'b','q',20
union all select 'a','q',30
union all select 'c','g',40
union all select 'a','q',50
union all select 'd','w',60 select * from tbselect col1,col2,sum(col3) from tb group by col1,col2
order by col1