我想將每個 ID 的最早記錄保留在日期為yyyy-mm-dd格式的表格中。如果我在同一天有兩張或更多的記錄，我只想拿一張，我不在乎它們是什么。
我嘗試將表與自身連接，但左連接不起作用并回傳多個。
示例原始表：
| ID_供應商 | 銷售量 | 辦公室 | dt |
|---|---|---|---|
| 1 | 3 | 一種 | 2021-10-12 |
| 1 | 50 | 乙 | 2021-10-13 |
| 2 | 109 | H | 2021-10-13 |
| 3 | 110 | H | 2021-10-05 |
| 4 | 111 | ? | 2021-10-13 |
| 4 | 112 | W | 2021-10-13 |
| 4 | 113 | 乙 | 2021-10-13 |
預期結果：
| ID_供應商 | 銷售量 | 辦公室 |
|---|---|---|
| 1 | 3 | 一種 |
| 2 | 109 | H |
| 3 | 110 | H |
| 4 | 111 | ? |
Y嘗試在沒有運氣的情況下使用over partition，現在我被困在這里LEFT JOIN回傳一個OUTER JOIN
歡迎任何幫助。這里的代碼：
WITH t as (
SELECT id_vendor
, sales
, office
, min(dt) fst_date
FROM test_table
WHERE dt >= date('2021-09-12')
-- AND id_vendor = '1004618231015'
GROUP BY id_vendor, sales, office
ORDER BY id_vendor
)
, b AS (
SELECT id_vendor
, sales
, office
, dense_rank() over (order by fst_date) as rnk
FROM t
-- WHERE id_vendor = '1004618231015'
GROUP BY id_vendor
, sales
, office
, fst_date
)
, c AS (
SELECT id_vendor
FROM b WHERE rnk = 1
GROUP BY id_vendor
)
, d AS (
SELECT id_vendor
, sales
, office
FROM b WHERE rnk = 1)
)
SELECT c.id_vendor
, d.sales
, d.office
FROM c
LEFT join d
ON c.id_vendor = d.id_vendor
uj5u.com熱心網友回復：
簡單地說，您可以使用它Row_number來獲得您的預期結果，如下所示：
select id_vendor, sales , office from (
SELECT id_vendor
, sales
, office
,Row_number() over(partition by id_vendor order by dt) rw
FROM test_table ) t
where t.rw=1
uj5u.com熱心網友回復：
所描述的任務不需要加入，只需在 subselect/cte 中使用row_numberwith partitioning by就可以了：ID_vendor
-- sample data
WITH dataset (ID_vendor, sales, office, dt) AS (
VALUES (1, 3, 'A', date '2021-10-12'),
(1, 50, 'B', date '2021-10-13'),
(2, 109, 'H', date '2021-10-13'),
(3, 110, 'H', date '2021-10-05'),
(4, 111, 'N', date '2021-10-13'),
(4, 112, 'W', date '2021-10-13'),
(4, 113, 'B', date '2021-10-13')
)
-- query
select id_vendor,
sales,
office
from (
select *,
row_number() over (partition by id_vendor order by dt) rnk
from dataset
)
where rnk = 1
order by id_vendor
輸出：
| id_vendor | 銷售量 | 辦公室 |
|---|---|---|
| 1 | 3 | 一種 |
| 2 | 109 | H |
| 3 | 110 | H |
| 4 | 111 | ? |
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/457455.html
上一篇：根據另一列值將一列值拆分為多列