id自增列
mac_bh設備編號nvarchar(10)
rq日期datetime
kssj開始時間datetime
jssj結束時間datetime
表腳本：
CREATE TABLE [dbo].[aaa] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[mac_bh] [nvarchar] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[rq] [datetime] NULL ,
[kssj] [datetime] NULL ,
[jssj] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[aaa] WITH NOCHECK ADD
CONSTRAINT [PK_aaa] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
GO
實作效果圖：
橫著從每個月1號到31號
豎著從每天的0點到晚上24點
顯示的內容是開始時間+結束時間，能把設備號顯示進去更好
uj5u.com熱心網友回復：
為啥開始時間是10:00:00的被歸到“9點-10點”這一檔uj5u.com熱心網友回復：
因為是10點開始的uj5u.com熱心網友回復：
那不是應該歸到10-11點這時間段里嗎，否則和8點開始而被歸到8-9點這個時間段矛盾了嗎
試試下面的
DECLARE @DATE VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @DATE='2019-12'
;WITH CTE
AS
(SELECT NUMBER,DATEADD(DAY,NUMBER,CAST(@DATE+'-01' AS DATE)) AS SINGLE_DAY
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE='P'
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY,CAST(@DATE+'-01' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@DATE+'-01' AS DATE)))))
SELECT @SQL=ISNULL(@SQL+',','')+'['+CAST(SINGLE_DAY AS VARCHAR(10))+']'
FROM CTE
SET @SQL='WITH CTE
AS
(SELECT A.NUMBER,B.*,CAST(A.NUMBER AS VARCHAR)+''-''+CAST(A.NUMBER+1 AS VARCHAR) AS TIME_RANGE
FROM MASTER.DBO.SPT_VALUES A
LEFT JOIN
(SELECT RQ ,DATEPART(HOUR,kssj) AS START_HOUR,
MAX(CONTENT) AS CONTENT
FROM
(SELECT *,
STUFF((SELECT '',''+mac_bh+'' ''+CAST(kssj AS VARCHAR(30))+'' ''+CAST(jssj AS VARCHAR(30)) FROM #T WHERE A.RQ=RQ AND DATEPART(HOUR,KSSJ)=DATEPART(HOUR,A.KSSJ) FOR XML PATH('''')),1,1,'''') AS CONTENT
FROM #T AS A) AS B
GROUP BY RQ,DATEPART(HOUR,kssj)) AS B ON A.NUMBER=B.START_HOUR
WHERE TYPE=''P'' AND NUMBER<12)
SELECT TIME_RANGE,'+@SQL+' FROM CTE
PIVOT (MAX(CONTENT) FOR RQ IN('+@SQL+')) B
ORDER BY NUMBER'
EXEC(@SQL)
uj5u.com熱心網友回復：
服務器: 訊息 156，級別 15，狀態 1，行 6在關鍵字 'WITH' 附近有語法錯誤。
我的資料庫是SQL SERVER 2000
uj5u.com熱心網友回復：
2000下，其它還好辦，不過拼接字串就比較麻煩，要寫函式了。
uj5u.com熱心網友回復：
SQL對我來說很難，函式就是難上加難，你能幫我寫出函式來嗎？重謝
uj5u.com熱心網友回復：
函式
CREATE FUNCTION KSSJ_COMBINE
(@RQ DATE,@KSSJ DATETIME)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @CONTENT VARCHAR(8000)
SELECT @CONTENT=ISNULL(@CONTENT+',','')+ ','+mac_bh+' '+CAST(kssj AS VARCHAR(30))+' '+CAST(jssj AS VARCHAR(30))
FROM
(SELECT mac_bh,RQ,kssj,jssj FROM T1 GROUP BY mac_bh,RQ,kssj,jssj) AS A
WHERE RQ=@RQ AND DATEPART(HOUR,KSSJ)=DATEPART(HOUR,@KSSJ)
RETURN @CONTENT
END
呼叫函式
DECLARE @DATE VARCHAR(10)
DECLARE @SQL VARCHAR(8000)
SET @DATE='2019-12'
SELECT @SQL=ISNULL(@SQL+',','')+'MAX(CASE WHEN RQ='''+CAST(SINGLE_DAY AS VARCHAR(10))+'''THEN CONTENT ELSE '''' END) AS '''+CAST(SINGLE_DAY AS VARCHAR(10))+''''
FROM
((SELECT NUMBER,DATEADD(DAY,NUMBER,CAST(@DATE+'-01' AS DATE)) AS SINGLE_DAY
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE='P'
AND NUMBER BETWEEN 0 AND DATEDIFF(DAY,CAST(@DATE+'-01' AS DATE),DATEADD(DAY,-1,DATEADD(MONTH,1,CAST(@DATE+'-01' AS DATE)))))) AS A
SET @SQL=' SELECT A.NUMBER,B.*,CAST(A.NUMBER AS VARCHAR)+''-''+CAST(A.NUMBER+1 AS VARCHAR) AS TIME_RANGE
INTO #A
FROM MASTER.DBO.SPT_VALUES A
LEFT JOIN
(SELECT RQ ,DATEPART(HOUR,kssj) AS START_HOUR,
MAX(DBO.KSSJ_COMBINE(RQ,KSSJ)) AS CONTENT
FROM T1
GROUP BY RQ,DATEPART(HOUR,kssj)) AS B ON A.NUMBER=B.START_HOUR
WHERE TYPE=''P'' AND NUMBER<12
SELECT TIME_RANGE,'+@SQL+'
FROM #A
GROUP BY TIME_RANGE,NUMBER
ORDER BY NUMBER'
EXEC(@SQL)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/88994.html
標籤：疑難問題
上一篇：PHP
下一篇：關于水卡演算法