The delete from the table where id in (select id from (select Max (id) from the table where user_id=xx group by goods_id having count (goods_id) & gt; 1) a);
Look at the SQL original intention is to delete user id duplicate records goods, is clearly wrong, because the subquery is wrong, truth is
The delete from the table where id in (select from b (select Max (id) as b from the table where user_id=xx group by goods_id having count (goods_id) & gt; 1) a);

Personal struggle is not that, is SQL subquery is clearly wrong, but the real operation on the mysql logic is: when the user_id=xx is a record, to delte full table, analysis for a long time, don't know what's going on,
Remind, if I am careful, should check out the subquery, direct error is not the tragic

CodePudding user response:

Should not be performed,

CodePudding user response:

Clearer complex SQL execution step by step

CodePudding user response:

Perform a select id from (select Max (id) from the table where user_id=xx group by goods_id having count (goods_id) & gt; 1) a look

CodePudding user response:

Pay attention to your Max (id) is not named
Select id from (select Max (id)
-- -- -- -- -- -- -- -- this id is actually take the outer table id, because you have no id the column in the subquery, so since the straton queries have a record, then the results of this query is=outer id, id clearly meet the conditions in (select id
So not to delete a full table is wrong instead of

CodePudding user response:

In general, in the specification of the DBA, table will be asked to write clearly the name of the table, in order to avoid this kind of dog blood event, but that most programmers don't care

CodePudding user response:

The

reference 4 floor ZJCXC reply:

pay attention to your Max (id) is not named
Select id from (select Max (id)
-- -- -- -- -- -- -- -- this id is actually take the outer table id, because you have no id the column in the subquery, so since the straton queries have a record, then the results of this query is=outer id, id clearly meet the conditions in (select id
So not to delete a full table is wrong instead of

Select id from (select Max (id) why when no Max (id) this column, select id from will read full table id?

CodePudding user response:

Select id from (select Max (id) why when no Max (id) this column, select id from will read full table id?
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- the inside of the subquery select id returns is not a full table, outer table of the current record is the id of the
The delete from the table where id in (
The delete here is a quick, for each article, all to judge a second query, so for each article, the subquery return a with the current record id corresponding to the id of the record, the end result is, of course, all meet the conditions of