SQLSERVER 列轉(zhuǎn)行

第一：你的時(shí)間維度表基本沒(méi)有意義，微軟SSIS中心認(rèn)為時(shí)間維度至少由日期構(gòu)成主鍵?？梢哉J(rèn)為是最小基本業(yè)務(wù)顆粒。

網(wǎng)站建設(shè)哪家好，找成都創(chuàng)新互聯(lián)公司！專注于網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、微信小程序、集團(tuán)企業(yè)網(wǎng)站建設(shè)等服務(wù)項(xiàng)目。為回饋新老客戶創(chuàng)新互聯(lián)還提供了雁江免費(fèi)建站歡迎大家使用！

來(lái)個(gè)Sample，更復(fù)雜的在我空間里。但是道理是一樣的。都是借助動(dòng)態(tài)SQL和一些函數(shù)。

----------------------------------------------------------------

/*

作者:Edwin

數(shù)據(jù)庫(kù):SQL?SERVER?2005+

作用:指定時(shí)間區(qū)間的自然周有幾天，如果垮年度，由外圍驗(yàn)證

Version?1.0

Copyright?(c)?2015,?SQL?SERVER?2008

*/

----------------------------------------------------------------

/*參數(shù)設(shè)定區(qū)域,參數(shù)為開(kāi)始時(shí)間和結(jié)束時(shí)間*/

----------------------------------------------------------------

declare?@FDate?DateTime?set?@FDate?=?'2014-09-01?00:00:00.000'

declare?@Edate?DateTime?set?@Edate?=?'2015-01-01?00:00:00.000'

----------------------------------------------------------------

/*SQL主體*/

----------------------------------------------------------------

declare?@WeekHeader?nvarchar(max)

select?@WeekHeader?=?coalesce(@WeekHeader+',['+cast(WeekOfYear?as?varchar)+']','['+cast(WeekOfYear?as?varchar)+?']')

from?

(

select?WeekOfYear?from?Comn.Calendar?where?DatePerDay=@FDate?and?DatePerDay@Edate?group?by?WeekOfYear?

)?M

declare?@PivotSQL?nvarchar(max)?set?@PivotSQL=N'

select

Year?as?年份,'+@WeekHeader+'

from

(

select?[Year],WeekOfYear,DatePerDay?from?[DT_WareHouse].[Comn].[Calendar]?where?DatePerDay=@FDate?and?DatePerDay@Edate

)?M

pivot

(

count(DatePerDay)?for?[WeekOfYear]?in('+@WeekHeader+')

)?PVT'

exec?sp_executesql?@PivotSQL,N'@FDate?datetime,@Edate?datetime',@FDate,@Edate

執(zhí)行結(jié)果：

在看一下時(shí)間維度表：

至于PVT標(biāo)題別名問(wèn)題，這個(gè)可以在時(shí)間維度表中創(chuàng)建字符串類型的第幾周等樣式的列來(lái)完成。

這種方式比較簡(jiǎn)單。

或是

----------------------------------------------------------------

/*

作者:Edwin

數(shù)據(jù)庫(kù):SQL?SERVER?2005+

作用:指定時(shí)間區(qū)間的自然周銷售,統(tǒng)計(jì)周期為某一年，如果垮年度，由外圍驗(yàn)證

Version?1.0

Copyright?(c)?2015,?SQL?SERVER?2008

*/

----------------------------------------------------------------

/*參數(shù)設(shè)定區(qū)域,參數(shù)為開(kāi)始時(shí)間和結(jié)束時(shí)間*/

----------------------------------------------------------------

declare?@FDate?DateTime?set?@FDate?=?'2014-09-01?00:00:00.000'

declare?@Edate?DateTime?set?@Edate?=?'2015-01-01?00:00:00.000'

----------------------------------------------------------------

/*SQL主體*/

----------------------------------------------------------------

declare?@WeekHeader?nvarchar(max)

select?@WeekHeader?=?coalesce(@WeekHeader+',['+cast(WeekOfYear?as?varchar)+']','['+cast(WeekOfYear?as?varchar)+?']')

from?

(

select?('第'+cast(WeekOfYear?as?varchar)+'周')?as?WeekOfYear?from?Comn.Calendar?where?DatePerDay=@FDate?and?DatePerDay@Edate?group?by?WeekOfYear?

)?M

declare?@PivotSQL?nvarchar(max)?set?@PivotSQL=N'

select

Year?as?年份,'+@WeekHeader+'

from

(

select?[Year],(''第''+cast(WeekOfYear?as?varchar)+''周'')?as?WeekOfYear,DatePerDay?from?[DT_WareHouse].[Comn].[Calendar]?where?DatePerDay=@FDate?and?DatePerDay@Edate

)?M

