Question

我有两个表:

TableOne
- RawDataId int (pk)
- TimeStamp DateTime
- BuildingID int

TableTwo
- RawDataId int (pk/fk)
- MeterId int (pk)
- Value real

气象局编辑并非独一无二,重复多次(但总是数量相等)。两个表格合在一起没有问题。我可以选择15顶顶,并按时间顺序订购邮票,给我每个参数的最新价值(总共15个,每个有时间邮票)。然而,我还需要从前一段时期(实际上在1440分钟和1439分钟之前)获得每个参数的价值,如果这样做的话。

因此,在进行询问后,我需要一张表格,上面有表1和表Two栏的栏目,但还有两栏的数值B和数值C(B是1440分钟之前的数值,C 1439更早)。前一天和晚上大部分时间,我一直在玩.,我慢慢慢地失去这块土地。

Any help would be appreciated. Thanks peeps.

————————————

I ve included the actual table schema below, together with some sample data.

CREATE TABLE [dbo].[TableOne](
[RawDataId] [bigint] IDENTITY(1,1) NOT NULL,
[TimeStamp] [datetime] NOT NULL,
[BuildingId] [int] NULL,
CONSTRAINT [TableOne_PK] PRIMARY KEY CLUSTERED 

CREATE TABLE [dbo].[TableTwo](
[MeterId] [bigint] NOT NULL,
[RawDataId] [bigint] NOT NULL,
[Value] [real] NULL,
 CONSTRAINT [TableTwo_PK] PRIMARY KEY CLUSTERED

表1最后30份记录样本数据:

RawDataId, TimeStamp, BuildingId
21677   2012-05-16 00:03:00.000 1
21678   2012-05-16 00:03:00.000 1
21679   2012-05-16 00:03:00.000 1
21680   2012-05-16 00:03:00.000 1
21681   2012-05-16 00:03:00.000 1
21682   2012-05-16 00:03:00.000 1
21683   2012-05-16 00:03:00.000 1
21684   2012-05-16 00:03:00.000 1
21685   2012-05-16 00:03:00.000 1
21686   2012-05-16 00:03:00.000 1
21687   2012-05-16 00:03:00.000 1
21688   2012-05-16 00:03:00.000 1
21689   2012-05-16 00:03:00.000 1
21690   2012-05-16 00:03:00.000 1
21691   2012-05-16 00:03:00.000 1
21662   2012-05-16 00:02:00.000 1
21663   2012-05-16 00:02:00.000 1
21664   2012-05-16 00:02:00.000 1
21665   2012-05-16 00:02:00.000 1
21666   2012-05-16 00:02:00.000 1
21667   2012-05-16 00:02:00.000 1
21668   2012-05-16 00:02:00.000 1
21669   2012-05-16 00:02:00.000 1
21670   2012-05-16 00:02:00.000 1
21671   2012-05-16 00:02:00.000 1
21672   2012-05-16 00:02:00.000 1
21673   2012-05-16 00:02:00.000 1
21674   2012-05-16 00:02:00.000 1
21675   2012-05-16 00:02:00.000 1
21676   2012-05-16 00:02:00.000 1

表格样本 2:

MeterId, RawDataId, Value
15  21691   7722613
14  21690   908944
13  21689   4982947
12  21688   3821899
11  21687   6
10  21686   0
9   21685   0
8   21684   5761656
7   21683   4240048
6   21682   1541372
5   21681   283223
4   21680   1.298603E+07
3   21679   388137
2   21678   876121
1   21677   0
15  21676   7722615
14  21675   908944
13  21674   4982947
12  21673   3821899
11  21672   5
10  21671   0
9   21670   0
8   21669   5761656
7   21668   4240052
6   21667   1541372
5   21666   283223
4   21665   1.298604E+07
3   21664   388137
2   21663   876122
1   21662   0

每1个表(印有时间印章)都写上了计数。在选择15个最高记录时(用时序号向我提供最新数值),我还需要在14时40分至14时39分之前获得这一参数的数值(与最新时间序列有关)。我希望这能够更清楚地说明这一点。

So far, my SQL query looks like this:

SELECT TOP 15 * FROM (Select TableOne.[RawDataId], 
[TimeStamp], BuildingId, MeterId, `enter code here`Value 
FROM [TableOne]
INNER JOIN TableTwo ON
TableOne = TableTwo) as PS
ORDER BY [TimeStamp];

问询给我如下,但我需要增加两栏,其数值为1440和1439分钟前,与时俱进:

RawDataId, TimeStamp, BuildingId, MeterId, Value
21677   2012-05-16 00:03:00.000 1   1   0
21678   2012-05-16 00:03:00.000 1   2   876121
21679   2012-05-16 00:03:00.000 1   3   388137
21680   2012-05-16 00:03:00.000 1   4   1.298603E+07
21681   2012-05-16 00:03:00.000 1   5   283223
21682   2012-05-16 00:03:00.000 1   6   1541372
21683   2012-05-16 00:03:00.000 1   7   4240048
21684   2012-05-16 00:03:00.000 1   8   5761656
21685   2012-05-16 00:03:00.000 1   9   0
21686   2012-05-16 00:03:00.000 1   10  0
21687   2012-05-16 00:03:00.000 1   11  6
21688   2012-05-16 00:03:00.000 1   12  3821899
21689   2012-05-16 00:03:00.000 1   13  4982947
21690   2012-05-16 00:03:00.000 1   14  908944
21691   2012-05-16 00:03:00.000 1   15  7722613

Answer 1

如果不看到数据(和可能查询)样本,它就很难理解这一问题。如果我的理解正确的话,这应当做到:

SELECT
(
SELECT TOP 1 Value
FROM TableOne t1 join TableTwo t2 ON t1.RawDataId = t2.RawDataId
WHERE t1.RawDataId IN (
SELECT RawDataId FROM TableTwo WHERE MeterId = tbl.MeterId
) and TimeStamp = DATEADD(mi, -1440, tbl.TimeStamp)
) as ValueB,
(
SELECT TOP 1 Value
FROM TableOne t1 join TableTwo t2 ON t1.RawDataId = t2.RawDataId
WHERE t1.RawDataId IN (
SELECT RawDataId FROM TableTwo WHERE MeterId = tbl.MeterId
) and TimeStamp = DATEADD(mi, -1439, tbl.TimeStamp)
) as ValueC
FROM TableTwo tbl

Answer 2

If you are always going to have values for 1440-minutes and 1439-minutes prior, something like this might work:

SELECT TOP 15 
      TableOne.BuildingID
    , TableOne.RawDataID
    , TableOne.TimeStamp
    , TableTwo.MeterID
    , TableTwo.Value
    , TableTwo1439.Value AS ValueB
    , TableTwo1440.Value AS ValueC
FROM TableOne
JOIN TableTwo ON TableOne.RawDataID=TableTwo.RawDataID
JOIN TableTwo TableTwo1439 ON TableTwo.MeterID=TableTwo1439.MeterID
JOIN TableOne TableOne1439 ON TableTwo1439.RawDataID=TableOne1439.RawDataID AND TableOne.TimeStamp=DATEADD(MINUTE,-1439,TableOne1439.TimeStamp)
JOIN TableTwo TableTwo1440 ON TableTwo.MeterID=TableTwo1440.MeterID
JOIN TableOne TableOne1440 ON TableTwo1440.RawDataID=TableOne1440.RawDataID AND TableOne.TimeStamp=DATEADD(MINUTE,-1440,TableOne1440.TimeStamp)
ORDER BY TableOne.Timestamp DESC

否则,你仍然可以采用同样的做法,但可能需要用词汇表加以压缩。

Answer 3

如果你使用2005年或以后的服务器,你可以尝试这样的东西:

WITH data AS (
  SELECT
    t1.RawDataId,
    t1.TimeStamp,
    t1.BuildingId,
    t2.MeterId,
    t2.Value,
    x.Diff AS TimeStampId
  FROM (SELECT BuildingId, MAX(TimeStamp) AS TimeStamp FROM TableOne GROUP BY BuildingId) t
    CROSS JOIN (SELECT 0 UNION ALL SELECT 1440 UNION ALL SELECT 1439) x (Diff)
    INNER JOIN TableOne t1 ON t.BuildingId = t1.BuildingId
                          AND t1.TimeStamp = DATEADD(MINUTE, -x.Diff, t.TimeStamp)
    INNER JOIN TableTwo t2 ON t2.RawDataId = t1.RawDataId
)
SELECT
  RawDataId,
  TimeStamp,
  BuildingId,
  MeterId,
  Value           = [0],
  Value1440MinAgo = [1440],
  Value1439MinAgo = [1439]
FROM data
PIVOT (
  MAX(Value) FOR TimeStampId IN ([0], [1440], [1439])
) p

这就是说,我在这里假设你想对各个建筑群负责,因为这个栏在你的产出中都有。如果你只想具体的话,你可以改写t。

(SELECT TOP 1 * FROM TableOne WHERE BuildingId = @Id ORDER BY TimeStamp DESC) t

友情链接