㈠ sql 一列数据分段统计怎么写
count(case when 分数字段 between 90 and 99 then 1 end) as[90-99分],count(case when 分拍亩散数字段 between 80 and 89 then 1 end) as[80-89分],count(case when 分数字段 between 70 and 79 then 1 end) as[70-79分],count(case when 分数字段<70 then 1 end) as[70分耐戚以下]from 学生分数表袭氏
㈡ sql server矩阵查询！急求
好像有点难哦，商品 奔驰 宝马 这个是查询结果的字段名称吗？应该是要创建临时表才能实现把；
应该能达到你要的效果，
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_Add]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SP_Add]
GO
CREATE TABLE [dbo].[SP_Add] (
[商品] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[宝马] [int] NOT NULL ,
[奔驰] [int] NOT NULL
) ON [PRIMARY]
GO
declare @str varchar(50),@std varchar(50)
delete from SP_Add
declare sp_name cursor keyset
for
select distinct spname from SP_info
open sp_name
fetch first from sp_name into @str
while @@fetch_status=0
begin
declare std cursor keyset
for
select distinct SP_std from SP_info
open std
fetch first from std into @std
while @@fetch_status=0
begin
if @std='宝马'
begin
insert into SP_Add
select @str,sum(Quantity),0 from [Order],SP_info where [Order].SPID =SP_info.SPID and SPNAME=@str and SP_std=@std
end
if @std='奔驰'
begin
insert into SP_Add
select @str,0,sum(Quantity) from [Order],SP_info where [Order].SPID =SP_info.SPID and SPNAME=@str and SP_std=@std
end
fetch next from std into @std
end
close std
deallocate std
fetch next from sp_name into @str
end
close sp_name
deallocate sp_name
select * from SP_Add
㈢ sql 分数字段分段统计
//尝试下面的sql--目测可以的
selectuid,score,t2.flagfrom[dbo].[table1]ast1
leftjoin[dbo].[table2]ast2
ont1.score>=t2.range_maxandt1.score<=t2.range_min
㈣ SQL 查询不连续的号码，按号码段分段显示，并统计分段数量。
declare@AAtable(yearsint,numberint)
insertinto@AAvalues(2014,45201521)
insertinto@AAvalues(2014,45201522)
insertinto@AAvalues(2014,45201523)
insertinto@AAvalues(2014,45201524)
insertinto@AAvalues(2014,45201525)
insertinto@AAvalues(2014,45201526)
insertinto@AAvalues(2014,45201527)
insertinto@AAvalues(2014,45201528)
insertinto@AAvalues(2014,45201532)
insertinto@AAvalues(2014,45201535)
insertinto@AAvalues(2014,45201536)
insertinto@AAvalues(2013,45070121)
insertinto@AAvalues(2013,45070122)
insertinto@AAvalues(2011,45010022)
insertinto@AAvalues(2011,45010023)
insertinto@AAvalues(2012,48038811)
insertinto@AAvalues(2012,48038815)
select年份,casewhenstart_num!=end_numthenconvert(varchar(20),start_num)+'-'+convert(varchar(20),end_num)elseconvert(varchar(20),start_num)endas票段号,份数from(
selectyears年份,min(number)asstart_num,max(number)asend_num,COUNT(years)as份数
from
(selectyears,number,number-ROW_NUMBER()over(orderbynumber)asgrp
from@AA)m
groupbygrp,years)t
orderby年份,票段号asc
㈤ SQL如何根据指定列的不同值区间分段
select
count(case when col_a between 0 and 10 then col_a end)
,count(case when col_a between 20 and 30 then col_a end)
,count(case when col_a between 3 and 40 then col_a end)
from tablename1
㈥ SQL：对score表进行分段统计
select 课程编号,
sum(case when 成绩=N'优棚宏' then 1 else 0 end) 优宏棚,
sum(case when 成绩蔽和则=N'良' then 1 else 0 end) 良,
sum(case when 成绩=N'中' then 1 else 0 end) 中,
sum(case when 成绩=N'差' then 1 else 0 end) 差,
...........
count(*) as 人数合计
from score
group by 课程编号
㈦ sql语句,循环生成一到十二月的汇总表
Select
xxxxxxxxxx,xxx,xxx,xxx
from
myTable
where
xxxx
group
by
xxx,xxx,xxx
关键在于分组group
by
，要你一直这么Union
那要1年365天你还得写365个union?
顺便说下，按你的意思的话，用报表中的矩阵很轻松就能解决。