nhibernate 多标准 CTE 子查询
我目前有一个 MultiCriteria 查询，它根据子查询中的 id 来过滤结果
Subqueries.PropertyIn("Id", detachedCriteria)
子查询对于多条件中使用的所有查询都是相同的询问。
看起来子查询重复的sql有点难看，在我当前的情况下重复了15次。
单独查询的原因是每个查询都有不同的连接，并且不希望出现大量笛卡尔连接。
如果我手动编写 SQL，我会将重复的子查询提取到公共表表达式中
WITH XYZ AS
{
....
}
，然后子查询将是 15 个查询中 XYZ 中的 where id。
这是 sql server 特有的，替代方案是临时表或其他数据库特定功能。
关于如何改进查询的任何想法，或者我是否坚持重复子查询？
I currently have a MultiCriteria query, which filters the results based on the ids being within a sub query
Subqueries.PropertyIn("Id", detachedCriteria)
The sub query is the same for all queries used in the multicriteria query.
It seems a bit ugly looking at the sql that the sub query is repeated, in my current case 15 times.
The reason for the separate queries is each one has different joins, and don't want one massive Cartesian join.
If I was writing the sql by hand I would pull out the repeated sub query into a common table expression
WITH XYZ AS
{
....
}
and then the sub query would be where id in XYZ in the 15 queries.
This is a bit sql server specific, an alternative would be a temporary table, or other database specific feature.
Any ideas of how to improve the query, or am I stuck with the sub queries being duplicated?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
好吧，如果您要返回多个结果集，那么使用
WITH无论如何都不会帮助您，因为它只能应用于单个SELECT语句。无论如何，为了使数据层代码与数据库无关，我可能会将实际的查询逻辑推入存储过程中。这意味着您可以使用特定于引擎的功能进城，因为它全部隐藏在公共界面后面。是的，您必须为您支持的每个数据库引擎（通常很少）重新实现查询，但是您可以完全控制每个引擎上运行的内容，并且您的数据访问代码将变得干净得多。
Well, if you're returning multiple result sets, using
WITHwon't help you anyways because it can only apply to a singleSELECTstatement.In any event, to make the data layer code database-agnostic, I would probably shove the actual query logic into a stored procedure. This means you can go to town using engine-specific features because it's all hidden behind the public interface. Yes, you'll have to reimplement the query for each database engine you support (which is normally very few), but you get full control over what gets run on each engine, and your data access code will be a heck of a lot cleaner.