如何匹配SQL查询? [英] How to match SQL Queries ?

问题描述

我想用不同的条件值匹配相同类型的SQL查询，

例如:

   从年龄在>以上的客户中选择* 20岁以下40
    
   从年龄在>以上的客户中选择* 30岁以下50

除了WHERE条件(20、40、30和50)中的值以外，以上两个查询都相同.我想识别这样的查询.它也应该与HAVING一起使用.它适用于条件(int，varchar，date等)中的任何值类型.

基本上，我想编写一个可以传递2个查询的C#函数，如果除了排除条件中的值之外，两个查询都相同，则应该返回true.

另一个例子:

    SELECT Employees.LastName，COUNT(Orders.OrderID)AS NumberOfOrders FROM(订单
    INNER JOIN员工
    ON Orders.EmployeeID = Employees.EmployeeID)
    GROUP BY LastName
   拥有COUNT(Orders.OrderID)> 10;

    SELECT Employees.LastName，COUNT(Orders.OrderID)AS NumberOfOrders FROM(订单
    INNER JOIN员工
    ON Orders.EmployeeID = Employees.EmployeeID)
    GROUP BY LastName
   拥有COUNT(Orders.OrderID)> 50;

    SELECT Employees.FirstName，COUNT(Orders.OrderID)AS NumberOfOrders FROM(订单
    INNER JOIN员工
    ON Orders.EmployeeID = Employees.EmployeeID)
    GROUP BY FirstName
   拥有COUNT(Orders.OrderID)> 50;

当我传递第一和第二查询时，它应该返回true，但是对于第二和第三查询则返回false.

我尝试使用正则表达式，但是如何找到参数所在的位置?可以在任何地方.

是否可以使用SqlScriptDom做到这一点?如何?我正在使用SqlScriptDom从SQL查询中获取表名，但是如何获取参数?

I want to match same type of SQL Queries with different condition values,

For example :

    SELECT * FROM Customer Where Age > 20 AND Age < 40
    
    SELECT * FROM Customer Where Age > 30 AND Age < 50

Both of the above queries are the same except the values in the WHERE condition (20, 40, 30 and 50). I want to identify such queries. It should work with HAVING as well. It should work for any value type in the condition (int, varchar, date etc).

Basically I want to write a C# function to which I can pass 2 queries and it should return true if both queries are the same except the values in the exclusion condition.

Another Example :

    SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
    INNER JOIN Employees
    ON Orders.EmployeeID=Employees.EmployeeID)
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 10;

    SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
    INNER JOIN Employees
    ON Orders.EmployeeID=Employees.EmployeeID)
    GROUP BY LastName
    HAVING COUNT(Orders.OrderID) > 50;

    SELECT Employees.FirstName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
    INNER JOIN Employees
    ON Orders.EmployeeID=Employees.EmployeeID)
    GROUP BY FirstName
    HAVING COUNT(Orders.OrderID) > 50;

When I pass 1st and 2nd queries it should return true, but false for 2nd and 3rd.

I tried with Regular Expressions, but how to find where the parameter located? It can be anywhere.

Is it possible to do it with SqlScriptDom? How? I am using SqlScriptDom to get the table names from the SQL query, but how to get parameters?

推荐答案

SQL的来源以及它们的构造方式?

Where do the SQLs come from and how are they constructed?

除非您知道SQL语句将要具有的非常特殊的子句以及可能更具体的'参数'定义，否则无法告诉参数是什么.

There would be no way to tell what the parameters are unless you know the very specific kinds of clauses that the SQL statements are going to have and, probably, a more specific definition of 'parameter'.

考虑:

选择*来自客户，其中status =有效"；和年龄> 20.

SELECT * FROM Customer Where status="Active" and Age > 20.

有效"还有一个参数?还是只有20个?

Is "Active" also a parameter? Or only 20?

我能想到的最好的办法是解析Where子句并排除所有SQL关键字，所有运算符以及您可以确定的所有内容都是列名(也可以采用table.column或database.table的格式.柱子).剩下的就是 一个字面值，从广义上讲，您可以说它是一个参数".

The best I can think of would be to parse the Where clause and exclude all SQL keywords, all operators and everything that you can determine is a column name (which may also be in the format table.column or database.table.column). Anything left would be a literal value which, at its broadest definition, you could say is a 'parameter'.

这篇关于如何匹配SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！