查找每一行SQL的Min、2 min、3 min等。
查找每一行SQL的Min、2 min、3 min等。
提问于 2018-08-01 09:56:32
我有一种情况，我需要根据一个公共键属性为每一行查找min、2分钟、3分钟等等。
表1 (Qty>=1)
╔═══════════════════╗
║ Item Location Qty ║
╠═══════════════════╣
║ 500 Loc1 5 ║
║ 501 Loc2 2 ║
║ 501 Loc3 1 ║
╚═══════════════════╝表2(量化总是1)
╔══════════════════════════╗
║ Item Location Qty Asset ║
╠══════════════════════════╣
║ 500 Loc1 1 11 ║
║ 500 Loc1 1 12 ║
║ 500 Loc1 1 13 ║
║ 500 Loc1 1 14 ║
║ 500 Loc1 1 15 ║
║ 500 Loc1 1 10 ║
║ 500 Loc1 1 9 ║
║ 500 Loc1 1 8 ║
║ 500 Loc1 1 7 ║
║ 501 Loc2 1 27 ║
╚══════════════════════════╝所需输出--如果表1中的Qty为5，那么输出应该有5行(即使Table2中有少于5行)
每一行应分别有min、2 min、3 min、4 min和5 min资产。
示例输出：
╔══════════════════════════╗
║ Item Location Qty Asset ║
╠══════════════════════════╣
║ 500 Loc1 1 7 ║
║ 500 Loc1 1 8 ║
║ 500 Loc1 1 9 ║
║ 500 Loc1 1 10 ║
║ 500 Loc1 1 11 ║
║ 501 Loc2 1 27 ║
║ 501 Loc2 1 ║
║ 501 Loc3 1 ║
╚══════════════════════════╝回答 3
Stack Overflow用户
回答已采纳
发布于 2018-08-01 17:40:32
稍短的方式：
select il.item, il.loc, il.qty, a.asset
from item_locations il
cross apply ( SELECT rownum rn FROM dual CONNECT BY level <= il.qty ) rn
left outer join lateral
( SELECT row_Number() over ( order by a.asset) rn, a.asset
FROM assets a
WHERE a.item = il.item and a.loc = il.loc) a ON a.rn = rn.rn;基本上，rn连接为每个qty值生成一行，然后根据资产编号的排序位置在资产中进行外部连接。
数据的完整示例：
with item_locations ( item, loc, qty ) AS
( SELECT 500, 'Loc1', 5 FROM DUAL UNION ALL
SELECT 501, 'Loc2', 2 FROM DUAL UNION ALL
SELECT 501, 'Loc3', 1 FROM DUAL ),
assets ( item, loc, qty, asset ) AS (
SELECT 500, 'Loc1', 1, 11 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 12 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 13 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 14 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 15 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 10 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 9 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 8 FROM DUAL UNION ALL
SELECT 500, 'Loc1', 1, 7 FROM DUAL UNION ALL
SELECT 501, 'Loc2', 1, 27 FROM DUAL )
select il.item, il.loc, il.qty, a.asset
from item_locations il
cross apply ( SELECT rownum rn FROM dual CONNECT BY level <= il.qty ) rn
left outer join lateral
( SELECT row_Number() over ( order by a.asset) rn, a.asset
FROM assets a
WHERE a.item = il.item and a.loc = il.loc) a ON a.rn = rn.rn;结果：
+------+------+-----+-------+
| ITEM | LOC | QTY | ASSET |
+------+------+-----+-------+
| 500 | Loc1 | 5 | 7 |
| 500 | Loc1 | 5 | 8 |
| 500 | Loc1 | 5 | 9 |
| 500 | Loc1 | 5 | 10 |
| 500 | Loc1 | 5 | 11 |
| 501 | Loc2 | 2 | 27 |
| 501 | Loc2 | 2 | |
| 501 | Loc3 | 1 | |
+------+------+-----+-------+Stack Overflow用户
发布于 2018-08-01 10:47:51
可以使用分层查询将table1行转换为基于qty的多行。
select item, location, qty, level as rn
from table1
connect by level <= qty
and item = prior item
and location = prior location
and prior dbms_random.value is not null;稍后，我已经将level as rn作为假密钥使用了。
您可以使用解析函数从table2行获得一个等价的键值；我假设您对min、2 min等的引用引用了资产应该显示的顺序，因此该列用于order by子句：
select t2.*,
row_number() over (partition by item, location order by asset) as rn
from table2 t2;然后，如果以CTE的形式提供这两种方法，则可以将结果加入到外部：
with t1 as (
select item, location, qty, level as rn
from table1
connect by level <= qty
and item = prior item
and location = prior location
and prior dbms_random.value is not null
),
t2 as (
select t2.*,
row_number() over (partition by item, location order by asset) as rn
from table2 t2
)
select t1.item, t1.location, 1 as qty, t2.asset
from t1
left join t2 on t2.item = t1.item and t2.location = t1.location and t2.rn = t1.rn;将上述两个子查询中的项、locaction和虚拟rn值作为组合连接条件使用。
使用您的示例数据获得：
ITEM LOCATION QTY ASSET
---------- -------- ---------- ----------
500 Loc1 1 7
500 Loc1 1 8
500 Loc1 1 9
500 Loc1 1 10
500 Loc1 1 11
501 Loc2 1 27
501 Loc2 1
501 Loc3 1
8 rows selected. Stack Overflow用户
发布于 2018-08-01 10:33:33
Alex和Matthew成功地给出了仅使用SQL的解决方案。
我认为另一种解决方案是创建一个数据库函数，该函数将管道用于您需要的所有行：
TYPE tp_r_item_loc_asset IS RECORD
(
item yuor_table_2.item%TYPE,
location yuor_table_2.location%TYPE,
qty yuor_table_2.qty%TYPE,
asset yuor_table_2.asset%TYPE
);
TYPE tp_a_item_loc_asset IS TABLE OF tp_r_item_loc_asset;
FUNCTION f_your_pipeline_funtion
RETURN tp_a_item_loc_asset
PIPELINED
IS
v_row tp_r_item_loc_asset;
v_asset NUMBER := null;
BEGIN
FOR i IN (SELECT item, location, qty
FROM your_table_1
ORDER BY item, location)
LOOP
FOR j in 1..i.qty
LOOP
BEGIN
SELECT asset
INTO v_asset
FROM yuor_table_2
WHERE item = i.item
AND location = i.location
ORDER BY asset ASC;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_asset := null;
END;
v_row.item := i.item;
v_row.location := i.location;
v_row.qty := 1;
v_row.asset := v_asset;
PIPE ROW (v_r_report_blob);
END LOOP;
END LOOP;
RETURN;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN;
END f_your_pipeline_funtion;把所有东西都放在一个包里.
之后，您可以通过以下方式引用它(从中选择)：
SELECT item, location, qty, asset
FROM TABLE (p_your_package_name.f_your_pipeline_funtion());我希望我(也)能帮上忙！
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51630389
复制相关文章
相似问题
腾讯云开发者
