Having trouble wrapping my head around having an efficient "duplicate entries" select in a single query.
在一个查询中进行高效的“重复条目”选择时遇到了麻烦。
In the below example, duplicate StockNo can exist spanning multiple Date. I want to search StockNo for duplicate entries, and if at least 1 StockNo record is found within the Date current YEAR-MONTH, then I also need to select its partner that could exist in any other YEAR-MONTH. Is this possible?
在下面的示例中，复制StockNo可以跨多个日期存在。我想在StockNo中搜索重复的条目，如果在当前年度-月的日期内找到至少一个StockNo记录，那么我还需要选择其他年份-月可能存在的合作伙伴。这是可能的吗?
Example Query:
示例查询:
SELECT * FROM `sales`
WHERE `StockNo` IN
(SELECT `StockNo` FROM `sales` GROUP BY `StockNo` HAVING COUNT(*) > 1)
AND `Date` LIKE '2016-11-%'
ORDER BY `StockNo`, `TransactionID`;
Example Data:
示例数据:
ID | StockNo | Date
1 | 1 | 2016-11-01
2 | 1 | 2016-11-10
3 | 2 | 2016-11-05
4 | 2 | 2016-10-29
5 | 3 | 2016-10-25
6 | 3 | 2016-10-15
With my example query and data, I have 3 pairs of duplicate entries. It's pretty obvious that I will only return 3 records (ID's 1, 2 & 3) due to AND Date LIKE '2016-11-%', however I need to return ID's 1, 2, 3, 4. I want to ignore ID's 5 & 6 because neither of them fall within the current month.
对于示例查询和数据，我有3对重复条目。很明显，我将只返回3条记录(ID的1、2和3)，以及日期，比如“2016- 11% -%”，但是我需要返回ID的1、2、3、4。我想忽略ID的5和6，因为它们都不属于当月。
Hope that makes sense. Thanks for any help you can provide.
希望是有意义的。谢谢你提供的任何帮助。
2 个解决方案
#1
2
SELECT StockNo
FROM sales
GROUP BY StockNo
HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0
If you also want to retrieve the full records for those matching stock numbers in the above query, you can just add a join:
如果您还想检索上述查询中匹配的股票号码的完整记录，您只需添加一个连接:
SELECT s1.*
FROM sales s1
INNER JOIN
(
SELECT StockNo
FROM sales
GROUP BY StockNo
HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0
) s2
ON s1.StockNo = s2.StockNo
Demo here:
演示:
SQLFiddle
#2
0
Thank you very much Tim for pointing me in the right direction. Your answer was close but it still only returned records from the current month and in the end I used the following query:
非常感谢蒂姆给我指出了正确的方向。你的回答很接近，但仍然只返回当月的记录，最后我使用了以下查询:
SELECT s1.* FROM `sales` s1
INNER JOIN
(
SELECT * FROM `sales` GROUP BY `StockNo` HAVING COUNT(`StockNo`) > 1
AND SUM(CASE WHEN DATE_FORMAT(`Date`, '%Y-%m')='2016-11' THEN 1 ELSE 0 END) > 0
) s2
ON s1.StockNo=s2.StockNo
This one had been eluding me for some time.
这件事已经困扰我一段时间了。
#1
2
SELECT StockNo
FROM sales
GROUP BY StockNo
HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0
If you also want to retrieve the full records for those matching stock numbers in the above query, you can just add a join:
如果您还想检索上述查询中匹配的股票号码的完整记录，您只需添加一个连接:
SELECT s1.*
FROM sales s1
INNER JOIN
(
SELECT StockNo
FROM sales
GROUP BY StockNo
HAVING SUM(CASE WHEN DATE_FORMAT(Date, '%Y-%m') = '2016-11' THEN 1 ELSE 0 END) > 0
) s2
ON s1.StockNo = s2.StockNo
Demo here:
演示:
SQLFiddle
#2
0
Thank you very much Tim for pointing me in the right direction. Your answer was close but it still only returned records from the current month and in the end I used the following query:
非常感谢蒂姆给我指出了正确的方向。你的回答很接近，但仍然只返回当月的记录，最后我使用了以下查询:
SELECT s1.* FROM `sales` s1
INNER JOIN
(
SELECT * FROM `sales` GROUP BY `StockNo` HAVING COUNT(`StockNo`) > 1
AND SUM(CASE WHEN DATE_FORMAT(`Date`, '%Y-%m')='2016-11' THEN 1 ELSE 0 END) > 0
) s2
ON s1.StockNo=s2.StockNo
This one had been eluding me for some time.
这件事已经困扰我一段时间了。