mysql+union+不存在时_mysql – 如果表2中不存在对,则从表1(使用UNION)中选择2行

所以我有两张桌子：

表uno：

id | gender | lf

-------------------------

abc | 1 | 2

cde | 2 | 1

efg | 1 | 2

表二人组：

id1 | id2

-------------------------

abc | cde

我的工作(到目前为止)查询从表uno中精确选择了2行：

(SELECT

*

FROM

uno

WHERE gender = 1

AND lf = 2

ORDER BY RAND()

LIMIT 1)

UNION

(SELECT

*

FROM

uno

WHERE gender = 2

AND lf = 1

ORDER BY RAND()

LIMIT 1)

哪个回报：

id | gender | lf

-------------------------

abc | 1 | 2

cde | 2 | 1

我需要的(并且还没弄清楚是否可以使用查询)是返回2个未在表二重奏中配对的ID.

在这个例子中,上面的查询不应该返回abc和cde,因为它们已经在table duo中(可能的对是efg和abc,efg和cde,因为它们在表duo中找不到).

谢谢！

更新：

在chiliNUT的帮助下,我带来了这个：

(SELECT

id,gender,lf

FROM

uno u1

WHERE NOT EXISTS

/* id1-centric exclusion rule */

/* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */

(SELECT

d.id1,

d.id2,

u2.id

FROM

duo d

LEFT JOIN uno u2 /* note the JOIN order, duo on uno */

ON d.id2 = u2.id

WHERE d.id1 = u1.id)

/* id2-centric exclusion rule */

/* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */

AND NOT EXISTS

(SELECT

d.id1,

d.id2,

u2.id

FROM

uno u2

LEFT JOIN duo d /* note the JOIN order, uno on duo */

ON d.id1 = u2.id

WHERE d.id2 = u1.id) and (gender=1 and lf=2)order by rand() limit 1)UNION(SELECT

id,gender,lf

FROM

uno u1

WHERE NOT EXISTS

/* id1-centric exclusion rule */

/* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */

(SELECT

d.id1,

d.id2,

u2.id

FROM

duo d

LEFT JOIN uno u2 /* note the JOIN order, duo on uno */

ON d.id2 = u2.id

WHERE d.id1 = u1.id)

/* id2-centric exclusion rule */

/* look at id1 FROM duo, exclude it IF it IS IN uno AND id2 IS also IN uno */

AND NOT EXISTS

(SELECT

d.id1,

d.id2,

u2.id

FROM

uno u2

LEFT JOIN duo d /* note the JOIN order, uno on duo */

ON d.id1 = u2.id

WHERE d.id2 = u1.id) and (gender=2 and lf=1) order by rand() limit 1)

我不得不编辑所以它会产生2个ID(具有不同的性别和lf).这肯定没有优化,可能会挂起我的数据库,但它是一个开始！

谢谢辣椒！