sql - 如何在oracle sql中每周显示数据
问题描述
我有运输数据说例子:-
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
解决方案
您可以比较 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;
输出:
星期 产品 1 铁 2 棉布 1 白饭 3 水果 3 钢
db<>在这里摆弄
推荐阅读
- c - c语言中的reduce函数
- python - Apply multiple agg functions on groupby index
- openstreetmap - download a polygone instead of bbox from OpenStreetMap
- macos - Macbook pro loos connection with external drive and doesnt support VGA
- snowflake-cloud-data-platform - Snowflake SQL - How can I query with an array variable for an IN clause
- proxy - 网络网关和网络代理一样吗
- javascript - 何时使用无依赖关系的 useEffect 与直接分配?
- token - Corda - 兑换代币 - 代币 SDK + 账户
- linq - 使用 Moment() 和 Linq 时日期无效
- arduino - Wire.read() 仅返回 7 位字节