sql-server - SQL 输出 - 包括来自不同表的具有相同时间戳的列
问题描述
我有一个查询多个表的 SQL 查询。保持相同的时间戳,我希望输出包含两个表中与特定时间戳相关的列。以下代码生成两个单独的表。
SELECT
[2000823317].[TimeStamp],
[2000823317].[E_Total_Last] as "2000823317_E_Total_Last"
FROM
[LIBPV_WB].[AD\30186656].[2000823317]
WHERE
[2000823317].[TimeStamp] BETWEEN '2010-12-31 19:00:00' AND '2010-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2011-12-31 19:00:00' AND '2011-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2012-12-31 19:00:00' AND '2012-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2013-12-31 19:00:00' AND '2013-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2014-12-31 19:00:00' AND '2014-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2015-12-31 19:00:00' AND '2015-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2016-12-31 19:00:00' AND '2016-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2017-12-31 19:00:00' AND '2017-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2018-12-31 19:00:00' AND '2018-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2019-12-31 19:00:00' AND '2019-12-31 19:05:00'
SELECT
[2000823318].[TimeStamp],
[2000823318].[E_Total_Last] as "2000823318_E_Total_Last"
FROM [LIBPV_WB].[AD\30186656].[2000823318]
WHERE
[TimeStamp] BETWEEN '2010-12-31 19:00:00' AND '2010-12-31 19:05:00' or
[TimeStamp] BETWEEN '2011-12-31 19:00:00' AND '2011-12-31 19:05:00' or
[TimeStamp] BETWEEN '2012-12-31 19:00:00' AND '2012-12-31 19:05:00' or
[TimeStamp] BETWEEN '2013-12-31 19:00:00' AND '2013-12-31 19:05:00' or
[TimeStamp] BETWEEN '2014-12-31 19:00:00' AND '2014-12-31 19:05:00' or
[TimeStamp] BETWEEN '2015-12-31 19:00:00' AND '2015-12-31 19:05:00' or
[TimeStamp] BETWEEN '2016-12-31 19:00:00' AND '2016-12-31 19:05:00' or
[TimeStamp] BETWEEN '2017-12-31 19:00:00' AND '2017-12-31 19:05:00' or
[TimeStamp] BETWEEN '2018-12-31 19:00:00' AND '2018-12-31 19:05:00' or
[TimeStamp] BETWEEN '2019-12-31 19:00:00' AND '2019-12-31 19:05:00'
当前输出是两个单独的表。如下所示。
+-------------------------+-------------------------+
| TimeStamp | 2000823317_E_Total_Last |
+-------------------------+-------------------------+
| 2011-12-31 19:00:32.000 | 18981.512 |
| 2012-12-31 19:00:38.000 | 28900.006 |
| 2013-12-31 19:00:36.000 | 38431.747 |
| 2014-12-31 19:00:35.000 | 47849.842 |
| 2015-12-31 19:00:35.000 | 57313.301 |
| 2016-12-31 19:00:35.000 | 66487.605 |
| 2017-12-31 19:00:30.000 | 75611.001 |
| 2018-12-31 19:00:34.000 | 84582.739 |
+-------------------------+-------------------------+
和
+-------------------------+-------------------------+
| TimeStamp | 2000823318_E_Total_Last |
+-------------------------+-------------------------+
| 2011-12-31 19:00:32.000 | 20704.789 |
| 2012-12-31 19:00:38.000 | 31622.242 |
| 2013-12-31 19:00:36.000 | 42268.048 |
| 2014-12-31 19:00:35.000 | 52779.672 |
| 2015-12-31 19:00:35.000 | 63317.686 |
| 2016-12-31 19:00:35.000 | 73649.122 |
| 2017-12-31 19:00:30.000 | 83923.87 |
| 2018-12-31 19:00:34.000 | 93977.001 |
+-------------------------+-------------------------+
所需的输出将有 3 列,第一列是时间戳,后跟 column2 = 第一个表中的数据和 column3 = 第二个表中的数据
+-------------------------+-------------------------+-------------------------+
| TimeStamp | 2000823317_E_Total_Last | 2000823318_E_Total_Last |
+-------------------------+-------------------------+-------------------------+
| 2011-12-31 19:00:32.000 | 18981.512 | value |
| 2012-12-31 19:00:38.000 | 28900.006 | value |
| 2013-12-31 19:00:36.000 | 38431.747 | value |
| 2014-12-31 19:00:35.000 | 47849.842 | value |
| 2015-12-31 19:00:35.000 | 57313.301 | value |
| 2016-12-31 19:00:35.000 | 66487.605 | value |
| 2017-12-31 19:00:30.000 | 75611.001 | value |
| 2018-12-31 19:00:34.000 | 84582.739 | |
+-------------------------+-------------------------+-------------------------+
解决方案
SELECT
[2000823317].[TimeStamp],
[2000823317].[E_Total_Last] as "2000823317_E_Total_Last",
[2000823318].[E_Total_Last] as "2000823318_E_Total_Last"
FROM
[LIBPV_WB].[AD\30186656].[2000823317]
LEFT JOIN
[LIBPV_WB].[AD\30186656].[2000823318] ON [2000823317].[TimeStamp] = [2000823318].[TimeStamp]
WHERE
[2000823317].[TimeStamp] BETWEEN '2010-12-31 19:00:00' AND '2010-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2011-12-31 19:00:00' AND '2011-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2012-12-31 19:00:00' AND '2012-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2013-12-31 19:00:00' AND '2013-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2014-12-31 19:00:00' AND '2014-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2015-12-31 19:00:00' AND '2015-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2016-12-31 19:00:00' AND '2016-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2017-12-31 19:00:00' AND '2017-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2018-12-31 19:00:00' AND '2018-12-31 19:05:00' or
[2000823317].[TimeStamp] BETWEEN '2019-12-31 19:00:00' AND '2019-12-31 19:05:00'
产生以下输出
+---+-------------------------+-------------------------+-------------------------+
| | A | B | C |
+---+-------------------------+-------------------------+-------------------------+
| 1 | TimeStamp | 2000823317_E_Total_Last | 2000823318_E_Total_Last |
| 2 | 2011-12-31 19:00:32.000 | 18981.512 | 20704.789 |
| 3 | 2012-12-31 19:00:38.000 | 28900.006 | 31622.242 |
| 4 | 2013-12-31 19:00:36.000 | 38431.747 | 42268.048 |
| 5 | 2014-12-31 19:00:35.000 | 47849.842 | 52779.672 |
| 6 | 2015-12-31 19:00:35.000 | 57313.301 | 63317.686 |
| 7 | 2016-12-31 19:00:35.000 | 66487.605 | 73649.122 |
| 8 | 2017-12-31 19:00:30.000 | 75611.001 | 83923.87 |
| 9 | 2018-12-31 19:00:34.000 | 84582.739 | 93977.001 |
+---+-------------------------+-------------------------+-------------------------+
推荐阅读
- sql - 保存国家/地区明智的州和城市信息的最佳选择是什么?XML 还是数据库?
- node.js - 调用 UpdateFunctionCode 操作 Lambda AWS 时发生错误 (MissingAuthenticationTokenException)
- coldfusion - Coinbase Pro API 无效签名 ColdFusion
- c# - 为什么属性是公共的,而 setter 是私有的
- android - 如何从应用程序目录(在 android 文件夹内)获取 Arraylist 中的所有音频
- r - 位置抖动的 geom_errorbar 问题
- php - Laravel:无法在刀片文件中显示图像
- ruby-on-rails - 如何比较宝石版本?
- google-signin - 调用 https://oauth2.googleapis.com/token 超时
- javascript - Discord.js 机器人加入然后离开