2015-06-03 08:50

如何在使用INNER JOIN时让MySQL选择随机获胜者

SELECT * FROM `surveys` INNER JOIN `surveyusers` ON `surveyusers`.`survID` = `surveys`.`survID` WHERE `surveyusers`.`hasWon` = 0 ORDER BY RAND();
UPDATE surveys SET winner = email;

This was a school assignment that required us to select and update the survey winner.

I use PHP to manage and show surveys, I want the SQL to pick the winner. PHP sets survID

I want to pick a random winner and set the winner coloumn in surveys table to the email in the surveyusers table. But I really have a lot of trouble with it.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • douzhanrun0497 douzhanrun0497 6年前

    Updates can take the same syntax as a Select.

    You can combine these two queries into one.

    UPDATE `surveys` `s` SET `s`.`winner` = (
        SELECT email FROM `surveyusers`
        WHERE `surveyusers`.`hasWon` = 0
        AND `survID` = 1
        ORDER BY RAND()
        LIMIT 1
    ) WHERE `survID` = 1;

    The survID you would have to set from PHP of course.

    点赞 评论 复制链接分享
  • dongzi3434 dongzi3434 6年前

    You can nest a SELECT query within your UPDATE query:

    UPDATE surveys SET winner = (
        SELECT `surveyusers`.email FROM `surveys` 
        INNER JOIN `surveyusers` ON `surveyusers`.`survID` = `surveys`.`survID` 
        WHERE `surveyusers`.`hasWon` = 0 ORDER BY RAND() LIMIT 1
    ) WHERE `survID` = xxx

    where xxx is the survey to update.

    点赞 评论 复制链接分享