sql-server - 查询单个表并合并相同日期时间的结果
问题描述
我有一个表,我在其中插入与 id 相关的值,这些值会随着时间而变化。
例子:
| 身份证 | 时间戳 | 价值 | | 1 | 2018-09-12 02:29:31.154 | 7.139 | | 1 | 2018-09-12 02:40:46.724 | 7.254 | | 2 | 2018-09-06 02:40:46.724 | 132.451 | | 1 | 2018-09-12 02:42:19.841 | 7.645 | | 3 | 2018-09-12 03:01:45.811 | 45.276 | | 1 | 2018-09-12 03:12:59.121 | 7.421 | | 2 | 2018-09-12 03:12:59.121 | 130.789 | | 1 | 2018-09-12 03:15:33.467 | 7.121 | | 2 | 2018-09-12 03:15:33.467 | 136.198 | | 3 | 2018-09-12 03:15:33.467 | 46.971 | | 2 | 2018-09-12 03:27:13.642 | 131.879 | | 3 | 2018-09-12 03:27:13.642 | 44.645 | | 1 | 2018-09-12 03:30:27.564 | 7.691 | | ... | ... | ... |
我的目标是进行单个查询以获取Id
1、2 和 3 的日期范围之间的值并将它们合并在一起。
结果将是这样的:
{ Timestamp: "2018-09-12 02:29:31.154", id1: 7.123, id2: null, id3: null }
{ Timestamp: "2018-09-12 02:40:46.724", id1: 7.254, id2: 132.451, id3: null }
{ Timestamp: "2018-09-12 02:42:19.841", id1: 7.645, id2: null, id3: null }
{ Timestamp: "2018-09-12 03:01:45.811", id1: null, id2: null, id3: 45.276 }
{ Timestamp: "2018-09-12 03:12:59.121", id1: 7.421, id2: 130.789, id3: null }
{ Timestamp: "2018-09-12 03:15:33.467", id1: 7.121, id2: 136.198, id3: 46.971 }
{ Timestamp: "2018-09-12 03:27:13.642", id1: null, id2: 131.879, id3: 44.645 }
{ Timestamp: "2018-09-12 03:30:27.564", id1: 7.691, id2: null, id3: null }
是否可以在单个查询中进行?
解决方案
var ids = new List<int> { 1, 2, 3};
var result = (from item in db.Table
where ids.Contains(item.id) && item.Timestamp > fromDate && item.Timestamp < toDate
group item by item.Timestamp into sub
select new
{
Timestamp = sub.Key,
id1 = sub.Where(x => x.id == 1).Select(x => x.Value).FirstOrDefault(),
id2 = sub.Where(x => x.id == 2).Select(x => x.Value).FirstOrDefault(),
id3 = sub.Where(x => x.id == 3).Select(x => x.Value).FirstOrDefault()
}).ToList();
推荐阅读
- android - getY 在片段中使用时返回 0.0
- flutter - 颤振设计曲线布局
- html - HTML:垂直对齐进度条
- c# - 如何对 List1 (int) 进行排序,以便相应的 List2 (string) 也进行排序?
- css - 可变宽度的图像条
- java - Cloud Dataflow:动态更改 bigquery 目标
- javascript - JSON.parse 里面有函数
- glassfish - jpa、persistence.xml、glassfish netbeans ide postgresql db pool an jdbc resource和jta的问题
- flutter - Flutter DraggableScrollableSheet - 如何以编程方式展开/折叠
- command-line - 如何在命令行中打开和编辑文件?