• ICriteria crit =session.CreateCriteria(typeof(User)); DetachedCriteriauserIdsForRoles = DetachedCriteria.For(typeof(User)) .SetProjection(Projections.Distinct(Projections.Property("I

ICriteria crit = session.CreateCriteria(typeof(User));

DetachedCriteria userIdsForRoles = DetachedCriteria.For(typeof(User))
.SetProjection(Projections.Distinct(Projections.Property("Id")))
.CreateCriteria("Roles")


展开全文
• SQL Subquery can be used inside of WHERE, SELECT,FROM 1.when subquery is used inside WHERE, it returns a column 2.when subquery is used inside FROM, it returns a table 3.when subquery is used inside ...
SQL Subquery
can be used inside of WHERE, SELECT,FROM 在嵌套查询是，我们经常会使用到exists、in、any、all等
1.when subquery is used inside WHERE, it returns a column
find the name of all salesmen who had more than one customer: (correlated subquery)
SELECT salesman_id,name
FROM salesman AS s
WHERE 1 <
(SELECT COUNT(*)
FROM customer AS c
WHERE c.salesman_id=s.salesman_id);

2.when subquery is used inside FROM, it returns a table
3.when subquery is used inside SELECT, it returns a single value
展开全文
• 不知道什么时候用Subquery 先上题： For each instrument, show its type, maker, the owner’s name, the corresponding orchestra name, and the number of concerts (name this column as concert_number) in ...
不知道什么时候用Subquery
先上题： For each instrument, show its type, maker, the owner’s name, the corresponding orchestra name, and the number of concerts (name this column as concert_number) in which the owner played from 2013 to 2016. Take into consideration only instruments produced in 2013 or earlier.
SELECT instruments.type, instruments.maker, members.name, orchestras.name, (SELECT COUNT (concerts.orchestra_id) FROM concerts WHERE concerts.year BETWEEN 2013 AND 2016 AND concerts.orchestra_id=orchestras.id) AS concert_number FROM instruments JOIN members ON members.id = owner_id JOIN orchestras ON orchestras.id = members.orchestra_id WHERE instruments.production_year<=2013;
为什么不用group by 来计算 number of concerts ？
we have a subquery in the SELECT clause, and we are counting number of concerts in the subquery itself. The way this subquery is running is, it is adding one value for each row(returned by the main query) and that value is same as the count of concerts performed between year 2013 and 2016 by that respective orchestra, that’s why you have c.orchestra_id=o.id.
例题： For each orchestra, show a report consisting of:
Name of the orchestra as the name column. Country/Countries in which the orchestra played as the country column. Number of concerts in this country as the concerts_no column. Average rating of the concerts played by this orchestra in this country as the avg_rating column. Show only the rows of the report for which the orchestra played more than once in a given country.
select o.name, cc.country, cc.concerts_no, cc.avg_rating from orchestras o join (select c.orchestra_id, c.country, count(c.id) as concerts_no, avg(rating) as avg_rating from concerts c group by c.country, c.orchestra_id having count(c.id)>1) as cc on o.id=cc.orchestra_id group by o.name, cc.country, cc.concerts_no, cc.avg_rating
用两个group by嵌套
展开全文
• Scalar Subquery Expressions A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list ....

Scalar Subquery Expressions
A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, then the value of the scalar subquery expression is NULL. If the subquery returns more than one row, then Oracle returns an error.
You can use a scalar subquery expression in most syntax that calls for an expression (expr). In all cases, a scalar subquery must be enclosed in its own parentheses, even if its syntactic location already positions it within parentheses (for example, when the scalar subquery is used as the argument to a built-in function).
Scalar subqueries are not valid expressions in the following places:
As default values for columns  As hash expressions for clusters  In the RETURNING clause of DML statements  As the basis of a function-based index  In CHECK constraints  In GROUP BY clauses  In statements that are unrelated to queries, such as CREATE PROFILE
关于scalar subquery expressions的ocp考题：
14、Which three are true about scalar subquery expressions?
A.A scalar subquery expression that returns zero rows evaluates to zoro
B.They cannot be used in the values clause of an insert statement*
C.They can be nested.
D.A scalar subquery expression that returns zero rows evaluates to null.
E.They cannot be used in group by clauses.
F.They can be used as default values for columns in a create table statement.
20、Which two are true about scalar subquery expressions?
A.You cannot correlate them with a table in the parent statement
B.You can use them as a default value for a column.
C..You must enclose them in parentheses.
D.They can return at most one row.
E.They can return two columns.

展开全文
• 1.定义 By default, if Oracle cannot unnest a subquery it postpones executes of that subquery to the en...
• Summary: in this tutorial, we will show you how to use theMySQL subqueryto write complex queries and explain the correlated subquery concept. A MySQL subquery is a query that is nested inside anoth.....
• 1.定义： The purpose of subquery unnesting is to inject semi- (IN, EXISTS), anti-join (NOT IN, NOT EXISTS),...
• <p>What I tried to do was pass IDs of the smaller table as subquery <pre><code>sql SELECT ou.* FROM online_users ou INNER JOIN user_attributes ua on ou.id = ua.user_id and ou.organization_id =...
• SQL Correlated Subquery WHEN to use : 1.when asking negative data questions https://learnsql.com/blog/correlated-sql-subquery-5-minutes/ 2. ...
• semijoin链接进行subquery unnesting..pdf
• 当我们在 WHERE 子句或 WHERE 子句或 HAVING 子句中插入另一个 SQL 语句时，我们就有一个 subquery 的架构。 Subquery 的作用是什么呢？第一，它可以被用来连接表格。另外，有的时候 subquery 是唯一能够连接两个...
• hive报错unsupport subquery expression表示不支持子查询，可以改用left join方式
• hint UNNEST 可以提示CBO进行Subquery Unnesting.pdf
• Scalar Subquery Expressions A scalar subquery expression is a subquery that returns exactly one column value from one row. The value of the scalar subquery expression is the value of the select list ....
• Failed , semanticException [error 10249] Unsupported subquery expression : Subquery expression refers to both parent and subquery expression and is not a valid join condition
• Use OuterRef when a queryset in a Subquery needs to refer to a field from the outer query. 由上述文字可知，OuterRef是用于子查询语句的输出查询。 举个例子，假设由两个模型类如下： class A(models.Model):...
• Laravel开发-eloquent-subquery-magic 提供使用许多子查询功能（如FromSubQuery或LeftJoinSubQuery）的雄辩扩展
• ## SQL的SubQuery

千次阅读 2014-07-04 10:57:18
SQL 语句时，我们就有一个 subquery 的架构。 Subquery 的作用是什么呢？第一，它可以被用来连接表格。另外，有的时候 subquery 是唯一能够连接两个表格的方式。 Subquery 的语法如下： SELECT "栏位1...
• <div><p>parameterized top () in subquery leeds to performance issue </p><p>该提问来源于开源项目：sqlkata/querybuilder</p></div>
• dependent subquery 需要引起最大关注，可谓是性能杀手，无数开发着已经踩坑。
• SQL---关联子查询（correlated subquery）.pdf
• 面试碰到这样一道SQL题: 数据库结构如图 要求取出 1. Country Name ...当时一想后面题还多着呢，　祭出Ｓｕｂｑｕｅｒｙ　三下五除二，　搞定，　 代码 SELECT Country.Name, (SELECT ...
• <p>A straw proposal that involves no frontend work is to simply add logging of events and physical plans for all subqueries and then add a subquery parameter to the frontend. I'm going to start ...
• 子查询（Subquery）是指出现在其他SQL语句内的SELECT子句。 例如： select * from t1 where col1=(select col2 from t2); 其中select * from t1，称为Outer Query/Outer Statement(外层查询) select col2 from ...
• ## SQL Subquery的用法

千次阅读 2015-03-07 11:53:27
当我们在 WHERE 子句或 WHERE 子句或 HAVING 子句中插入另一个 SQL 语句时，我们就有一个 subquery 的架构。 Subquery 的作用是什么呢？第一，它可以被用来连接表格。另外，有的时候 subquery 是唯一能够连接...
• ## SubQuery or Join?

千次阅读 2012-07-31 11:07:33
很多开发都喜欢用Subquery而不喜欢用Join,对于他们来讲Subquery更容易实现。但是很多情况下用Join性能要比用Subquery好。   首先我们看一下Subquery: 子查询也称为内部查询或内部选择，而包含子查询的语句也称为...

...