史上最難面試題 · web前端

SQL in JS（LINQ版本）在 JavaScript 中實現類似 LINQ 風格的數據查詢語法。具體來說，就是用： from() 表示數據源，可以有多個不同的數據源，將會進行笛卡爾乘積 select() 表示數據投影（選擇） where() 表示過濾條件，可以組合多個，多個 where 之間是 AND 連接，where 中多個參數是 OR 連接的 groupBy() 表示分組條件（相同結果就會合成一組，可以同時指定好多分組條件，結果將是嵌套分組的） having() 表示從分組結果中進行選擇（選出特定的分組）類似與 where 以上所有函數都需要鏈式調用風格，但是無需考慮中間斷開保存為某個變量的情形，因此放心的用閉包即可。使用例子： const somenumbers = [1, 2, 3] query().select().from(somenumbers).execute(); //[1, 2, 3] query().from(somenumbers).select().execute(); //[1, 2, 3] execute前順序無關哦 //同樣也適用于對象數組 var persons = [ {name: '彼得', profession: '教師', age: 20, maritalStatus: '已婚'}, {name: '邁克爾', profession: '教師', age: 50, maritalStatus: '未婚'}, {name: '彼得', profession: '教師', age: 20, maritalStatus: '已婚'}, {name: '安娜', profession: '科學家', age: 20, maritalStatus: '已婚'}, {name: '露絲', profession: '科學家', age: 50, maritalStatus: '已婚'}, {name: '安娜', profession: '科學家', age: 20, maritalStatus: '未婚'}, {name: '安娜', profession: '政治家', age: 50, maritalStatus: '已婚'} ]; query().select().from(persons).execute(); // [{name: '彼得',...}, {name: '邁克爾', ...}] function profession(person) { return person.profession; } //也可以做一些投影操作 query().select(profession).from(persons).execute(); //["教師", "教師", "教師", "科學家", "科學家", "科學家", "政治家"] //除了where和having之外，其他的操作不允許重復寫 query().select().select().execute(); //Error('Duplicate SELECT'); query().select().from([]).select().execute(); //Error('Duplicate SELECT'); query().select().from([]).from([]).execute(); //Error('Duplicate FROM'); query().select().from([]).where([]).where([]) //這表示AND連接兩個條件 //你可以省略execute前的任何一部分 query().select().execute(); //[] query().from(somenumbers).execute(); // [1, 2, 3] query().execute(); // [] //需要支持where語法 function isTeacher(person) { return person.profession === 'teacher'; } //SELECT profession FROM persons WHERE profession="teacher" query().select(profession).from(persons).where(isTeacher).execute(); //["教師", "教師", "教師"] //SELECT * FROM persons WHERE profession="teacher" query().select().from(persons).where(isTeacher).execute(); //[{person: '彼得', profession: '教師', ...}, ...] function name(person) { return person.name; } //SELECT name FROM persons WHERE profession="teacher" query().select(name).from(persons).where(isTeacher).execute();//["彼得", "邁克爾", "彼得"] //還需要支持groupBy語法 //SELECT * FROM persons GROUP BY profession <- Bad in SQL but possible in this kata query().select().from(persons).groupBy(profession).execute(); [ ["教師", [ { name: "彼得", profession: "教師" ... }, { name: "邁克爾", profession: "教師" ... } ] ], ["科學家", [ { name: "安娜", profession: "科學家" }, ... ] ] ... ] //where和groupBy可以同時存在 //SELECT * FROM persons WHERE profession='teacher' GROUP BY profession query().select().from(persons).where(isTeacher).groupBy(profession).execute(); //或者，你可能需要select(下面這個例子演示了如何模擬select unique) function professionGroup(group) { return group[0]; } //SELECT profession FROM persons GROUP BY profession query().select(professionGroup).from(persons).groupBy(profession).execute(); //["教師","科學家","政治家"] //分組不一定要從對象數組里分 function isEven(number) { return number % 2 === 0; } function parity(number) { return isEven(number) ? '偶數' : '奇數'; } const numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9]; //SELECT * FROM numbers query().select().from(numbers).execute(); //[1, 2, 3, 4, 5, 6, 7, 8, 9] //SELECT * FROM numbers GROUP BY parity query().select().from(numbers).groupBy(parity).execute(); //[["奇數",[1,3,5,7,9]],["偶數",[2,4,6,8]]] //你甚至可以嵌套多個分組 function isPrime(number) { if (number < 2) { return false; } var divisor = 2; for(; number % divisor !== 0; divisor++); return divisor === number; } function prime(number) { return isPrime(number) ? '素數' : '合數'; } //SELECT * FROM numbers GROUP BY parity, isPrime query().select().from(numbers).groupBy(parity, prime).execute(); // [["奇數",[["合數",[1,9]],["素數",[3,5,7]]]],["偶數",[["素數",[2]],["合數",[4,6,8]]]]] //都有groupby了，怎么能少了having function odd(group) { return group[0] === 'odd'; } query().select().from(numbers).groupBy(parity).having(odd).execute(); //[["奇數",[1,3,5,7,9]]] //排序也是很重要滴 function descendentCompare(number1, number2) { return number2 - number1; } //SELECT * FROM numbers ORDER BY value DESC query().select().from(numbers).orderBy(descendentCompare).execute(); //[9,8,7,6,5,4,3,2,1] //from需要支持多個不同來源 var teachers = [ { teacherId: '1', teacherName: '彼得' }, { teacherId: '2', teacherName: '安娜' } ]; var students = [ { studentName: '邁克爾', tutor: '1' }, { studentName: '露絲', tutor: '2' } ]; function teacherJoin(join) { return join[0].teacherId === join[1].tutor; } function student(join) { return {studentName: join[1].studentName, teacherName: join[0].teacherName}; } //SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor query().select(student).from(teachers, students).where(teacherJoin).execute(); //[{"studentName":"邁克爾","teacherName":"彼得"},{"studentName":"露絲","teacherName":"安娜"}] //where和having可以支持多個參數，表示OR連接 function tutor1(join) { return join[1].tutor === "1"; } //SELECT studentName, teacherName FROM teachers, students WHERE teachers.teacherId = students.tutor AND tutor = 1 query().select(student).from(teachers, students).where(teacherJoin).where(tutor1).execute(); //[{"studentName":"邁克爾","teacherName":"彼得"}] <- AND 連接 function lessThan3(number) { return number < 3; } function greaterThan4(number) { return number > 4; } //SELECT * FROM number WHERE number < 3 OR number > 4 query().select().from(numbers).where(lessThan3, greaterThan4).execute(); //[1, 2, 5, 7] <- OR連接 function greatThan1(group) { return group[1].length > 1; } function isPair(group) { return group[0] % 2 === 0; } function id(value) { return value; } function frequency(group) { return { value: group[0], frequency: group[1].length }; } //SELECT number, count(number) FROM numbers GROUP BY number HAVING count(number) > 1 AND isPair(number) query().select(frequency).from(numbers).groupBy(id).having(greatThan1).having(isPair).execute(); // [{"value":2,"frequency":2},{"value":6,"frequency":2}]) 答案： ~~~ const query = function() { let projection = null let source = null let conditionAnd = [] let orderExpr = null let groupExpr = [] let havingAnd = [] const group = method => next => datasource => { const ret = [] datasource.forEach(d => { const key = method(d) const found = ret.find(x => x[0] == key) if (found) { found[1].push(d) } else { ret.push([key, [d]]) } }) return ret.map(it => [it[0], next(it[1])]) } const self = { select(_projection) { if (projection !== null) throw new Error('Duplicate SELECT') projection = _projection return self }, from(..._source) { if (source !== null) throw new Error('Duplicate FROM') source = _source return self }, where(...conditionOr) { conditionAnd.push(conditionOr) return self }, orderBy(expr) { if (orderExpr !== null) throw new Error('Duplicate ORDERBY') orderExpr = expr return self }, groupBy(...expr) { if (groupExpr.length != 0) throw new Error('Duplicate GROUPBY') groupExpr = expr return self }, having(...havingOr) { havingAnd.push(havingOr) return self }, execute() { source = source || [[]] projection = projection || (_ => _) orderExpr = orderExpr || (() => false) const doJoin = src => { if (src.length == 1) return src[0] const res = [] src.reverse().reduce((p, c) => { return (...da) => c.forEach(i => p(...da, i)) }, (...data) => res.push(data))() return res } const doFilter = cnd => src => src.filter(x => cnd.reduce((pAnd, cAnd) => pAnd && cAnd.reduce((pOr, cOr) => pOr || cOr(x),false),true)) const doGroup = src => groupExpr.map(group).reverse().reduce((p, c) => c(p), _ => _)(src) const doSelect = src => src.map(projection) const doSort = src => src.sort(orderExpr) const deb = (f, x) => { console.log(f, x) return x } return [doJoin, doFilter(conditionAnd), doGroup, doFilter(havingAnd), doSelect, doSort].reduce((p, c) => deb(c, c(p)), source) } } return self }; ~~~