我目前面临一点墙,我正在尝试从两个不同的表中获取信息,并将它们与按计数记录的信息并排显示。一个是第一个表中具有特定值的总金额,第二个是不同列的值。
表 A.当前与 B.id 相同
但是,我想要的信息是表 A 中当前的总命中数,并显示 B.name 的信息而不是 b.id
我已经尝试了很多事情,到目前为止,我一直遇到一个问题,它说来自不正确或组不正确:
select
count(pk.id) as "Total",
lc.fullyqualifiedname as "Name"
from
tsu pk,
location lc
where
pk.locationid = lc.id
group by
lc.id
having
lc.id = :ID;
从上面的代码中,我得到错误00923
如果有人可以帮助我 - 我哪里做错了?
我最终需要
的是第 1 列 - 位置为 lc.id 的点击总数 ( count(*( ( 第 2 列 - lc.id 表示为完全限定的名称正在显示的名称。提供黑貂表中不同列的值。
编辑:
select count(pk.id) as "Total",
lc.fullyqualifiedname as "Name"
from tsu pk,
location lc
where pk.locationid = lc.id
group by lc.id
having lc.id = :ID;
此脚本有效,但它显示表 lc 中的第 1 列,我希望从 lc 显示列名。
-- 使用应答脚本解决了问题。
对于单个lc.id
,无需使用GROUP BY
:
select count(pk.id) as "Total",
min(lc.fullyqualifiedname) as "Name" -- here agg function
from tsu pk
join location lc -- join syntax is preferred
on pk.locationid = lc.id
where lc.id = :ID; -- filtering with where
或者:
select count(pk.id) as "Total",
min(lc.fullyqualifiedname) as "Name",
lc.id
from tsu pk
join location lc
on pk.locationid = lc.id
---where lc.id IN (...)
group by lc.id;
问题是lc.fullyqualifiedname
出现在select
列表中,而不是group by
子句中,因此ORA-00979: not a GROUP BY expression
。
select count(pk.id) as "Total",
lc.fullyqualifiedname as "Name"
from tsu pk,
location lc
where pk.locationid = lc.id
and lc.id = :id
group by lc.fullyqualifiedname;
或者更好的是,
select count(*) as "Total",
lc.fullyqualifiedname as "Name"
from tsu pk
join location lc on lc.id = pk.locationid
where lc.id = 1
group by lc.fullyqualifiedname;
如果这不是您需要的,请包括一些示例数据和预期结果。