场景:
一个名为"项目"的表格
六列:"Item_Num"、"Item_Name"、"Status"、"Aisle"、"Bin"&"数量"
目标是确保具有相同"状态"的"Item_Num"/"Item_Name"位于相同的"过道"one_answers"箱子"位置;但是,可能存在这样一种情况,即具有相同"Item_Num"/"Item_Name"one_answers"Status"的两个框可能位于不同的"Aisle"one_answers"Bin"位置。我希望能够为系统运行查询,以返回"Item_Num"、"Item_NName"one_answers"State"相同但"Aisle"one_answers"Bin"数据不匹配的记录。
Item_Num | Item_Name | Status | Aisle | Bin | Qty
5206 | Popcorn | A | 1 | 2 | 5
5206 | Popcorn | A | 1 | 3 | 4
5206 | Popcorn | A | 2 | 2 | 5
5206 | Popcorn | B | 1 | 4 | 2
在上面的例子中,我希望系统返回第一列匹配的行。这将允许将三个项目合并到一个地点。
首先查找哪些项目记录组具有多个通道、bin值。然后返回属于这些组的任何行。
with g as
( select item_num, item_name, status,
count(distinct aisle) as aisles,
count(distinct bin) as bins
from items
group by item_num, item_name, status
having count(distinct aisle) > 1
or count(distinct bin) > 1
)
select i.item_num, i.item_name, i.status,
i.aisle, i.bin,
sum(1.qty) tot_qty
from items i
join g on (i.item_num, i.item_name, i.status)
=(g.item_num, g.item_name, g.status)
group by item_num, item_name, status, aisle, bin
试试这个:
select a.Item_num,a.Item_Name,a.Status,a.Aisle,a.Bin,a.qty from table a inner join table b
ON a.Item_num=b.Item_num and a.Item_Name=b.Item_Name and a.Status=b.Status and a.Aisle <> b.Aisle and a.Bin <> b.Bin