+-
我正在尝试在SELECT语句中使用COUNT(*)。但是,我需要重新命名它,并能够为WHERE子句引用它。
我尝试过使用AS,我尝试省略AS,因为根据oracle页面似乎没有必要:https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions032.htm
尝试将新标识符包装在引号中,但这也不起作用。
这是有效的,但是为计数提供了一个oracle生成的名称,这是不理想的,我不知道如何引用每行的计数:
SELECT
school_name,
(SELECT COUNT(*)
FROM liason_to
WHERE school_name = s.school_name)
FROM school s;
这是我尝试但不起作用的:
SELECT
school_name,
(SELECT COUNT(*) AS numLiasons
FROM liason_to
WHERE school_name = s.school_name)
FROM school s
WHERE numLiasons > 0;
它不会使列名称为“numLiasons”,并且末尾的where子句不知道numLiasons是什么,因此失败。
1
投票
投票
您可以通过在其后面添加一个名称来为列添加别名,也可以在两者之间使用关键字AS
。它与您对表格的处理基本相同。
SELECT school_name,
(SELECT count(*)
FROM liason_to l
WHERE l.school_name = s.school_name) AS numliasons
FROM school s;
或者干脆
SELECT school_name,
(SELECT count(*)
FROM liason_to l
WHERE l.school_name = s.school_name) numliasons
FROM school s;
但是你不能在WHERE
子句中使用别名(在WHERE
子句中的条件选择了记录之后发生了别名)。你必须重复这一尝试。
SELECT school_name,
(SELECT count(*)
FROM liason_to l
WHERE l.school_name = s.school_name) numliasons
FROM school s
WHERE (SELECT count(*)
FROM liason_to l
WHERE l.school_name = s.school_name) > 0;
1
投票
投票
您可以使用连接和分组来避免子查询..并且您可以将您喜欢的anme指定为别名
SELECT s.school_name, COUNT(*) as my_count
FROM school s
INNER JOIN liason_to l on s.school_name = l.school_name
GROUP BY s.school_name
使用您的代码,您只需在(子选择)列上分配别名即可
SELECT
school_name,
(SELECT COUNT(*)
FROM liason_to
WHERE school_name = s.school_name) as my_name
FROM school s;
无论如何要过滤你可以使用的聚合结果但是对于count(*)
记住这只适用于非空行,所以通常count(*)是> 0
0
投票
投票
您不能在WHERE子句中引用别名,但您可以这样做:
SELECT
t.school_name,
t.numLiasons
FROM (
SELECT
s.school_name,
(
SELECT COUNT(*)
FROM liason_to
WHERE school_name = s.school_name
) AS numLiasons
FROM school s
) t
WHERE t.numLiasons > 0;