如何对任意查询(可能包含排序)执行计数

本文关键字：排序 包含 执行 任意 查询 | 更新日期: 2023-09-27 18:12:15

我的任务是更新我们内部使用的内部框架。框架做的一件事是你传递一个查询，它会返回查询中包含的行数(框架大量使用datareader，所以我们需要在UI事情之前得到总数)。

需要计数的查询可能因项目而异(sol注入不是问题，查询不是来自用户输入，只是硬编码来自另一个程序员，当他们为他们的项目使用框架时)，我被告知，仅仅让程序员为计数编写第二个查询是不可接受的。

目前的解决方案是这样做(我没有写这个，我只是被告知要修复它)。

//executes query and returns record count
public static int RecordCount(string SqlQuery, string ConnectionString, bool SuppressError = false)
{
    //SplitLeft is just myString.Substring(0, myString.IndexOf(pattern)) with some error checking. and InStr is just a wrapper for IndexOf.
    //remove order by clause (breaks count(*))
    if (Str.InStr(0, SqlQuery.ToLower(), " order by ") > -1)
        SqlQuery = Str.SplitLeft(SqlQuery.ToLower(), " order by ");
    try
    {
        //execute query
        using (SqlConnection cnSqlConnect = OpenConnection(ConnectionString, SuppressError))
        using (SqlCommand SqlCmd = new SqlCommand("select count(*) from (" + SqlQuery + ") as a", cnSqlConnect))
        {
            SqlCmd.CommandTimeout = 120;
            return (Int32)SqlCmd.ExecuteScalar();
        }
    }
    catch (Exception ex)
    {
        if (SuppressError == false)
            MessageBox.Show(ex.Message, "Sql.RecordCount()");
        return -1;
    }
}

然而，它打破了查询(再次，不是我的查询，我只需要使它工作)

select [ClientID], [Date], [Balance] 
from [Ledger] 
where Seq = (select top 1 Seq 
             from [Ledger] as l 
             where l.[ClientID] = [Ledger].[ClientID] 
             order by [Date] desc, Seq desc) 
      and Balance <> 0)

，因为它将删除order by之后的所有内容并中断查询。我想我可以从简单的字符串匹配到更复杂的解析器，但在我这样做之前，我想问一下是否有更好的方法。

UPDATE: order by子句被删除，因为如果您使用我的方法或CTE包含它，您将得到错误The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

更多细节:这个框架用于编写转换应用程序。我们编写应用程序，从客户的旧数据库中提取数据，并在客户购买我们的CRM软件时将其移动到我们的数据库格式。我们经常使用写得很差的源表，并且可能有几个gig大小。我们没有足够的资源将整个表保存在内存中，所以我们使用DataReader将数据拉出，这样所有内容就不会立即在内存中。然而，需求是包含要处理的记录总数的进度条。这个RecordCount函数用于计算进度条的最大值。它工作得相当好，唯一的障碍是，如果程序员编写转换需要对数据输出进行排序，在最外层的查询中有一个order by子句中断count(*)

部分解决方案:我在试图弄清楚它的时候想到了这个，它不会100%工作，但我认为它会比当前的解决方案更好

如果我找到一个order by子句，然后检查查询中的第一件事是否为select(并且没有Top)，我将开始的文本替换为select top 100 percent。它的工作更好，但我不是张贴这作为一个解决方案，因为我希望一个通用的解决方案。

如何对任意查询(可能包含排序)执行计数

假设您除了相当普通的select语句之外不会看到任何内容，我认为您不需要一个完整的SQL解析器来做您想要的事情。您可以合理地假设您已经获得了语法上有效的SQL。但是，您需要构建一个标记器(词法分析器)。

Transact SQL所需的词法分析非常简单。令牌列表由以下内容组成(这是我突然想到的，因为我已经有一段时间没有这样做了):

  • 空白
  • 两种注释类型:
    • --样式注释<代码><代码>//…/"风格的评论
  • 三种类型的引用文字:
    • 字符串字面值(例如，"我的字符串字面值")和
    • 引用保留字作为列名或对象名的两种方式:
      • ANSI/ISO样式，使用双引号(例如，[table])
      • Transact-SQL样式，使用方括号(例如，0x01A2F)
  • 十六进制字面值(例如，757)
  • 数字文字(如-3218、5.4、-7.6E-32或5.0m、$5.3201、order by等)
  • 单词，保留或不保留:一个unicode字母，下划线('')， 'at'符号('@')或散列('#')，后面跟着零个或多个unicode字母，十进制数字，下划线('')或@，美元或散列符号('@'，'$'或'#')。
  • 操作符，包括括号

基本上都可以用正则表达式完成。如果使用Perl，一天就可以完成，非常简单。不过，在c#中可能需要更长的时间。

我可能会将注释视为空白，并将多个空白和注释序列折叠成单个空白令牌，因为它有助于识别诸如select count(*)之类的结构。

