比较相似的列是否相等 [英] Comparing Similar Columns for Equality

问题描述

我有一个(简化的)表，其结构如下:

I have a (simplified) table that is structured like so:

Table: ItemData

PK | ItemID | StoreFK | Retail
1  | 100101 | 1       | 4.99
4  | 100101 | 2       | 4.99
7  | 100101 | 3       | 0.99
2  | 100102 | 1       | 6.99
5  | 100102 | 2       | 6.99
8  | 100102 | 3       | 6.99
3  | 100103 | 1       | 7.99
6  | 100103 | 2       | 8.99
9  | 100103 | 3       | 9.99

我想退回在一个或多个商店中零售价不同的所有商品:

I would like to return all the items that have a different retail at one or more stores:

返回:

ItemID
100101 
100103 

• 项目100101在商店3的零售价较低，然后在商店1& 2它被返回.

  • Item 100101 has a lower retail at store 3 then at store 1 & 2 it is returned.

    项目100103在每个商店位置都有不同的零售，因此将其退回.

    Item 100103 has a different retail at each store location so it is returned.

    项目100102在所有三个商店中的零售额均相等，因此不会退货.

    Item 100102 has equality in it's retail at all three stores so it are not returned.

    我对SQL并不陌生，但是对于如何在高效的庄园中进行这种不平等检查，我一无所知.基于另一列的分组检查一列中是否相等的最佳方法是什么?

    I am not new to SQL, but I am lost as to how to make this inequality check in an efficient manor. What is the best way to check for equality in one column based on groupings on another column?

    推荐答案

    在充分尊重Lieven的情况下，我更愿意这样做:

    With all due respect to Lieven, I prefer this:

    SELECT ItemID
    FROM   ItemData
    GROUP BY
           ItemID
    HAVING COUNT(DISTINCT Retail)>1
    

    这篇关于比较相似的列是否相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！