Question

1. links: link_id, road
2. roads: road, code
2. admin_classes: code, admin_class, priority

So, for ONE LINK_ID I have several codes and several admin_class+priority raws But in result set i need only one row for each link_id: link_id and admin_class, that refers to min priority. I try to make sql statement, like following:

select l.link_id, ac.admin_class, min(ac.priority)
from base.links as l
inner join base.admin_classes as ac
on ac.code=l.code
where ac.admin_class<>0

但是,首先,它并不奏效,并非需要。我只希望定下两栏——连接——直线和行政管理——

随后,我尝试如下:

select l.link_id, (select ac.admin_class from base.admin_classes as ac where ac.code=l.code
order by ac.priority limit 1) as admin_class from base.links as l

这是正确的,但我不知道如何增加内部选择的条件,我只需要拥有行政结构的原材料;和

I use PostgreSql 8.3

Thank you in advance.

Answer 1

I m不熟悉邮政gresql,但I m 猜测原电离板,t work becuses a min(集成功能,无group by

然而,如果你不需要<条码>min(),则应当做到如下:

select l.link_id, ac.admin_class
from base.links as l
inner join base.admin_classes as ac
        on ac.code=l.code
       and ac.admin_class<>0

否则,将尝试:

select l.link_id, ac.admin_class, min(ac.priority)
from base.links as l
inner join base.admin_classes as ac
        on ac.code=l.code
       and ac.admin_class<>0
group by l.link_id, ac.admin_class

Answer 2

我认为,你应当做两件事。

<>0> 形成一种称为“观点”的观点


CREATE VIEW viewAdminRoads
AS
SELECT 
roads.code,
roads.road,
admin_classes.admin_class,
admin_classes.priority
FROM roads 
INNER JOIN admin_classes
        ON roads.road
         = admin_classes.road
WHERE admin_classes.priority &lt&gt 0

提出这一看法可简化整个过程。

<>2> 创建你的甄选<>。


SELECT
link.link_id,
MIN(viewAdminRoads.priority) as priority
FROM links
INNER JOIN viewAdminRoads
        ON links.code
         = viewAdminRoads.code
GROUP BY link.link_id

你们的朋友是你们的朋友。

Answer 3

这并不可行?

inner join base.admin_classes as ac 
on ac.code=l.code and ac.admin_class<>0

Answer 4

确实,我对PogreSql非常了解,但这是它如何与T-SQL合作:

select l.link_id, ac.admin_class, min(ac.priority)
from base.links as l
inner join base.admin_classes as ac
on ac.code=l.code
group by l.link_id, ac.admin_class
having ac.admin_class <> 0

友情链接