你不需要解析器的原因是你不太关心解析树。你需要关心的是嵌套括号。所以…

  1. 一旦你得到了一个发出标记流的词法分析器，你所需要做的就是吃掉和丢弃计数开/闭括号的标记，直到你在括号深度0处看到一个'from'关键字。

  2. 将from写入StringBuilder

  3. 开始将令牌(包括with)添加到StringBuilder中，直到在括号深度0处看到'order by'。要做到这一点，您需要在词法分析器中构建一定数量的预查功能(请参阅我之前关于将空格和/或注释序列折叠成单个空格令牌的说明)

  4. 此时，您应该已经基本完成了。执行查询

指出

  1. 参数化查询可能无法工作

  2. 带有CTE和ORDER BY子句的递归查询可能会被破坏。

  3. 这将丢弃FOR子句之后的任何内容:如果查询使用查询提示，COMPUTE子句或COMPUTE BY/compute，则您的结果可能与原始查询不同(特别是使用任何UNION子句，因为它们会分解查询结果集)。

  4. 裸CC_27查询将被打破，因为像

          select c1,c2 from t1
UNION select c1,c2 from t2

    会变成

          select count(*) from t1
UNION select c1,c2 from t2

  5. 所有这些都是完全未经测试的，只是我的想法基于我多年来不得不做的奇怪的事情。

与其修改查询的现有子句，不如插入一个新的子句，INTO子句。

SELECT *
INTO #MyCountTable -- new clause to create a temp table with these records.
FROM TheTable
SELECT @@RowCount
-- or maybe this:
--SELECT COUNT(*) FROM #MyCountTable
DROP TABLE #MyCountTable

TSql查询修改似乎是一个永恒的斗争，是最近发生的事情。

你会张贴一个答案，如何做到这"正确的方式"使用IQueryable

假设你有一些任意的查询:

IQueryable<Ledger> query = myDataContext.Ledgers
  .Where(ledger => ledger.Seq ==
    myDataContext.Ledgers
      .Where(ledger2 => ledger2.ClientId == ledger.ClientId)
      .OrderByDescending(ledger2 => ledger2.Date)
      .ThenByDescending(ledger2 => ledger2.Seq)
      .Take(1).SingleOrDefault().Seq
  )
  .Where(ledger => ledger.Balance != 0);

然后您只需获得行数，不需要任何自定义方法或查询操作。

int theCount = query.Count();
//demystifying the extension method:
//int theCount = System.Linq.Queryable.Count(query);

LinqToSql将在查询文本中包含您想要的计数。

我猜您是想取消order by子句来提高性能。一般情况非常复杂，您需要完整的sql解析器来删除排序子句。

还有，你检查了

的比较性能吗?
select count(id) from ....

v/s 

select count(*) from (select id, a+b from ....)

问题是a+b需要在后面求值，本质上是执行两次查询。

如果你想要一个进度条，因为检索本身很慢，那么这是完全适得其反的，因为你将花费几乎相同的时间来估计计数。

如果应用程序足够复杂，数据可能在两次查询执行之间发生变化，那么你甚至不知道计数的可靠性。

所以:真正的答案是你不能以有效的方式获得任意查询的计数。对于一种非有效的方法，如果您的结果集是可rewindable的，那么到结果集的末尾，找出行数，然后返回到第一行。

如果您不尝试重新构建查询，而是执行以下操作:

WITH MyQuery AS (
select [ClientID], [Date], [Balance] 
from [Ledger] 
where Seq = (select top 1 Seq 
            from [Ledger] as l 
                where l.[ClientID] = [Ledger].[ClientID] 
            order by [Date] desc, Seq desc) 
      and Balance <> 0)
)
  SELECT COUNT(*) From MyQuery;

注意我还没有在SQL Server 2005上测试过，但它应该可以工作。

更新:

我们已经确认SQL Server 2005不支持CTE中的ORDER BY子句。但是，这确实适用于Oracle和其他数据库。

我根本不会编辑或尝试解析SQL，但您可能不得不使用EVIL CURSOR(不要担心，我们不会显式遍历任何内容)。这里，我将简单地将您的特殊SQL传递给一个进程，该进程将其作为游标运行，并返回游标中的行数。可能有一些可用的优化，但我保持简单，这应该适用于任何有效的选择语句(甚至cte)，您传递给它。不需要编写和调试自己的T-SQL lexer或任何东西。

create proc GetCountFromSelect (
    @SQL nvarchar(max)
)
as
begin
    set nocount on
    exec ('declare CountCursor insensitive cursor for ' + @SQL + ' for read only')
    open CountCursor
    select @@cursor_rows as RecordCount
    close CountCursor
    deallocate CountCursor
end
go
exec GetCountFromSelect '// Your SQL here'
go
相关文章：