Mysql“不在”选择问题

Question

我只需要在一张表中找到今天参加比赛的所有人员，而不是前几天的获胜者...

我的查询是：

SELECT Email,FName,Name,ID 
FROM `voucher_registrations` 
WHERE DATE_FORMAT(`Date_Submitted`,'%Y-%c-%d') = curdate() 
and Email not in (select email 
                  from `voucher_registrations` 
                  where winner=1)

出现问题...当我尝试执行查询时，mysql 没有响应（ 表中大约有 7000 行）

有什么想法吗？或者用另一种方式进行这种查询？

尼古拉斯

Answer 1

您可以在电子邮件字段上添加索引以加快查询速度，并在子查询中选择“DISTINCT”电子邮件以减少搜索值：

SELECT Email,FName,Name,ID 
FROM `voucher_registrations` 
WHERE Date_Submitted = CURDATE()
  AND Email not in 
    (SELECT DISTINCT Email FROM`voucher_registrations` WHERE winner=1)

Answer 2

尝试一下（与你的略有不同）：

SELECT Email,FName,Name,ID 
FROM `voucher_registrations` 
WHERE DATE_FORMAT(`Date_Submitted`,'%Y-%c-%d') = curdate() 
  AND Email NOT IN 
    (SELECT DISTINCT email FROM `voucher_registrations`
     WHERE winner = 1)

Answer 3

SELECT vr1.Email,vr1.FName,vr1.Name,vr1.ID 
    FROM `voucher_registrations` vr1 
        LEFT JOIN `voucher_registrations` vr2
            ON vr1.Email = vr2.Email
                AND vr2.winner = 1
    WHERE DATE_FORMAT(vr1.`Date_Submitted`,'%Y-%c-%d') = curdate() 
        AND vr2.Email IS NULL /* Email was not found among winners */

Answer 4

这不是等价的吗：

SELECT v.Email,v.FName,v.Name,v.ID 
FROM `voucher_registrations` v 
JOIN (select email from `voucher_registrations` where winner <>1)t on t.email=v.email
WHERE DATE_FORMAT(`v.Date_Submitted`,'%Y-%c-%d') = curdate() 

Answer 5

SELECT Email,FName,Name,ID 
FROM `voucher_registrations` 
WHERE DATE_FORMAT(`Date_Submitted`,'%Y-%c-%d') = curdate() 
and Email <> (select email 
              from `voucher_registrations` 
              where winner=1)