select Mark_Range='0<= M <60',
Numbers=count(*)
from @t
where Mark>=0 and Mark<60
union all
select Mark_Range='60<=M <80',
Numbers=count(*)
from @t
where Mark>=60 and Mark<80
union all
select Mark_Range='M>=80',
Numbers=count(*)
from @t
where Mark>=80
Numbers=count(*)
from @t
where Mark>=0 and Mark<60
union all
select Mark_Range='60<=M <80',
Numbers=count(*)
from @t
where Mark>=60 and Mark<80
union all
select Mark_Range='M>=80',
Numbers=count(*)
from @t
where Mark>=80
解决方案 »
- 请教一个存储过程。
- connection 调用 数据库 存储过程 带输入输出参数
- 这句SQL语句成立吗?(go的含义是什么)
- 求一sql及crystal report (橫豎轉換)﹐難﹐請高手來拿分﹗
- 对于淘宝网中好评率的设计
- 想不出来用SQL怎么写,关于在给定的时间段时查找对应关系并找出开始结束时间
- 菜鸟问题,在线等待:用Sql server,怎样使int型记录依次加1
- 我安装的SQLSERVER2000简体中文开发版,但是帮助不能使用为什麽??
- 关于 select ... where ... IN的问题
- 怎样简化统计表中字段内容为空字段数与总字段的百分比
- 请助:SQL联接问题!!
- 高手请教
UNION
SELECT '60<= M <80 ' Mark_Range,COUNT(1) Numbers WHERE MarK>=60 AND Mark<80
UNION
SELECT 'M >=80 ' Mark_Range,COUNT(1) Numbers WHERE Mark>80
declare @t table(StudentID varchar(10),Mark int)
insert into @t select '2001',80
union all select '2002',65
union all select '2003',92
union all select '2004',78
union all select '2005',56
--查询
select Mark_Range='0<= M <60',
Numbers=count(*)
from @t
where Mark>=0 and Mark<60
union all
select Mark_Range='60<=M <80',
Numbers=count(*)
from @t
where Mark>=60 and Mark<80
union all
select Mark_Range='M>=80',
Numbers=count(*)
from @t
where Mark>=80
--结果
Mark_Range Numbers
---------- -----------
0<= M <60 1
60<=M <80 2
M>=80 2(所影响的行数为 3 行)
UNION
SELECT '60<= M <80 ' Mark_Range,COUNT(1) Numbers FROM Course WHERE MarK>=60 AND Mark<80
UNION
SELECT 'M >=80 ' Mark_Range,COUNT(1) Numbers FROM Course WHERE Mark>80
when Mark>=60 and Mark<80 then '60<=M <80'
when Mark>=80 then 'M>=80' else '' end as Mark_Range,
count(*) as Numbers from Course
group by
case when Mark>=0 and Mark<60 then '0<= M <60'
when Mark>=60 and Mark<80 then '60<=M <80'
when Mark>=80 then 'M>=80' else '' end
xueguang(xg)的答案跟zlp321002(飘过)的答案差不多,都是是正确的,谢谢!
salonstar(Tommy)用的方法我想是最好的,之前我也试着这样做过,没有做出来,我想用这种方法肯定实现,salonstar(Tommy)写的答案我试用还是有错误,不知道为什么?
UNION
SELECT '60<= M <80 ' Mark_Range,COUNT(1) Numbers FROM Course WHERE MarK>=60 AND Mark<80
UNION
SELECT 'M >=80 ' Mark_Range,COUNT(1) Numbers FROM Course WHERE Mark>80
select _range,count(*) as mumber from
(select case when Mark>=0 and Mark<60 then '0<= M <60'
when Mark>=60 and Mark<80 then '60<=M <80'
when Mark>=80 then 'M>=80' else '' end as Mark_Range,
from Course )as a
group by _range