代码之家 › 专栏 › 技术社区 › flx

如何用联接(或类似的)替换子查询

sql mysql

flx · 技术社区 · 6 年前

我知道我应该一个人解决,但我陷入了绝望

谁借给了施密茨女士同样的书?打印出他们的姓氏

你是虽然

这是我的尝试:

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
WHERE ExID IN (
  SELECT ExID
  FROM Ausleihe
         LEFT JOIN Leser L2 on Ausleihe.LeserOID = L2.LeserOID
  WHERE l2.Nachname = 'Schmitz'
);

这是数据库结构。

**Buch -> Book**
BuchOID -> BookOID
Titel -> Title
Verfasser -> Author

**Vormerkung -> Reservartion**
LeserOID -> ReaderOID
BuchOID -> BookOID
VormDat -> Reservation date

**Leser -> Reader**
LeserOID -> ReaderOID
Nachname -> Last name
Mail

**Exemplar -> Copy**
BuchOID -> BookOID
ExID -> CopyID
AnschDat -> Date of aqusition

**Ausleihe -> Lending**
BuchOID -> BookOID
ExID -> CopyID
LeserOID -> ReaderOID
LNr -> *PK of this table*
ADat -> Lending date
RDat -> Return date

**Mahnung -> Reminder**
MahnungOID -> ReminderOID
LeserOID - > ReaderOID
BuchOID -> BookOID
ExID -> CopyID
Datum -> date
Betrag -> amount

2 回复 | 直到 6 年前

Fahmi 6 年前

你可以在下面试试-

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
JOIN Leser L2 on a.LeserOID = L2.LeserOID
   WHERE l2.Nachname = 'Schmitz'

Zeljka 6 年前

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
LEFT JOIN Leser L2 on Ausleihe.LeserOID = L2.LeserOID
WHERE L2.Nachname is not null and L2.Nachname = 'Schmitz'

或者有了内部连接,你可以这样做

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
INNER JOIN Leser L2 on (Ausleihe.LeserOID = L2.LeserOID AND and L2.Nachname = 'Schmitz')

或者就这样

SELECT l.Nachname
FROM Ausleihe as a
LEFT JOIN Leser as l on a.LeserOID = l.LeserOID
INNER JOIN Leser L2 on Ausleihe.LeserOID = L2.LeserOID
WHERE L2.Nachname = 'Schmitz'

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

4 月前

Shukurullox Komiljonov · 从记录中获得相互和解。使用SQL

5 月前

John D · 需要为NULL或NOT NULL的WHERE子句

5 月前

ojek · 如何对SQL结果进行分组和编号?

5 月前

senek · 如何在PL/SQL中将选择结果(列)放入数组中

5 月前

Sax · 规范化Google表格(第一步)

5 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

5 月前

Jatin · 检索卷计数的动态sql抛出错误语法错误[关闭]

5 月前

Andrus · 如何在sql中查找第二个匹配项

5 月前

Nebula Tech · 在SQL Server中搜索字符串中单词的多次出现

5 月前