use module
go
create table PopulationInfo
( id int identity(1,1) primary key,
country varchar(10) not null,
sex tinyint, -- l表男性 2表女性
number int
)insert into PopulationInfo (country,sex,number) values ('中国',1,340)
insert into PopulationInfo (country,sex,number) values ('中国',2,260)
insert into PopulationInfo (country,sex,number) values ('美国',1,45)
insert into PopulationInfo (country,sex,number) values ('美国',2,55)
insert into PopulationInfo (country,sex,number) values ('加拿大',1,51)
insert into PopulationInfo (country,sex,number) values ('加拿大',2,49)
insert into PopulationInfo (country,sex,number) values ('英国',1,40)
insert into PopulationInfo (country,sex,number) values ('英国',1,60)use module
go select country, sum (case when sex = 1 then number else 0 end ) as male, --男性
sum (case when sex = 2 then number else 0 end ) as female-- 女性
from PopulationInfo
--group by country
上面的查询按 country male female 格式显示
这儿查询的时候如果去掉group by country会提示错误“选择列表中的列 'PopulationInfo.country' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中”
这个怎么理解??
go
create table PopulationInfo
( id int identity(1,1) primary key,
country varchar(10) not null,
sex tinyint, -- l表男性 2表女性
number int
)insert into PopulationInfo (country,sex,number) values ('中国',1,340)
insert into PopulationInfo (country,sex,number) values ('中国',2,260)
insert into PopulationInfo (country,sex,number) values ('美国',1,45)
insert into PopulationInfo (country,sex,number) values ('美国',2,55)
insert into PopulationInfo (country,sex,number) values ('加拿大',1,51)
insert into PopulationInfo (country,sex,number) values ('加拿大',2,49)
insert into PopulationInfo (country,sex,number) values ('英国',1,40)
insert into PopulationInfo (country,sex,number) values ('英国',1,60)use module
go select country, sum (case when sex = 1 then number else 0 end ) as male, --男性
sum (case when sex = 2 then number else 0 end ) as female-- 女性
from PopulationInfo
--group by country
上面的查询按 country male female 格式显示
这儿查询的时候如果去掉group by country会提示错误“选择列表中的列 'PopulationInfo.country' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中”
这个怎么理解??
且select 里country 也必须去掉。
要不就除了聚合函数以外的字段都略过不写