排列函式和連接

我有以下表格。

Order_det

Pick_det

我想要的表格如下，

在這個社區的幫助下，我找到了一個非常相似的答案，即。 鏈接到該答案，類似的問題

 select *
from
(
  select t1. *, max(shipment_num) over (partition by ord_num) as orders_max_ship_num
  from pick_det t1
) with_max
where shipment_num = orders_max_ship_num
order by ord_num, item_code;

我的問題是。 我應該在哪里加入Order_det表以獲得Unit_sales_price的值，并從pick_det表中已經檢索到的max shipment_num行？

uj5u.com熱心網友回復：

我遲到了，因為我把所有的東西都打出來了，并確保它運行正常

if OBJECT_ID('tempdb. #Order_det') IS NOT NULL DROP TABLE #Order_det。
如果OBJECT_ID('tempdb. #Pick_det') IS NOT NULL DROP TABLE #Pick_det。

創建 表 #Order_det (Ord_num INT, item_code INT, Unit_sales_price INT) 。
創建 表 #Pick_det(Ord_num INT, Shipment_num INT, Item_code INT, Quantity_to_pick INT, Quantity_picked INT）。)

INSERT INTO #Order_det (Ord_num, item_code, Unit_sales_price)
VALUES[/span
(1111, 1, 50) 。
(1111, 2, 40) 。
(1111, 3, 30) 。
(1111, 4, 20) 。
(1111, 5, 10) 。
(2222, 3, 30)

INSERT INTO #Pick_det (Ord_num, Shipment_num, Item_code, Quantity_to_pick, Quantity_picked)
VALUES[/span
(1111, 1, 1, 100, 100) 。
(1111, 2, 1, 100, 100) 。
(1111，3，2，100，100)。
(2222, 3, 3, 200, 200)

SELECT[/span
    訂單Det.Ord_num
    , PickList.Shipment_Num
    , OrderDet.Item_code
    , PickList.Quality_to_pick 
    , PickList.Quality_picked 
    , OrderDet.Unit_sales_price
    , OrderDet.Unit_sales_price * PickList.Want_picked AS Total_price
FROM[/span
    (
        SELECT[/span 
            順序
            , MAX(Shipment_num) OVER (PARTITION BY ord_num）AS MaxShipment_Num--
            , 裝運量_Num
            , 專案代碼
            , 挑選的數量 
            , 挑選的數量 
        FROM #Pick_det
    ) AS PickList 
    INNER JOIN #Order_det AS OrderDet
        ON OrderDet.Ord_num = PickList.Ord_num 
        AND OrderDet.item_code = PickList.Item_code
        and PickList.Shipment_num = PickList.MaxShipment_Num

uj5u.com熱心網友回復：

你可以簡單地在你的基礎上進行查詢：

with s as ( select * from from ( ( select t1. *, max(shipment_num) over (partition by ord_num) as orders_max_ship_num from pick_det t1 ) with_max where shipment_num = orders_max_ship_num ) select* s.qty_picked as total_price from s join order_det od on od.ord_num = s.ord_num and od.item_code= s.item_code order by s.ord_num, s.item_code。

或者你馬上應用連接：

select
  s.ord_num, s.shipment_num, s.item_code, s.qty_to_pick, s.qty_picked,
  od.unit_sales_price, od.unit_sales_price * s.qty_picked as total_price
從開始
(
  select t1. *, max(shipment_num) over (partition by ord_num) as orders_max_ship_num
  from pick_det t1
) s
join order_det od on od.ord_num = s.ord_num and od.item_code= s.item_code
where s.shipment_num = s.orders_max_ship_num
order by s.ord_num, s.item_code;

uj5u.com熱心網友回復：

另一個簡單的技術，你可以通過應用分析函式 - Dense_Rank，這是一個視窗函式，為結果集的磁區中的每一行分配一個等級

我正在嘗試按照shipment_num以降序排列，然后只在共同列上進行 JOIN。我也使用了CTE和子查詢：

With temp AS（Select * from (SELECT a。 *, dense_rank() OVER（PARTITION BY ord_num order by shipment_num desc) rnk
from Pick_det a) t where rnk = 1)

選擇 temp.Ord_num, temp.Shipment_num, temp.item_code, temp.Qualty_to_pick, temp.Qualty_picked,
temp.qty_to_pick * Order_det.Unit_sales_price total_price
from temp JOIN Order_det on temp.ord_num= Order_det.ord_num
and temp.item_code = Order_det.item_code。

你的子查詢將看起來像這樣：

Select temp.Ord_num, temp.Shipment_num, temp.Item_code, temp.Want_to_Pick, temp.Want_picked, temp.qty_to_pick * Order_det.Unit_sales_price total_price from[/span (SELECT a. *, dense_rank() OVER（PARTITION BY ord_num order by shipment_num desc) rnk from Pick_det a) temp INNER JOIN Order_det on temp.ord_num = Order_det.ord_num and temp.item_code = Order_det.item_code AND temp.rnk = 1;

如果你想了解dense_rank函式，這里是鏈接

這里是SQL Fiddle

標籤：

上一篇：合并兩個表的列而不進行連接查詢

下一篇：帶外鍵的SELECT，帶JOIN的mysql