22,300
社区成员




WITH t1 AS (
SELECT
ROW_NUMBER() OVER(partition by DATEPART(hour, performstartdate),
cameraguid order by id) as nid,
cameraguid,
performstartdate,
DATEPART(hour,performstartdate) [hour],
imagelifengaverage
FROM imagelengthaveragetable
WHERE (performstartdate >= '2015-08-01')
AND (performstartdate < '2015-09-01')
),
t as
(
SELECT t1.[hour], t2.twocode, t1.imagelifengaverage
FROM t1
LEFT JOIN cameratable as t2
ON t1.cameraguid = t2.[guid]
WHERE t1.nid =1
)
SELECT twocode,
[8] AS [8:00:00],
[9] AS [9:00:00],
[10] AS [10:00:00],
[11] AS [11:00:00],
[12] AS [12:00:00],
[13] AS [13:00:00],
[14] AS [14:00:00],
[15] AS [15:00:00],
[16] AS [16:00:00],
[17] AS [17:00:00]
FROM t
PIVOT (MIN(imagelifengaverage)
FOR [hour] IN ([8],[9],[10],[11],[12],[13],[14],[15],[16],[17])
) p
[/quote]
这个只是把一个月的数据集合在一起了,我要的是分开的看10楼,就算把条件改成 WHERE (performstartdate= '2015-08-01')
WHERE (performstartdate = '2015-08-02') WHERE (performstartdate = '2015-08-03') WHERE (performstartdate >= '2015-08-04') WHERE (performstartdate >= '2015-08-05')....... WHERE (performstartdate >= '2015-09-01')要循环30多次才能查完一个月的,30次的数据库读取。。。会很慢 还有就是不是整点的时间的显示。。。。那个只是刚巧数据库里的数值是整点。。
WITH t1 AS (
SELECT
ROW_NUMBER() OVER(partition by DATEPART(hour, performstartdate),
cameraguid order by id) as nid,
cameraguid,
performstartdate,
DATEPART(hour,performstartdate) [hour],
imagelifengaverage
FROM imagelengthaveragetable
WHERE (performstartdate >= '2015-08-01')
AND (performstartdate < '2015-09-01')
),
t as
(
SELECT t1.[hour], t2.twocode, t1.imagelifengaverage
FROM t1
LEFT JOIN cameratable as t2
ON t1.cameraguid = t2.[guid]
WHERE t1.nid =1
)
SELECT twocode,
[8] AS [8:00:00],
[9] AS [9:00:00],
[10] AS [10:00:00],
[11] AS [11:00:00],
[12] AS [12:00:00],
[13] AS [13:00:00],
[14] AS [14:00:00],
[15] AS [15:00:00],
[16] AS [16:00:00],
[17] AS [17:00:00]
FROM t
PIVOT (MIN(imagelifengaverage)
FOR [hour] IN ([8],[9],[10],[11],[12],[13],[14],[15],[16],[17])
) p
WITH t AS (
SELECT DATEPART(hour,imagelengthaveragetable.performstartdate) [hour],
cameratable.twocode,
imagelengthaveragetable.imagelifengaverage
FROM imagelengthaveragetable
LEFT JOIN cameratable
ON imagelengthaveragetable.cameraguid = cameratable.guid
WHERE (imagelengthaveragetable.performstartdate >= '2015-08-01')
AND (imagelengthaveragetable.performstartdate < '2015-09-01')
)
SELECT twocode,
[8] AS [8:00:00],
[9] AS [9:00:00],
...
[17] AS [17:00:00]
FROM t
PIVOT (AVG(imagelifengaverage)
FOR [hour] IN ([8],[9],[10],[11],[12],[13],[14],[15],[16],[17])
) p