如何在oracle sql中每周显示数据

Question

我有传输数据，例如:-

select * from  DR_TRANSPORT
PRODUCTS  TR_DATE      SHIPMENT
----------------------------
IRON     19/02/2021   Y
COTTON   26/02/2021   Y
RICE     20/02/2021   N
FRUITS   01/03/2021   Y
STEEL    07/03/2021   Y

现在用户将通过日期和日期，例如:19/02/2021和26/03/2021

预期数据:

Weeks Goods
------------
week1 fruits
week2 IRON
WEEK3 COTTON
````
based on the user dates we need to extract weeks and get the o/p as above 

Answer 1

您可以比较iso周的开始时间:

SELECT FLOOR(
(TRUNC(tr_date, 'IW') - TRUNC(DATE '2021-02-19', 'IW'))/7
) + 1 AS week,
products
FROM   table_name
WHERE  tr_date BETWEEN DATE '2021-02-19' AND DATE '2021-03-26'

对于样本数据:

CREATE TABLE table_name (PRODUCTS, TR_DATE, SHIPMENT) AS
SELECT 'IRON',   DATE '2021-02-19', 'Y' FROM DUAL UNION ALL
SELECT 'COTTON', DATE '2021-02-26', 'Y' FROM DUAL UNION ALL
SELECT 'RICE',   DATE '2021-02-20', 'N' FROM DUAL UNION ALL
SELECT 'FRUITS', DATE '2021-03-01', 'Y' FROM DUAL UNION ALL
SELECT 'STEEL',  DATE '2021-03-07', 'Y' FROM DUAL;

输出:

<表类>周产品tbody><<tr>1铁2棉花1水稻3水果3钢