PostgreSQL不同不起作用 [英] postgresql distinct not working
问题描述
我正在使用以下代码从数据库中获取价值:
I am using the following code for getting value from the database:
但是当我编写这段代码(测试以查看问题出在哪里..)时,我注意到查询没有从数据库中获取不同的值:这是查询
选择"distinct(ca.id)"作为"id","acc.name"作为"accName","pIsu.name"作为"pareentIsu","sIsu.name"作为"subIsu",dv.domain_value作为contractType,acc.id作为accId,dvct.domain_value作为contractstatus
来自contracts_account的ca大约在ca.parent_isu_fk上加入business_unit pIsu = pIsu.id
在ca.sub_isu_fk上左加入business_unit sIsu = sIsu.id
在ca.account_fk上左加入business_unit acc = acc.id
con.contracts_account_fk = ca.id
上的左加盟合约condv.id = con.contract_type_fk上的左连接domain_values dv
dvct.id = con.contract_status_fk上的左联接domain_values dvct
其中ca.id不为null,并且按(ca.id)以(4466079)顺序con.contract_type_fk
But when i wrote this code (testing as to see where the problem is..) i noticed the query is not fetching the distinct value out from the database:
Here is the query
select distinct(ca.id)as id, acc.name as accName,pIsu.name as pareentIsu, sIsu.name as subIsu ,
dv.domain_value as contractType,acc.id as accId,dvct.domain_value as contractstatus
from contracts_account ca left join business_unit pIsu on ca.parent_isu_fk = pIsu.id
left join business_unit sIsu on ca.sub_isu_fk = sIsu.id
left join business_unit acc on ca.account_fk = acc.id
left join contracts con on con.contracts_account_fk = ca.id
left join domain_values dv on dv.id = con.contract_type_fk
left join domain_values dvct on dvct.id = con.contract_status_fk
where ca.id is not null and con.contract_type_fk in ( 4466079 ) order by ca.id
此查询只是不返回我'不同的ID '
我究竟做错了什么?我正在使用postgres 8.2
This query is simply not returning me 'Distinct Id'
What am i doing wrong?
I am using postgres 8.2
推荐答案
您的查询被解释为:
select distinct (ca.id) as id, acc.name as accName, . . .
这是标准的 distinct
语句.如果您只希望每个 ca.id
包含第一行的值(基于 order by
),则可以使用 distinct on
:
This is a standard distinct
statement. If you want just one row per ca.id
with values from the first row (based on the order by
), then use distinct on
:
select distinct on (ca.id) ca.id, acc.name as accName, . . .
这篇关于PostgreSQL不同不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!