使用 group by 在多個表上選擇

使用 Postgres 9.3，我在選擇幾個表時遇到問題。我有 simcards 庫存，我想得到這樣的東西：

| network | supplier | total topups (EUR) | 1st topup qty|   
| three   | three    | 500                | 34           |  
| three   | core     | 300                | 4            |  
| o2      | o2       | 100                | 3            |  

| network | supplier | total topups (EUR) | 2nd topup qty|   
| three   | three    | 200                | 4            |  

| network | supplier | total topups (EUR) | 3rd topup qty|   
| three   | three    | 200                | 4            |  

我的表模式是：

• simcard（id，iccid（唯一），network_id，supplier_id）
• 充值（id，日期，金額，simcard_id）
• 網路（ID，名稱）
• 供應商（ID，名稱）

目前我可以選擇所有第一次充值，但我不知道如何以我想要的格式顯示它們（network| supplied by| topups sum| 1st topup qty）

然後我不知道如何為每張 SIM 卡選擇第二、第三次充值。我嘗試了limit 1 offset 1oroffset 2但它沒有用。

select 
   distinct topup.iccid    
   , s.created_date
   , n.name as "network"
   , p.name as "supplier"
from
(
select 
   s.iccid as "iccid"
   , t.date

from 
   simcard_topup as t
   join simcard_simcard as s
       on t.iccid_id = s.id
--where 
--  CAST(t.date AS DATE) = date '2015-03-13' - integer '14'

group by
   s.iccid
   , t.date
order by
   t.date asc
) as topup
join simcard_simcard as s
   on topup.iccid = s.iccid
join simcard_network as n
   on s.network_id = n.id
join simcard_supplier as p
   on s.supplier_id = p.id

SELECT st.topup_nr, n.name AS network, p.name AS supplier
    , st.topup_sum, st.topup_qty
FROM  (
  SELECT t.topup_nr, s.network_id, s.supplier_id
       , sum(t.amount) AS topup_sum
       , count(*) AS topup_qty
  FROM  (
     SELECT simcard_id, amount
          , row_number() OVER (PARTITION BY simcard_id, ORDER BY date) AS topup_nr
     FROM   simcard_topup
     ) t
  JOIN   simcard_simcard s ON s.id = t.simcard_id  -- or so I assume
  GROUP  BY 1,2,3
  ) st
JOIN   simcard_network  n ON n.id = st.network_id
JOIN   simcard_supplier p ON p.id = st.supplier_id
ORDER  BY st.topup_nr, st.topup_qty DESC NULLS LAST
                    , st.topup_sum DESC NULLS LAST;

筆記

• 核心功能是內部子查詢中的視窗函式row_number()，用於對每個 SIM 卡的充值進行編號。這使得在外部查詢中聚合第 1、第 2 等充值變得簡單。
• 由於缺乏資訊，假設加入on ，儘管您的程式碼中也有simcard_simcard，這似乎是倒退…simcard_topup``s.id = t.simcard_id``on t.iccid_id = s.id
• 不要使用像date列名這樣的基本類型名稱。
• 為什麼你CAST(t.date AS DATE)的程式碼中有？日期應該是date，因此無需將其轉換為date。此外，似乎我們只需要排名充值的日期。
• 計數(network_id, supplier_id)並按 排序結果... topup_qty DESC, topup_sum DESC，因此與最多充值的組合排在第一位。NULLS LAST只是為了防止 NULL 值。

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