ANSI Sql查询强制返回0条记录

I'm looking for an ANSI-SQL method to do a Select query without returning any record, but fill a TDataSet's Fields structure.

我正在寻找ANSI-SQL方法来执行Select查询而不返回任何记录，但填充了TDataSet的Fields结构。

The method I found is by adding a "where 1=0" in any query, for example:

我找到的方法是在任何查询中添加“where 1 = 0”，例如：

Select Id, name, province
from customers
where 1=0

This is a fairly trivial example, it turns a little more complicated when I have to work with queries entered by the user, then parse them, remove the where clause if it already has one, and replace by "1=0".

这是一个相当简单的例子，当我必须处理用户输入的查询，然后解析它们，删除where子句（如果已有）时，它会变得更复杂，并替换为“1 = 0”。

If the last clause in the user-entered query is the where clause, then there's no problem at all, but what about more complicated queries like this:

如果用户输入的查询中的最后一个子句是where子句，那么根本没有问题，但是更复杂的查询如下：

select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname

By using the "where 1=0" method, the only way to insert it in the previous example is by having a rather powerful SQL parser (remember the user can enter complex queries, including Subqueries, and all that), who can understand where to include this string.

通过使用“where 1 = 0”方法，在前一个示例中插入它的唯一方法是使用一个相当强大的SQL解析器（记住用户可以输入复杂查询，包括子查询，以及所有这些），谁能理解哪里包括这个字符串。

Does anyone knows a better way to do this? I cannot use "limit 1" because it must be in an ANSI way.

有谁知道更好的方法吗？我不能使用“限制1”，因为它必须采用ANSI方式。

5 个解决方案

#1

What about adding your own SELECT around the user's SELECT?

如何在用户的SELECT周围添加自己的SELECT？

SELECT * FROM (
select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname
) x
WHERE 0=1

EDIT: ORDER BY would not work with that solution, but since you get no rows, you could try to remove that from the query when necessary.

编辑：ORDER BY不适用于该解决方案，但由于没有行，您可以尝试在必要时从查询中删除它。

5 个解决方案

#1

更多相关文章

随机推荐