根據這個 SO answer，我們有：
SELECT DISTINCT s.id
FROM students s
LEFT JOIN grades g ON g.student_id = s.id
WHERE g.student_id IS NOT NULL
哪個使用LEFT JOIN. 但它可以用這樣的“半連接”來實作：
SELECT s.id
FROM students s
WHERE EXISTS (SELECT 1 FROM grades g
WHERE g.student_id = s.id)
students給定兩個集合和，你如何用 JavaScript 撰寫這兩個查詢grades？我認為第一個是使用“嵌套回圈連接”實作的（或者可以使用索引連接資料結構進行優化，但我還不知道如何實作）。
const query1Results = nestedJoin(students, grades, (s, g) => {
return Boolean(g.student_id && g.student_id === s.id)
}).map(([s]) => s.id)
function nestedJoin(R, S, compare) {
const out = []
for (const r of R) {
for (const s of S) {
if (compare(r, s)) {
out.push([ r, s ])
}
}
}
return out
}
你會怎么做第二個呢？
const query2Results = semiJoin(students, grades, (s, g) => {
return g.student_id === s.id
}).map(([s]) => s.id)
function semiJoin(R, S) {
const out = []
for (const r of R) {
for (const s of S) {
if (compare(r, s)) {
out.push([ r, s ])
}
}
}
return out
}
基本上，我以同樣的方式實作它。
const students = [
{ id: 1 },
{ id: 2 },
{ id: 3 },
{ id: 4 },
{ id: 5 },
{ id: 6 },
]
const grades = [
{ id: 1, student_id: 1 },
{ id: 2, student_id: 1 },
{ id: 3, student_id: 1 },
{ id: 4, student_id: 1 },
{ id: 5, student_id: 1 },
{ id: 6, student_id: 1 },
{ id: 10, student_id: 2 },
{ id: 20, student_id: 2 },
{ id: 30, student_id: 2 },
{ id: 40, student_id: 2 },
{ id: 50, student_id: 2 },
{ id: 60, student_id: 2 },
{ id: 100, student_id: 3 },
{ id: 200, student_id: 3 },
{ id: 300, student_id: 3 },
{ id: 400, student_id: 3 },
{ id: 500, student_id: 3 },
{ id: 600, student_id: 3 },
]
const expectedOutput = [
1, 2, 3
]
uj5u.com熱心網友回復：
這確實取決于您的表設計和索引，但假設您有一個grades.student_id，JS 等價物可能看起來像
const students = [{"id":1},{"id":2},{"id":3},{"id":4},{"id":5},{"id":6}]
const grades = [{"id":1,"student_id":1},{"id":2,"student_id":1},{"id":3,"student_id":1},{"id":4,"student_id":1},{"id":5,"student_id":1},{"id":6,"student_id":1},{"id":10,"student_id":2},{"id":20,"student_id":2},{"id":30,"student_id":2},{"id":40,"student_id":2},{"id":50,"student_id":2},{"id":60,"student_id":2},{"id":100,"student_id":3},{"id":200,"student_id":3},{"id":300,"student_id":3},{"id":400,"student_id":3},{"id":500,"student_id":3},{"id":600,"student_id":3}]
// index
const idxGradesStudentId = new Set(grades.map(({ student_id }) => student_id))
function semiJoin(R, S) {
// WHERE EXISTS
return R.filter(({ id }) => idxGradesStudentId.has(id))
.map(({ id }) => id) // SELECT
}
console.log(semiJoin(students, grades))因此，它不是使用嵌套回圈，而是使用 on 上的索引grades.student_id（由Set表示）來評估EXISTS子句 ( Set.prototype.has())，這具有O(1)查找時間復雜度的優勢。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/420157.html
標籤：