代码之家 › 专栏 › 技术社区 › Sasha Grievus

如何排除同时满足两个条件的记录?

mysql

Sasha Grievus · 技术社区 · 7 年前

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
ORDER BY g.guidUser, g.idGame

现在我想排除同时具有

我试过了

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus,g.idGameVersus,g.timeFinal 
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
** AND !(g.accepted!="" AND g.guidUserVersus='1') **
ORDER BY g.guidUser, g.idGame

但它似乎并没有选择它应该选择的记录,就像让g接受一样=“”和g.guidUserVersus='2'

这是小提琴 http://sqlfiddle.com/#!9/737e61/1

1 回复 | 直到 7 年前

Gixuna 7 年前

编辑:您需要使用或在条件内替换和。

SELECT g.guidUser,u1.username,g.sent,g.accepted,g.declined,g.rewarded,g.guidUserVersus,u2.username AS usernameUserVersus,g.idGameVersus,g.timeFinal 
FROM games g 
INNER JOIN userdata u1 ON g.guidUser=u1.guidUser 
INNER JOIN userdata u2 ON g.guidUserVersus=u2.guidUser 
WHERE 
((g.sent!="" OR g.accepted!="" OR g.declined!="") 
AND g.rewarded="" AND (g.guidUser='1' OR g.guidUserVersus='1')) 
AND NOT (g.accepted!="" OR g.guidUserVersus='1')
ORDER BY g.guidUser, g.idGame

推荐文章

Bard.Mus · 迁移后的数据库字符集环境

1 年前

tinkerr · 如何在2025年在MacOS上安装MySQL 5.7

1 年前