我有以下未运行的查询:
with countf as (
select nationid, count(*) as c from customer
group by nationid
),
maxf as ( select max(nationid) from customer )
select c.customerid, c.nationid from customer c, countf cf, maxf m
where c.nationid = cf.nationid
and cf.c = m
问题似乎m
是记录而不是整数。但是,如果我将其作为子查询运行,如下所示:
cf.c = ( select max(nationid) from customer )
它按预期工作。我认为我使用的 with 语句不是预期的方式。试
cf.c in maxf
让我假设使用生成的表WITH
不应该在WHERE
子句中使用。
我知道还有其他方法可以使用all
例如获取相同的查询。我真的只对我应该如何使用 with 语句感兴趣。我只能SELECT
在以后使用它吗?
在此先感谢您的任何帮助。
这是因为条件and cf.c = m
应该如下所示
with countf as (
select nationid, count(*) as c from customer
group by nationid
),
maxf as ( select max(nationid) as max_nationid from customer )
select c.customerid, c.nationid from customer c, countf cf, maxf m
where c.nationid = cf.nationid
and cf.c = m.max_nationid
旁注:使用适当的 ANSI 样式JOIN
语法,它更具可读性,例如
select c.customerid,
c.nationid from customer c
join countf cf on c.nationid = cf.nationid
join maxf m on cf.c = m.max_nationid
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句