在mysql中，如何只选择一个列中值相同但另一个列中值不同的行?

I'm not really a back-end developer so I have very little experience with sql. I need to filter from a table the "monthly buyings", where a client would buy in the same transaction products to be shipped in different dates.

我不是一个真正的后端开发人员，所以我对sql几乎没有经验。我需要从表中筛选“月度购买”，即客户在相同的交易产品中购买不同日期的产品。

Let's say I have this on a table:

假设我把这个放在桌子上:

transactionID |   deliveryDate  
------------- |   ------------
  1           |    2013-11-24  
  1           |    2013-11-24  
  2           |    2013-11-26   
  3           |    2013-11-10  
  3           |    2013-11-17  
  3           |    2013-11-24  
  4           |    2013-11-10  
  4           |    2013-11-10  
  4           |    2013-11-17  
  4           |    2013-11-17

I want this as a result:

我想要这样的结果:

transactionID |   deliveryDate  
------------- |   ------------
  3           |    2013-11-20  
  3           |    2013-11-22  
  3           |    2013-11-24  
  4           |    2013-11-10  
  4           |    2013-11-10  
  4           |    2013-11-17  
  4           |    2013-11-17

Transaction 3 and 4 are the only ones that fit my criteria, because transaction 1, whereas there are multiple buyings in the same transaction, the delivery date is the same. Transaction 2 doesn't fit either I need to have multiple items being bought. So, basically I need to select every row where the count of the transactionID is more than one, but only where there is also more than one distinct deliveryDate.

事务3和4是唯一符合我标准的，因为事务1，而在同一事务中有多个购买，交付日期是相同的。事务2也不适合我需要购买多个项目。因此，基本上，我需要选择每一行，其中transactionID的计数大于1，但只有在有多个不同的交付日期时。

What I tried so far is this:

我到目前为止所尝试的是:

SELECT *
FROM myTable
WHERE `transactionID` IN
    (     SELECT `transactionID`
          FROM `myTable`
          GROUP BY `transactionID`
          HAVING COUNT(*) > 1
    )
ORDER BY `transactionID`

I know I'm just filtering when there is more than one buy in the same transaction, but I don't really know how to follow this. Besides, since there are plenty of more columns this query is pretty slow, so I think that there is probably a better direction where some of you can point me to.

我知道我只是在过滤同一个交易中有多个买家，但我真的不知道如何跟进。此外，由于有大量的列，这个查询速度非常慢，因此我认为可能有更好的方向可以让你们中的一些人指向我。

Thanks!

谢谢!

3 个解决方案

#1

You are close. You need to count the distinct dates:

你是接近。你需要数清楚不同的日期:

SELECT *
FROM myTable
WHERE `transactionID` IN
    (     SELECT `transactionID`
          FROM `myTable`
          GROUP BY `transactionID`
          HAVING COUNT(distinct DeliveryDate) > 1
    )
ORDER BY `transactionID`

#2

I suppose this would be faster, and it also removes the duplicate rows (the ones with the same id and date)

我认为这样会更快，而且它还会删除重复的行(id和日期相同的行)

SELECT 
   a.transactionID,
   a.deliveryDate,
count( b.transactionID )
FROM 
    temp a
    JOIN temp b ON ( a.transactionID = b.transactionID AND a.deliveryDate <> b.deliveryDate )
GROUP BY
   a.transactionID,
   a.deliveryDate
HAVING
   count( b.transactionID ) > 1

#3

Use this also work.

也使用这个工作。

SELECT *
    FROM myTable GROUP BY `transactionID`HAVING COUNT(DeliveryDate) > 1 ORDER BY `transactionID`

#1