我正在寻找一种方法来获取使用 Sql Server 2019 的任意 sql 查询返回的列的系统名称。以下是一些示例查询和所需的结果：
company: id, name
employee: id, name, companyId
select * from company
| tableName | columnName |
company id
company name
select id as whatever from company
| tableName | columnName |
company id
select e.id, e.name, c.name from employee e join company c on e.companyId = c.id
| tableName | columnName |
employee id
employee name
company name
我无法控制输入查询，它们由最终用户提供。
我有一个缓慢的查询，我正在尝试调整。DB（mssql）中的表：
Users, ChatGroups, ChatMessages, ChatGroupChannels,ChatMessageSeenFast查询：
WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
SELECT (
select count(*)
from ChatMessages m
left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)
) as unseenCount, task.flag as taskFlag, task.note as taskNote, s.id as id, s.seen as seenByReader, MAX(s.date) as seenByReaderDate, s.textFlags as messageTextFlags, MAX(s.date) as messageSentDate, s.text as messageText, s.userId as messageSenderId, s.type as messageType, s.recipientId as messageRecipientId, s.recipientType as messageRecipientType, g.name as groupName, g.imageMip64x64 as groupImageMip64, g.id as groupId, g.groupType as groupType, g.groupFlag as groupFlag, u.profilePictureMip64 as mbyOtherUserProfilePicMip64, u.name as mbyOtherUserName, uu.name as senderName
FROM messages_ranked s
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by task.flag, task.note, s.id, s.seen, s.textFlags, s.text, s.userId, s.type, s.recipientId, s.recipientType, g.name, g.imageMip64x64, g.id, g.groupType, g.groupFlag, u.profilePictureMip64, u.name, uu.name
ORDER BY MAX(s.date) DESC
OFFSET @skip ROWS
FETCH NEXT @take ROWS ONLY
打破它：
我的第一步是获得对ChatGroup - LastMessageInThatGroup。我正在使用 CTE 来做到这一点：
WITH messages_ranked AS( SELECT p.*, ROW_NUMBER() OVER (PARTITION BY p.RecipientId ORDER BY p.dATE DESC) AS seqnum FROM ChatMessages p JOIN ChatGroupMemberships as g ON (p.recipientId = g.groupId and g.deleted <> 'true') WHERE g.userId = @userId)
select g.id as ChatGroupId, s.id as LastMessageInThisGroupId
FROM messages_ranked s
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
WHERE seqnum = 1 and (u.id <> @userId or g.groupType > 0)
group by s.id, g.id
ORDER BY MAX(s.date) DESC
这意味着给定用户是成员的任何组中的最后一条消息的 ID 为 86823，相应组的 ID 为 6901。
解释一下(u.id <> @userId or g.groupType > 0)- 聊天组有以下 groupTypes：
group.coreMemberId1是我们的用户或其他成员。group.coreMemberId2是他们中的另一个。group.coreMemberId1都group.coreMemberId2等于创建此群组的用户的 ID。我的第二步是加入其他表以获取一些补充信息：
join ChatGroups g on s.recipientId = g.id
join Users u on u.id = g.coreMemberId1 or u.id = g.coreMemberId2
join Users uu on uu.id = s.userId
left join ChatGroupTasks task on task.groupId = g.Id and task.userId = @userId
到目前为止，一切都很好。我的问题在于最后一步 - 获取我们的用户在所有对话渠道中的未读消息计数：
SELECT (
select count(*)
from ChatMessages m
left join ChatGroupChannels ch on ch.groupId = s.recipientId and ch.id = m.channelId
left join ChatMessageSeenFast f on f.userId = @userId and f.groupId = s.recipientId and f.channelId = ISNULL(ch.Id, 0)
where m.recipientId = s.recipientId and m.channelId = ISNULL(ch.Id, 0) and m.id > ISNULL(f.lastSeenMsgId, 0)
) as unseenCount
一个组可以有任意数量的通道。为了节省空间，在上面的查询中为每个聊天组“存在”一个“虚拟”频道（称为 ID = 0）。由于大多数用户不使用频道，因此默认情况下为每个组创建一个频道对我来说是一种资源浪费。因此，默认情况下，所有消息都写入此特殊通道。那应该解释ISNULL(ch.Id, 0)
ChatMessageSeenFast持有以下信息：
groupId - userId - channelId (can be null) - lastMessageSeenId - lastUpdateDatetime
这一步背后的逻辑是：
为了计算所有未见消息的数量，我将计算每个频道的所有未见消息的数量，然后进行总结。
当我s.recipientId从子查询中使用它时，它会大大降低整个事情的速度。将突出显示的分支添加到执行计划中：
在频道存在之前，我可以通过简单地加入ChatMessageSeenFast分配给对话中的用户来获得未见消息的数量，但现在这将只为一个（选定的）频道产生未见消息。
我已经创建了建议我的 mssms 的索引，它加快了执行速度，但我仍然对处理查询所需的内存和时间不满意。我怎样才能加快速度？
为了将整个问题放在一个上下文中，我使用这些数据在我的网络应用程序中创建对话列表：
