从下面的SQL查询中删除重复项

Question

SQL表

id       component   price  manufactured
1         fx card   500     2011
2         ram       400     2010
3         case      400     2010
4         smps      500     2011
5         cord      200     2010
6         usb       200     2010

预期产量(返回相同价格和制造年限的组件作为不同的组合)：

component   component   price   manufactured
smps        fx card     500     2011
case        ram         400     2010
cord        usb         200     2010

已尝试查询

SELECT m1.[component]
  ,m2.[component]
  ,m1.[price]
  ,m1.[manufactured]
FROM [dbo].[Mfg] m1
inner join [dbo].[Mfg] m2
on m1.component != m2.component 
and m1.price = m2.price 
and m1.manufactured = m2.manufactured

以上查询的结果(尽管输出错误)：

component   component   price   manufactured
smps        fx card     500    2011
case        ram         400    2010
cord        usb         200    2010
ram         case        400    2010
fx card     smps        500    2011
usb         cord        200    2010

请帮助我使用查询来消除重复的组合。

Answer 1

如果没有组件对共享相同的名称，则此方法有效：

SELECT m1.[component]
      ,m2.[component]
      ,m1.[price]
      ,m1.[manufactured]
FROM [dbo].[Mfg] m1
JOIN [dbo].[Mfg] m2 ON m1.component > m2.component 
                   AND m1.price = m2.price 
                   AND m1.manufactured = m2.manufactured;

我所做的更改只是用不等运算符!=换成了greater than运算符。这样，你就可以一次而不是两次地得到每一对。

为了提供相同名称的可能性，请使用

JOIN [dbo].[Mfg] m2 ON m1.id > m2.id
                   AND m1.price = m2.price 
                   AND m1.manufactured = m2.manufactured;

我假设id是独一无二的，所以它不会出错。