pivot

(

count(DatePerDay)?for?[WeekOfYear]?in('+@WeekHeader+')

)?PVT'

exec?sp_executesql?@PivotSQL,N'@FDate?datetime,@Edate?datetime',@FDate,@Edate

結(jié)果：

按照時(shí)間區(qū)間查詢數(shù)據(jù)庫(kù)的問(wèn)題

select * from 表 where 時(shí)間='統(tǒng)計(jì)其實(shí)時(shí)間' and 時(shí)間=‘統(tǒng)計(jì)結(jié)束時(shí)間’

sqlserver2008怎么實(shí)現(xiàn)自動(dòng)分區(qū)表

/*第一步：創(chuàng)建分區(qū)函數(shù)*/

Create partition function Part_func_Bag(varchar(20)) as range right

/*正式區(qū)間

for values(N'01100923909760', N'01100936207030', N'6311001806524',N'92451400060101');*/for values(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714');goselect * from Bag where BagCode in(N'91701311710807', N'07201280707101', N'8011011089884',N'80241001430714')

/*第二步：創(chuàng)建文件組和文件*/

alter database ZXAutoCode add filegroup [Bag_1]; alter database ZXAutoCode add filegroup [Bag_2];alter database ZXAutoCode add filegroup [Bag_3];alter database ZXAutoCode add filegroup [Bag_4];goalter database ZXAutoCode add file (name = Bag1_data,filename = 'E:\MSSQL\TESTDATA\Bag1_data.ndf',size = 3MB) to filegroup [Bag_1];alter database ZXAutoCode add file (name = Bag2_data,filename = 'E:\MSSQL\TESTDATA\Bag2_data.ndf',size = 3MB) to filegroup [Bag_2];alter database ZXAutoCode add file (name = Bag3_data,filename = 'E:\MSSQL\TESTDATA\Bag3_data.ndf',size = 3MB) to filegroup [Bag_3]; alter database ZXAutoCode add file (name = Bag4_data,filename = 'E:\MSSQL\TESTDATA\Bag4_data.ndf',size = 3MB) to filegroup [Bag_4]; go

/*第三步：創(chuàng)建分區(qū)方案并關(guān)聯(lián)到分區(qū)函數(shù)*/

Create partition scheme Part_func_Bag_scheme as partition Part_func_Bag to ([Bag_1],[Bag_2],[Bag_3],[Bag_4],[Primary]); go

/*第四步 重建索引（刪除聚集索引以及需要分區(qū)字段的索引后重建該類索引，表被按分區(qū)值將分配到各文件組。數(shù)據(jù)在這一步開(kāi)始轉(zhuǎn)移。）*/

EXEC sp_helpindex N'Bag' --查看orders中使用的索引 drop index idx_cl_od on Bag;gocreate clustered index idx_cl_od on Bag(bagcode) on Part_func_Bag_scheme(bagcode); go

數(shù)據(jù)庫(kù)中，between…and相當(dāng)于數(shù)學(xué)中的開(kāi)區(qū)間還是閉區(qū)間？急?。?！

between

2

and

4

和

=2and=4

是等價(jià)的，-------------每種數(shù)據(jù)庫(kù)處理方式不一樣，這種是sql的處理方式;mysql+sqlserver

oracle中between and也包含邊界值，也就是說(shuō)包含兩個(gè)端的數(shù)，前后都是

閉區(qū)間

。

java 按照時(shí)間區(qū)間查詢sqlserver2000的問(wèn)題

select count(*)

from Complaints d

where 1 = 1 and d.create_time:create_time1 and d.create_time:create_time2

看看參數(shù)傳進(jìn)去的實(shí)際值是什么樣的。

或者，又試試，先不用參數(shù)，直接把日期寫(xiě)在SQL語(yǔ)句中。

SQLSERVER語(yǔ)句怎么查詢一個(gè)時(shí)間段的數(shù)據(jù)

select * from 表 where 日期字段='開(kāi)始日期' and 日期字段='截止日期'

and convert(char(8),日期字段,108)='開(kāi)始時(shí)間' and convert(char(8),日期字段,108)='截止時(shí)間'

例如:

select * from tb1 where dDate='2010-11-05' and dDate='2010-11-15'

and convert(char(8),dDate,108)='22:30:00' and convert(char(8),dDate,108)='23:00:00'

網(wǎng)站題目：sqlserver區(qū)間,sql 時(shí)間區(qū)間
網(wǎng)站路徑：http://sd-ha.com/article6/phhpig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、軟件開(kāi)發(fā)、網(wǎng)站內(nèi)鏈、定制網(wǎng)站、云服務(wù)器、App設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)