mysql 结果倒置_mysql-从SELECT查询中获得相反的结果

这些是我的表：

`room`(roomID,roomNum)

`customer`(customerID,Surname,etc)

`contract`(contractID,roomID,weekNum)

`paymen`t(paymentID,customerID,contractID,YearKoino)

当我使用以下查询时：

`select` room.roomnum

`from` payment,contract,room,customer

`where` payment.contractID = contract.contractID

`and` contract.roomID=room.roomID

`and` customer.customerID=payment.customerID

`and` contract.weeknum='40'

`and` payment.YearKoino='2007' ;

我得到的结果是：

+---------+

| roomnum |

+---------+

| Δ-12 |

| Γ-22 |

| Α-32 |

| Γ-21 |

| Δ-11 |

| Ε-12 |

| Γ-31 |

| Ε-22 |

| Α-22 |

| Δ-12 |

| Γ-12 |

+---------+

11 rows in set

我想做的是运行一个查询,该查询给出与我完全相反的结果(表房中不包含在表付款中的Roomnum),这可以通过将上述查询的Roomum结果与房间中的Roomnum列进行比较来完成table.到目前为止,我的一些努力：

`Select` room.roomnum

`from` room

`where` NOT EXISTS

(`select` room.roomnum

`from` payment,contract,room,customer

`where` payment.contractID = contract.contractID

`and` contract.roomID=room.roomID

`and` customer.customerID=payment.customerID

`and` contract.weeknum='40'

`AND` payment.YearKoino='2007');

Empty set

和

`SELECT` *

`FROM` customer a

`LEFT OUTER JOIN` payment b

`on` a.customerID=b.customerID

`where` a.customer is null;

我也尝试用“ NOT IN”代替“ NOT EXISTS”,但徒劳.我已经读到,做到这一点的最佳方法是使用“ left join”.当我不得不比较时,我可以做到这一点.简单的表.但在我的示例中,我必须将列与表联接中的列进行比较…

任何意见,将不胜感激.

解决方法:

我不确定为什么您不参与.

这应该可以工作(不要与表名别名一起使用)：

Select r1.roomnum

from room AS r1

where r1.roomnum NOT IN

(select r2.roomnum

from payment,contract,room as r2,customer

where payment.contractID = contract.contractID

and contract.roomID=r2.roomID

and customer.customerID=payment.customerID

and contract.weeknum='40'

AND payment.YearKoino='2007');

标签：mysql,join,select,left-join

来源： https://codeday.me/bug/20191011/1891228.html