ANSI Sql查询强制返回0条记录
我正在寻找一种 ANSI-SQL 方法来执行 Select 查询而不返回任何记录，但填充 TDataSet 的字段结构。
我发现的方法是在任何查询中添加“where 1=0”，例如：
Select Id, name, province
from customers
where 1=0
这是一个相当简单的示例，当我必须处理用户输入的查询然后解析它们时，它会变得有点复杂，如果已经有 where 子句，请删除该子句，并用“1=0”替换。
如果用户输入的查询中的最后一个子句是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
通过使用“where 1=0”方法，将其插入到where子句中的唯一方法前面的例子是通过拥有一个相当强大的 SQL 解析器（记住用户可以输入复杂的查询，包括子查询等等），谁可以理解在哪里包含这个字符串。
有谁知道更好的方法来做到这一点？我不能使用“limit 1”，因为它必须采用 ANSI 方式。
I'm looking for an ANSI-SQL method to do a Select query without returning any record, but fill a TDataSet's Fields structure.
The method I found is by adding a "where 1=0" in any query, for example:
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".
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:
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.
Does anyone knows a better way to do this? I cannot use "limit 1" because it must be in an ANSI way.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
在用户的 SELECT 周围添加您自己的 SELECT 怎么样？
编辑：
ORDER BY不适用于该解决方案，但由于您没有获得任何行，因此您可以在必要时尝试从查询中删除该行。What about adding your own SELECT around the user's SELECT?
EDIT:
ORDER BYwould not work with that solution, but since you get no rows, you could try to remove that from the query when necessary.供将来参考，以防人们最终有不同的目标：请注意，使 WHERE 子句成为矛盾可能会导致优化器决定根本不执行子计划。因此，如果您需要查询的一些副作用（无论是热缓存、执行过程等等），请注意。 :-)
For future reference in case people end up here with a different goal: Note that making the WHERE-clause a contradiction can cause the optimizer to decide to not execute the sub-plan at all. So if you need some side-effects of the query (be it warm a cache, execute a procedure, whatever), be advised. :-)
如果您使用 MSSQL Server，那么您可以将查询包装在
SET FMTONLY周围if your using MSSQL Server, then you can wrap your query around
SET FMTONLY在 Firebird 中，您可以“准备”语句而不是“执行”它。准备只是解析语句并返回字段列表。
In Firebird you may 'prepare' the statement instead of 'execute' it. Preparing simply parses the statement and returns the field list.
或者使用
（也许进行一些健全性检查，但您明白了 - SELECT TOP 0 将仅返回包含记录布局的元数据，而不返回记录数据）。
Or use
(perhaps with some sanity checking, but you get the idea - a SELECT TOP 0 will return only the meta data containing the record layout and no record data).