I want to match same type of SQL Queries with different condition values,
我想匹配具有不同条件值的相同类型的SQL查询，
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).
除了WHERE条件（20,40,30和50）中的值之外，上述两个查询都是相同的。我想识别这样的查询。它也应该与HAVING一起使用。它应该适用于条件中的任何值类型（int，varchar，date等）。
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.
基本上我想写一个C＃函数，我可以传递2个查询，如果除了排除条件中的值之外两个查询都相同，它应该返回true。
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.
当我传递第一个和第二个查询时它应该返回true，但是对于第二个和第三个查询则返回false
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?
是否可以使用SqlScriptDom执行此操作？怎么样？我使用SqlScriptDom从SQL查询中获取表名，但是如何获取参数？
1 个解决方案
#1
1
Okay, I don't mean to pick on your language but I think it's kind of important here. The queries in your example don't have parameters. They have exclusion criteria in a WHERE clause. It sounds like what you're trying to do is compare the text of two queries for everything except the WHERE clause. ANSI SQL and T-SQL both follow the same convention that in a SELECT query the WHERE clause comes after the FROM clause and before any GROUP BY, HAVING or ORDER BY clause. So you could pull it out and compare it that way if you were going to just analyze the text of the code. One issue you might think about though is that SQL often provides subtly different ways of accomplishing the same thing. For example, if in your examples, you had <= and >= instead of < and > you could use the BETWEEN operator.
好吧，我不是故意选择你的语言，但我认为这在这里很重要。示例中的查询没有参数。它们在WHERE子句中有排除标准。听起来你要做的就是比较除WHERE子句之外的所有内容的两个查询的文本。 ANSI SQL和T-SQL都遵循相同的约定，即在SELECT查询中，WHERE子句位于FROM子句之后和任何GROUP BY，HAVING或ORDER BY子句之前。因此，如果您只是分析代码的文本，那么您可以将其拉出来并进行比较。您可能会想到的一个问题是，SQL通常提供了完全不同的方法来完成相同的事情。例如，如果在您的示例中，您有<=和> =而不是 <和> ，则可以使用BETWEEN运算符。
I think you probably could use the SqlScriptDom to do what you want but I'm not good enough with that to help really.
我想你可能会使用SqlScriptDom来做你想做的事情，但我还不够好，以帮助你。
#1
1
Okay, I don't mean to pick on your language but I think it's kind of important here. The queries in your example don't have parameters. They have exclusion criteria in a WHERE clause. It sounds like what you're trying to do is compare the text of two queries for everything except the WHERE clause. ANSI SQL and T-SQL both follow the same convention that in a SELECT query the WHERE clause comes after the FROM clause and before any GROUP BY, HAVING or ORDER BY clause. So you could pull it out and compare it that way if you were going to just analyze the text of the code. One issue you might think about though is that SQL often provides subtly different ways of accomplishing the same thing. For example, if in your examples, you had <= and >= instead of < and > you could use the BETWEEN operator.
好吧，我不是故意选择你的语言，但我认为这在这里很重要。示例中的查询没有参数。它们在WHERE子句中有排除标准。听起来你要做的就是比较除WHERE子句之外的所有内容的两个查询的文本。 ANSI SQL和T-SQL都遵循相同的约定，即在SELECT查询中，WHERE子句位于FROM子句之后和任何GROUP BY，HAVING或ORDER BY子句之前。因此，如果您只是分析代码的文本，那么您可以将其拉出来并进行比较。您可能会想到的一个问题是，SQL通常提供了完全不同的方法来完成相同的事情。例如，如果在您的示例中，您有<=和> =而不是 <和> ，则可以使用BETWEEN运算符。
I think you probably could use the SqlScriptDom to do what you want but I'm not good enough with that to help really.
我想你可能会使用SqlScriptDom来做你想做的事情，但我还不够好，以帮助你。