度量快速开发平台-专业、快速的软件定制快开平台

标题: SQL Server将自己的查询结果作为待查询数据子列 [打印本页]

作者: fteair 时间: 2020-2-20 17:47
标题: SQL Server将自己的查询结果作为待查询数据子列

嵌套子查询是SQL语句中比较常用的一种查询方法，开发过程中遇到查询需要将自己的某列作为待查询的数据，在参考别人的SQL语句的写法终于实现了自己需要的功能。

不太和谐查询语句如下：

SELECT DISTINCT dbo.a.ProxyID, dbo.a.account AS adminAccount, dbo.b.LevelName, dbo.a.ProfitProportion, dbo.a.totalUpScore, dbo.a.score, dbo.a.members, dbo.a.authority, dbo.a.registerDate, dbo.a.freezeState, temp.belongName

FROM dbo.a INNER JOIN dbo.b ON dbo.a.ProxyLevel = dbo.b.LevelId INNER JOIN (SELECT dbo.c.BelongsAgent, a.ProxyID, a.account AS belongName FROM dbo.a AS a INNER JOIN dbo.c ON a.ProxyID = BelongsAgent AND a.ProxyID = dbo.c.BelongsAgent) AS temp ON dbo.a.belongsAgent = temp.BelongsAgent

　　精简版的查询语句如下：（省略了a表的一些不重要字段）

　　SELECT DISTINCT dbo.a.BelongAgentId, dbo.a.Account, dbo.b.LevelName, temp.BelongName FROM dbo.a INNER JOIN (SELECT a.Account AS BelongName, a.ProxyId FROM dbo.a AS a INNER JOIN (SELECT BelongAgentId AS id FROM dbo.a AS a) AS t ON a.ProxyId = t.id) AS temp ON dbo.a.BelongAgentId = temp.ProxyId INNER JOIN dbo.b ON dbo.a.ProxyLevel = dbo.b.LevelId

作者: fteair 时间: 2020-2-21 17:39

作者: 张兴康 时间: 2020-2-22 13:46

作者: fteair 时间: 2020-2-22 17:42

作者: fteair 时间: 2020-2-22 17:43

欢迎光临度量快速开发平台-专业、快速的软件定制快开平台 (http://bbs.delit.cn/)