Postgresql

在 postgresql 中排序

  • January 29, 2016

我面臨一個問題，而我不明白。在 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)
);

編輯後，問題（終於！）清楚了。問題是您想要的排序很複雜。這是一個“差距和島嶼”問題。您首先要隔離具有相同位置的“島嶼”，即具有相同位置的連續（按 排序時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)如果填充了並且可以用於排序，那麼查詢將會簡單得多。

引用自：https://dba.stackexchange.com/questions/127683

相關問答

Postgresql

與 SQL 中的按列排序相反

  • January 1, 2021
檢視問答
Postgresql

是否可以按輸入 ID 的順序排序？

  • September 27, 2021
檢視問答
Postgresql

如何按典型的軟體版本（如 XYZ）訂購？

  • July 7, 2021
檢視問答
Postgresql

如何在 JSON_AGG 中使用 ORDER BY 和 LIMIT

  • June 7, 2021
檢視問答
Postgresql

在 postgresql 中按鍵排序 json 項的巧妙方法（兩個範例）

  • December 28, 2020
檢視問答
Postgresql

如何基於列的串聯建構 PostgreSQL ORDER BY 表達式？

  • February 22, 2017
檢視問答