SELECT 有条件优先来源

发布于 2024-08-23 13:02:08 字数 788 浏览 4 评论 0原文

我有一个查询已经使用了一段时间，但是我有一个新场景，我不确定如何处理。下面的查询从名为 availables 的表中提取平均定价。我现在遇到的问题是，该表中包含的数据可能来自两个来源之一。所以我在表中添加了一个源字段。

问题是任何记录的这些来源之一或两个可能是空白的，现在很好，或者它们可能包含同一记录的数据（这是我的问题）。除了新的替代来源之外，这些记录通常是唯一的。

Room Date        Price   Source
27   2010-02-28  $27.99  1
27   2010-02-28  $25.99  2

在这个实例中，我需要查询仅提取第一个源并忽略第二个源，但前提是它们都存在。

SELECT 
    rooms.id, 
    name, 
    ppl, 
    private AS exclusive, 
    MIN(spots) AS spots, 
    AVG(availables.price) AS price FROM "rooms" 
INNER JOIN 
    "availables" ON availables.room_id = rooms.id 
WHERE 
    (("rooms".hostel_id = 6933) AND 
     (rooms.active IS true AND bookdate BETWEEN '2011-02-20' AND '2011-02-22')) 
GROUP BY 
    rooms.id, name, ppl, private ORDER BY price

原文

I have a query which I've been using for sometime however I have a new scenario which I'm not sure how to handle. The query below extracts avg pricing from a table called availables. The problem I have now is that, the data which is contained in this table can come from one of two sources. So I added a source field to the table.

The problem is one or both of these sources for any record may be blank which is fine now, or they may contain data for the same record (which is my problem). The records are normally unique except for the new alternative sources.

Room Date        Price   Source
27   2010-02-28  $27.99  1
27   2010-02-28  $25.99  2

I this one instance I need the query to pull only the first source and ignore the second, but only if they both exist.

SELECT 
    rooms.id, 
    name, 
    ppl, 
    private AS exclusive, 
    MIN(spots) AS spots, 
    AVG(availables.price) AS price FROM "rooms" 
INNER JOIN 
    "availables" ON availables.room_id = rooms.id 
WHERE 
    (("rooms".hostel_id = 6933) AND 
     (rooms.active IS true AND bookdate BETWEEN '2011-02-20' AND '2011-02-22')) 
GROUP BY 
    rooms.id, name, ppl, private ORDER BY price

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧时光的容颜 2024-08-30 13:02:08

添加子查询，例如：

inner join
  (select Room, Date, min(Source) as Source
  from availables
  group by Room, Date) first_available
on first_available.Room = available.Room
    and first_available.Date = available.Date
    and first_available.Source = available.Source

add a subquery like:

inner join
  (select Room, Date, min(Source) as Source
  from availables
  group by Room, Date) first_available
on first_available.Room = available.Room
    and first_available.Date = available.Date
    and first_available.Source = available.Source

回复收藏 0 原文

叹梦 2024-08-30 13:02:08

SELECT  *
FROM    rooms
JOIN    (
        SELECT  DISTINCT ON (room_id, date) *
        FROM    availables
        ORDER BY
                room_id, date, source
        ) a
ON      a.room_id = rooms.id
WHERE   …

SELECT  *
FROM    rooms
JOIN    (
        SELECT  DISTINCT ON (room_id, date) *
        FROM    availables
        ORDER BY
                room_id, date, source
        ) a
ON      a.room_id = rooms.id
WHERE   …

回复收藏 0 原文

~没有更多了~