如何识别查询语句中有一个子查询

Question

开发一个用于SQL训练的C#项目，并根据每个主题的培训提供不同的练习。其中一个练习是使用子查询编写查询。 需要评估用户是否在查询状态中使用/实现了子查询。

问：编写一个 sql 查询以显示 Sales.SalesOrderDetail 表中的 SalesOrderID、LineTotal、Average LineTotal，使用 Sub 查询

Select SalesOrderID,LineTotal [LineTotal],
(Select AVG(LineTotal) from Sales.SalesOrderDetail) as [AverageLineTotal] 
from Sales.SalesOrderDetail 

[AverageLineTotal]是一个子查询。

我们可以通过任何方式识别它吗？ 喜欢执行计划 或者 SP 来标识它 语句中有一个子查询

有没有办法通过执行计划来识别它？

Answer 1

如果这是一个 c# 项目，您可以使用正则表达式解析查询以查找查询是否包含(选择 {任何其他文本}(。

public static void Main()
{
    var sql = @"Select SalesOrderID,LineTotal [LineTotal],(Select AVG(LineTotal) from Sales.SalesOrderDetail) as [AverageLineTotal] from Sales.SalesOrderDetail";
    Console.WriteLine(DoesSqlContainSubquery(sql));
}
public bool DoesSqlContainSubquery(string sql)
{
    var regexTest = new Regex(@"( *Select .*)", RegexOptions.IgnoreCase);
    var containsSubquery = regexTest.IsMatch(sql);
    return containsSubquery;
}

Answer 2

由于过多

的 T-SQL 构造和选项，分析即席脚本本质上很复杂。话虽如此，针对目标用例的一种健壮方法是使用 Microsoft.SqlServer.TransactSql.ScriptDom 解析脚本。

下面是一个示例 PowerShell 脚本，它使用官方 Microsoft Dacfx NuGet 包中的脚本 DOM 程序集，如果需要，可以下载并提取它。

# Add TSqlScript DOM assembly reference, downloading and extracting to the specified location if needed
$scriptDomAssemblyPath = "C:TempMicrosoft.SqlServer.TransactSql.ScriptDom.dll"
$scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/150.4384.2"
if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
    $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
    if ($response.StatusCode -ne 200) {
        throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
    }
    $tempZipFilePath = "$([System.IO.Path]::GetTempPath())/$([System.IO.Path]::GetRandomFileName()).zip"
    [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
    $response.BaseResponse.Dispose()
    $tempUnzipFolderPath = "$([System.IO.Path]::GetTempPath())/$([System.IO.Path]::GetRandomFileName())"
    Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
    $tempZipFilePath | Remove-Item
    Move-Item "$tempUnzipFolderPathlibnet46Microsoft.SqlServer.TransactSql.ScriptDom.dll" "$scriptDomAssemblyPath"
    $tempUnzipFolderPath | Remove-Item -Recurse
}
Add-Type -Path $scriptDomAssemblyPath
# script to be parsed
$scriptText = @"
Select SalesOrderID,LineTotal [LineTotal],
(Select AVG(LineTotal) from Sales.SalesOrderDetail) as [AverageLineTotal] 
from Sales.SalesOrderDetail
"@
#parse script
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$scriptReader = New-Object System.IO.StringReader($scriptText)
$script = $parser.Parse($scriptReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0) {
    throw "$($parseErrors.Count) parsing errors"
}
# sanity check for expected SELECT query
if(($script.Batches.Count -ne 1) -or ($script.Batches[0].Statements.Count -ne 1) -or ($script.Batches[0].Statements[0].QueryExpression -eq $null)) {
    throw "script with single SELECT statement expected"
}
# find scalar subquery expression in select list
$subQueryFound = $false
foreach($selectElement in $script.Batches[0].Statements[0].QueryExpression.SelectElements) {
    if($selectElement.Expression.ToString() -eq "Microsoft.SqlServer.TransactSql.ScriptDom.ScalarSubquery") {
        $subQueryFound = $true
        break
    }
}
# show if subquery was used
if($subQueryFound) {
    Write-Host "A subquery is used"
}
else {
    Write-Host "A subquery is not used"
}