时间戳

首页 > 解决方案 > 如何使用sql在父母中获取孩子的孩子值

sql - 如何使用sql在父母中获取孩子的孩子值

问题描述

我对 SQL 不是很熟悉。

我有 3 张桌子，每张桌子都是一对多的。意味着一个History有很多Applications，但一个Application有很多ApplicationAttributes。

我需要在每个请求中显示EnrolledTotal列，其中名称。HistoriesHistoryApplicationLIKE = '%Enrollment%'

请问有人可以帮我吗？

历史表：

Id  Imei                  CreationDate   DeviceId
1   ProductionDevice299   2018-11-04     1
7   ProductionDevice300   2018-11-09     1

应用表：

Id  Name              DeviceHistoryId
1   Enrollment.cone   1
2   DPC_OWNERS        1
3   OTHER_APPS        1
6   Enrollment.emp    7
7   DPC_OWNERS        7

应用属性表：

Id  Key             Value   DeviceApplicationId
1   EnrolledTotal   2       1
2   LoginsTotal     5       2
3   OtherAttribt1   8       3
4   OtherAttribt2   12      3
5   OtherAttribt3   17      3
6   EnrolledTotal   21      6
7   LoginsTotal     25      7

预期结果：

Id  Imei                  CreationDate   DeviceId   EnrolledTotal
1   ProductionDevice299   2018-11-04     1          2
7   ProductionDevice300   2018-11-09     1          21

我当前的 sql 查询：

SELECT 
[Extent1].[Id] AS [id], 
[Extent1].[Imei] AS [imei], 
CAST([Extent1].[CreationDate] AS DATE) AS [lastSeenOnline],  
[Extent1].[DeviceId] AS [deviceId]

FROM [DeviceManagement].[dbo].[DeviceHistory] AS [Extent1]

INNER JOIN (
    SELECT [Imei], max([CreationDate]) as MaxDate
    FROM [dbo].[DeviceHistory]
    GROUP BY [Imei], CAST([CreationDate] AS DATE)
) [Extent2] on [Extent1].[Imei] = [Extent2].[Imei] and [Extent1].[CreationDate] = [Extent2].MaxDate

标签： sqlsql-servertsql

解决方案

这似乎是一个JOIN和GROUP BY查询：

select h.Id, h.Imei, h.CreationDate, h.DeviceId,
       sum(cast(aa.value as int)) as num_enrolled
from histories h join
     applications a
     on a.DeviceHistoryId = h.id join
     ApplicationAttributes aa
     on aa.DeviceApplicationId = a.id and
        aa.key = 'EnrolledTotal'
group by h.Id, h.Imei, h.CreationDate, h.DeviceId;

推荐阅读