左联接，没有使用MIN()的重复值 [英] left join without duplicate values using MIN()

问题描述

我有一张桌子_1:

id  custno
1   1
2   2
3   3

和一个table_2:

and a table_2:

id  custno  qty  descr
1   1       10   a
2   1       7    b
3   2       4    c
4   3       7    d
5   1       5    e
6   1       5    f

当我运行此查询以显示每个客户的最小订购量时:

When I run this query to show the minimum order quantities from every customer:

SELECT DISTINCT table_1.custno,table_2.qty,table_2.descr 
FROM table_1 
LEFT OUTER JOIN table_2 
    ON  table_1.custno = table_2.custno AND  qty = (SELECT MIN(qty) FROM table_2 
WHERE table_2.custno = table_1.custno   )

然后我得到这个结果:

custno qty  descr
1      5    e 
1      5    f
2      4    c
3      7    d

客户1每次以相同的最小数量(和不同的描述)出现两次，但我只希望看到客户1出现一次.我不在乎那是用"e"作为描述还是用"f"作为描述的记录.

Customer 1 appears twice each time with the same minimum qty (& a different description) but I only want to see customer 1 appear once. I don't care if that is the record with 'e' as a description or 'f' as a description.

推荐答案

通用" SQL方式:

SELECT table_1.custno,table_2.qty,table_2.descr 
FROM table_1, table_2 
WHERE table_2.id = (SELECT TOP 1 id 
                    FROM table_2 
                    WHERE custno = table_1.custno 
                    ORDER BY qty )

SQL 2008方式(可能更快):

SQL 2008 way (probably faster):

SELECT custno, qty, descr 
FROM
(SELECT 
    custno, 
    qty, 
    descr,
    ROW_NUMBER() OVER (PARTITION BY custno ORDER BY qty) RowNum
    FROM table_2
) A
WHERE RowNum = 1

这篇关于左联接，没有使用MIN()的重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！