㈠ 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?
順便說下，按你的意思的話，用報表中的矩陣很輕松就能解決。