sql - 删除重复的 SQL 连接结果

Question

好的，我是 SQL 的新手，所以请多多包涵。我创建了一个声明，我得到了我想要的结果，但想要摆脱重复的结果。有什么简单的解决方案？这是我的声明

SELECT
    li.location,
    li.logistics_unit,
    li.item,
    li.company,
    li.item_desc,
    li.on_hand_qty,
    li.in_transit_qty,
    li.allocated_qty,
    li.lot,
    i.item_category3,
    location.locating_zone,
    location.location_subclass,
    i.item_category4
FROM
    location_inventory li
INNER JOIN item i ON li.item = i.item
INNER JOIN location l ON l.location = li.location
WHERE
    i.item_category3 = 'AS' AND
    li.warehouse = 'river' AND
    li.location NOT LIKE 'd%' AND
    li.location NOT LIKE 'stg%'
ORDER BY
    li.item asc

Answer 1

如果你对你的 JOIN 有信心，那么 DISTINCT 应该这样做:

select DISTINCT
       location_inventory.location , 
       location_inventory.logistics_unit , 
       location_inventory.item , 
       location_inventory.company , 
       location_inventory.item_desc , 
       location_inventory.on_hand_qty , 
       location_inventory.in_transit_qty , 
       location_inventory.allocated_qty , 
       location_inventory.lot ,
       item.item_category3 , 
       location.locating_zone , 
       location.location_subclass , 
       item.item_category4
from location_inventory
INNER JOIN item
on location_inventory.item=item.item
INNER JOIN location
on location_inventory.location=location.location
where item.item_category3 = 'AS' and 
      location_inventory.warehouse = 'river' and 
      location_inventory.location not like 'd%' and 
      location_inventory.location not like 'stg%'
order by location_inventory.item asc