使用当天的最新时间选择每个用户的行

Question

我能够从DB获得我需要的数据，除了每一天我只想要一行数据，因此，例如在下面的图片中，应该只有第二行，而不是第一行。在某种程度上，我需要能够为一个用户从一个日期只得到一行。

SELECT id_user, date_created || ' ' || time_created as placed
            FROM (
                        SELECT
                        b_objednavky.uzivatel AS id_uzivatele,
                        b_objednavky.vytvoreno::date AS date_created,
                        to_char(b_objednavky.vytvoreno, 'HH24:MI:SS') as time_created,
                        b_objednavky.created

                    FROM b_objednavky
                    INNER JOIN u_uzivatele ON b_objednavky.uzivatel = (
                     SELECT u_uzivatele.id from u_uzivatele JOIN bw_paid_orders
                           ON u_uzivatele.id = bw_paid_orders.user_id 
                     WHERE bw_paid_orders.active_thru < NOW() + interval '6 months'
                     ) uzivatele
                    INNER JOIN bc_stavy_objednavky ON b_objednavky.stav = bc_stavy_objednavky.id

                    WHERE bc_stavy_objednavky.sysid = 'nova' AND b_objednavky.vytvoreno <= NOW()
                    AND bc_staty.kod NOT IN (
                      'BR', 'CL', 'AR', 'MX'
                    )

                ) AS order
                 LEFT OUTER JOIN (
                  SELECT user_id, max(paid) as last_paid FROM bw_paid_orders
                GROUP BY user_id
                ) last_paid_by_user ON (id_user = user_id) 
                WHERE (created > last_paid OR last_paid IS NULL) AND created BETWEEN ('2017-02' || '-01')::date
            AND ('2017-02' || '-01')::DATE + '1 month'::interval 
            ORDER BY datum_objednani DESC, objednavajici, nazev, duration

Answer 1

使用DISTINCT ON

从一个日期只为一个用户获取一行

SELECT DISTINCT ON (id_user, date_created) ...

若要获得当天的最新时间的行，请添加(在同一查询级别)：

ORDER BY id_user, date_created, time_created DESC NULLS LAST

详细信息：

• Select first row in each GROUP BY group?