关于UPDATE的Mysql问题

Question

UPDATE counter_reports 
SET `counter`=`counter`+1,`date`=?
WHERE report_id IN(
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `year`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `month`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `week`=1 
                    ORDER BY report_id DESC LIMIT 1),
                   (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `day`=1 
                    ORDER BY report_id DESC LIMIT 1)
                  )

这样的sql有什么替代方案吗？我需要更新(增加 1)天、周、月和年的最后计数器报告。

如果我手动添加，sql 工作正常，但对于子查询它无法启动。

谢谢。 :)

Answer 1

MySQL 有点蹩脚，这样做，就可以了:

UPDATE counter_reports 
SET `counter`=`counter`+1,`date`=?
WHERE report_id IN(
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `year`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `month`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `week`=1 
                    ORDER BY report_id DESC LIMIT 1) as x),
                   (select report_id from (SELECT report_id 
                    FROM counter_reports 
                    WHERE report_name="emails_sent" AND `day`=1 
                    ORDER BY report_id DESC LIMIT 1) as x)
                  )

另请查看此处的最后一个示例(Mysql 代码，与您的问题相关):http://mssql-to-postgresql.blogspot.com/2007/12/deleting-duplicates-in-postgresql-ms.html