sql-server - 在没有 LOOP 的 SQL 中使用 join 语句进行动态查询
问题描述
我有以下元数据,它提供了有关数据库中表层次结构的信息:
通过循环,我可以构造以下 sql 语句。还有另一种不用循环的巧妙方法吗?
SELECT
SubClassCode,
ClassCode,
DeptCode,
DivisionCode INTO tempTable
FROM tblSBDivisionMaster T1
JOIN tblSBDepartmentMaster T2
ON T2.ParentID = T1.DivisionID
JOIN tblSBClassMaster T3
ON T3.ParentID = T2.DeptID
JOIN tblSBSubClassMaster T4
ON T4.ParentID = T3.ClassID
带有循环示例:
declare @merchandiseHierCount as int ,@nextrowscount as int,@join as varchar(1000),@firstTable as varchar(200),@codeColumns as varchar(1000)
SELECT
@codeColumns = COALESCE(@codeColumns + ',', '') + MasterCodeColumn
FROM tblsbhierarchy
WHERE HierarchyType = 1
ORDER BY HierLevel;
SET @merchandiseHierCount = (SELECT
COUNT(*)
FROM tblsbhierarchy
WHERE HierarchyType = 1)
select @firstTable = MasterTableName FROM tblsbhierarchy
WHERE HierarchyType = 1 and HierLevel =1
set @nextrowscount =1
WHILE (@nextrowscount <= @merchandiseHierCount)
begin
declare @tableName as varchar(100),@heir as varchar(20),@IdColumn as varchar(100)
select @tableName = MasterTableName,@heir= HierLevel,@IdColumn = MasterKeyColumn from tblsbhierarchy where HierLevel = @nextrowscount and HierarchyType=1
if @nextrowscount >1
begin
SET @join = (SELECT
CONCAT(@join,' join ' + @tableName + ' T' + @heir + ' on '
+' T'+ @heir + '.' + @IdColumn + ' = T' + cast((@heir-1) as VARCHAR) + '.ParentId'))
end
set @nextrowscount = @nextrowscount+1
end
set @join = 'Select '+ @codeColumns+ ' into temptable From ' + @firstTable +' T1 ' + @join
select @join
最终查询,@Squirrel 对查询稍作修改。我请求的查询有小错误。所以我对最终结果进行了更正。
declare @select nvarchar(max),
@from nvarchar(max),
@sql nvarchar(max)
;
WITH tbl
AS
(SELECT
HierLevel,MasterTableName,MasterKeyColumn,MasterCodeColumn
,tbl = 'T' + CONVERT(VARCHAR(5), HierLevel)
,parent = 'T' + CONVERT(VARCHAR(5), LAG(HierLevel) OVER (ORDER BY HierLevel))
,parentId = lag(MasterKeyColumn) over( ORDER by HierLevel)
FROM tblsbhierarchy
WHERE HierarchyType = 1)
SELECT
@select = ISNULL(@select + ', ', 'SELECT ')
+ tbl + '.' + MasterCodeColumn
,@from = ISNULL(@from, '')
+
CASE
WHEN HierLevel = 1 THEN 'INTO temptable' + CHAR(13)
ELSE ''
END
+
CASE
WHEN HierLevel = 1 THEN 'FROM '
ELSE 'JOIN '
END
+ MasterTableName
+ ' AS ' + tbl + CHAR(13)
+
CASE
WHEN HierLevel <> 1 THEN CHAR(9) + 'ON ' + tbl + '.ParentId' + ' = ' + parent + '.'+parentId + CHAR(13)
ELSE ''
END
FROM tbl
ORDER BY HierLevel
SELECT
@sql = @select + CHAR(13) + @from
print @sql
解决方案
这将为您提供格式良好的查询。基本上它使用字符串连接并检查 HierLevel。如果 level = 1 或其他,则采取相应措施。char(13)
是回车,所以当你执行 aPRINT
时,你会得到一个格式良好的可读查询
declare @select nvarchar(max),
@from nvarchar(max),
@sql nvarchar(max)
; with tbl as
(
select *,
tbl = 'T' + convert(varchar(5), HierLevel),
parent = 'T' + convert(varchar(5), LAG(HierLevel) OVER(ORDER BY HierLevel))
from tblsbhierarchy
where HierarchyType = 1
)
select @select = isnull(@select + ', ', 'SELECT ')
+ tbl + '.' + MasterCodeColumn,
@from = isnull(@from, '')
+ case when HierLevel = 1 then 'INTO temptable' + char(13) else '' end
+ case when HierLevel = 1 then 'FROM ' else 'JOIN ' end
+ MasterTableName
+ ' AS ' + tbl + char(13)
+ case when HierLevel <> 1
then char(9) + 'ON ' + tbl + '.' + MasterKeyColumn + ' = ' + parent + '.ParentId' + char(13)
else ''
end
from tbl
order by HierLevel
select @sql = @select + char(13) + @from
print @sql
/* RESULT
SELECT T1.DivisionCode, T2.DeptCode, T3.ClassCode, T4.SubClassCode
INTO temptable
FROM tblSBDivisionMaster AS T1
JOIN tblSBDepartmentMaster AS T2
ON T2.DeptID = T1.ParentId
JOIN tblSBClassMaster AS T3
ON T3.ClassID = T2.ParentId
JOIN tblSBSubClassMaster AS T4
ON T4.SubClassID = T3.ParentId
*/
推荐阅读
- r - 锁定环境但不锁定 .Random.seed
- python - Python散点图:如何使用与颜色周期具有相同颜色的颜色图
- .htaccess - .htaccess 来自旧休息端点新端点的重写请求
- android - 导航架构组件 singleTask 或 singleInstance 在返回堆栈中
- sql-server - 无法使用不同的服务器版本号运行 .sql 脚本
- java - 如何在 Spring Boot API 中实现 RabbitMQ 消费者部分(接收器)
- java - 如何在休息响应中归档的 json 文本中包含超链接
- php - 如何将当前时间与 PHP 表中提到的时间进行比较,以确定活动/过期/即将到来?
- bash - 如何阻止 wget 顺序下载在 url 中包含括号?
- angular - Angular 7 - 仅在选中复选框时验证输入字段