How to write a complex query with this data?
I have a table type:
id | text | tel | time
----------------------------
1 | text1 | tel1 | unixtime
2 | text1 | tel1 | unixtime
3 | text1 | tel1 | unixtime
4 | text2 | tel2 | unixtime
5 | text3 | tel3 | unixtime
6 | text3 | tel3 | unixtime
I want it "clean", try to delete all rows where is text
= tel
, and leave one with the largest time
. I began like this:
SELECT concat(`text`, `tel`),
count(concat(`text`, `tel`))
FROM `ads` GROUP by concat(`text`, `tel`)
Having count(concat(`text`, `tel`)) &开发者_StackOverflowgt; 1
Something as something works, but something all wrong (but still I can not guess how to remove, leaving only a one record of repeating).
I think he misphrased the question, since according to his code snippet he's looking for rows where the combination of text+tel are the same (hence the CONCAT). He's not simply looking for rows where text=tel.
If you're willing to write a quick script for this and not keep it in pure SQL, you could solve it in two steps.
First, find the rows where text+tel are duplicated:
mysql>
SELECT count(id) as dupe_count,
text as dupe_text,
tel as dupe_tel,
max(time) as dupe_maxtime
FROM tbl
GROUP BY concat(text,tel)
HAVING dupe_count > 1;
+------------+-----------+----------+--------------+
| dupe_count | dupe_text | dupe_tel | dupe_maxtime |
+------------+-----------+----------+--------------+
| 4 | text1 | tel1 | 1285203547 |
+------------+-----------+----------+--------------+
1 row in set (0.00 sec)
Then iterate through them, and use:
DELETE FROM tbl WHERE text=$2 AND tel=$3 AND time < $4;
You could also use the dupe_count result to do a "DELETE FROM tbl WHERE ... ORDER BY time ASC LIMIT x", where x is dupe_count-1 (effectively deleting all but one row from each dupe group).
With a few more minutes of work you could probably port the latter DELETE logic into a single step with a subquery -- but if you're just looking to run this once, I'd recommend scripting it, looping the results, running the deletes, and being finished.
SELECT text, tel, max(time)
FROM ads
WHERE text = tel
group by text, tel
having count(time) > 1
I may be missing something here ...
SELECT text, tel, MAX(time)
FROM ads
WHERE text=tel
GROUP BY text, tel
精彩评论