主页 / dba / 问题 / 127683
Accepted
Fabrizio Mazzoni
Asked: 2016-01-29 22:27:46 +0800 CST

在 postgresql 中排序

  • 772

我遇到了一个问题，但我不明白。在 mac 上运行 PostgreSQL 并有这个查询：

select tldid, 
       tldaction, 
       to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
       locdescription,
       lttype, 
       tldorder
from trip_log 
    left join trip_log_det on tlid=tldtlid 
    left join locations on tldlocation=locid 
    left join location_types on loctype=ltid
where tlid = 12 
order by tldid, tldorder

示例表数据为：

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

我想得到的结果如下：

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1    
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

我无法按最后一列 (tldorder) 排序，因为那样会影响旅行的逻辑。我需要根据最后一列 (tldorder) 求出结果，但仍保持原始locdescription值序列。分析前两行数据：

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2

我需要交换它们，这样它就会变成：

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4

接下来的两行相同

95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1

必须成为：

96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4

问题是我无法订购，locdescription因为这些是预设的，并且必须在整个查询过程中保持特定的顺序，因为这是在公路旅行中定义的地点，但它们可以在“它们自己”中随意调整。

这是表中数据的顺序。

DAR ES SALAAM
DAR ES SALAAM
TUNDUMA GOING
TUNDUMA GOING
SOLWEZI
SOLWEZI
TUNDUMA RETURN 
TUNDUMA RETURN
DAR ES SALAAM
DAR ES SALAAM

它们必须保持这样，但是可以交换值的键以便根据tldorder值对它们进行排序。

如果我执行上面显示的查询，我会得到与表中相同的结果：

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

我想我已经理解了这个问题。我按 tldid 订购，它是一个序列号，而不是多次相同的值。一旦我也对 tldorder 进行排序，该值将链接到一个唯一的 tldid 值。这就是排序不起作用的原因。有什么办法可以解决这个问题吗？

表定义：

CREATE TABLE public.trip_log_det
(
  tldid integer NOT NULL DEFAULT nextval('trip_lod_det_tldid_seq'::regclass),
  tldtlid integer,
  tldlocation integer,
  tldaction character varying,
  tldactiondate date,
  tldorder integer, -- Ordering for arrival loading offloading and departure actions so user does not get confused!
  CONSTRAINT trip_lod_det_pkey PRIMARY KEY (tldid),
  CONSTRAINT trip_lod_det_tldlocation_fkey FOREIGN KEY (tldlocation)
      REFERENCES public.locations (locid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.locations
(
  locid integer NOT NULL DEFAULT nextval('locations_locid_seq'::regclass),
  locdescription character varying,
  loctype integer,
  CONSTRAINT locations_pkey PRIMARY KEY (locid),
  CONSTRAINT "loctype->lttype" FOREIGN KEY (loctype)
      REFERENCES public.location_types (ltid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.location_types
(
  ltid integer NOT NULL DEFAULT nextval('location_types_ltid_seq'::regclass),
  lttype character varying,
  ltdeparturedate boolean NOT NULL DEFAULT false,
  ltarrivaldate boolean NOT NULL DEFAULT false,
  ltloadingdate boolean NOT NULL DEFAULT false,
  ltoffloadingdate boolean NOT NULL DEFAULT false,
  CONSTRAINT location_types_pkey PRIMARY KEY (ltid)
);
postgresql order-by

1 个回答

  1. Best Answer

    编辑后，问题（终于！）清楚了。问题是你想要的顺序很复杂。这是一个“差距和孤岛”问题。您首先要隔离具有相同位置的“岛屿”，即具有相同位置的连续（按 排序时tldid）行，然后根据 对这些岛屿中的行重新排序tldorder。

    解决这个问题的一种方法：

    with trip_log_det_ordered as
  ( select 
        tldid, 
        tldaction, 
        tldactiondate, 
        locdescription,
        lttype, 
        tldorder, 
        case when locid <> lag(locid) over (order by tldid) then 1 else null end
            as location_change
    from trip_log 
        left join trip_log_det on tlid=tldtlid 
        left join locations on tldlocation=locid 
        left join location_types on loctype=ltid
    where tlid = 12 
  )
select 
    tldid, 
    tldaction, 
    to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
    locdescription,
    lttype, 
    tldorder
from 
    trip_log_det_ordered
order by 
    count(location_change) over (order by tldid
                                 rows between unbounded preceding
                                          and current row),
    tldorder ;

    和else null可以rows between unbounded preceding and current row删除，因为它们是默认值。

    如果count(...)在选择列表中添加 ，您可以看到岛屿编号。第一个岛将有0（位置变化），第二个岛将有1，第三个岛将有2，等等......

    进一步说明：

    • trip_log_det (tldactiondate)如果已填充并可用于排序，查询将简单得多。
    • 3

相关问题