## LINQ 查询语法

magic-script 支持类 SQL 的 LINQ 语法，对内存集合进行查询，可与数据库结果配合使用。

### 基本查询
```magicscript
var list = [{name: '张三', age: 20, sex: 0}, {name: '李四', age: 30, sex: 1}]

// 过滤
return select * from list t where t.age > 18

// 选择字段 + 转换
return select t.name, t.age > 18 ? '成年' : '未成年' status from list t
```

### 分组聚合
```magicscript
var data = db.select('select dept_id, salary from employee')

return
    select
        t.dept_id,
        count(t.dept_id) total,
        sum(t.salary) totalSalary,
        avg(t.salary) avgSalary
    from data t
    group by t.dept_id
    order by sum(t.salary) desc
```

### JOIN 关联
```magicscript
// 注意：LINQ 中不用 as 别名，直接用空格分隔
// 使用 db.camel() 后，下划线列名会转为驼峰（如 dept_id -> deptId）
var users = db.camel().select('select name, dept_id from user')
var depts = db.camel().select('select id, name from department')

// 简单写法，不用 as 别名
return select u.name, d.name from users u left join depts d on u.deptId = d.id
```

### 空值处理
```magicscript
var list = [{name: null, age: 18}, {name: '张三', age: null}]

return select
    t.name || '未知' name,        // || 将 null/空字符串 替换
    ifnull(t.age, 0) age          // ifnull 仅处理 null
from list t
```

### LINQ + 分组聚合 + 排序
```magicscript
var orders = db.select('select * from orders')

return
    select
        t.category,
        count(t.id) orderCount,
        sum(t.amount) totalAmount
    from orders t
    group by t.category
    having sum(t.amount) > 1000
    order by totalAmount desc
```

### 嵌套查询 + 树形结构
```magicscript
var allDepts = db.select('select id, name, parent_id from department')

var toTree = (list, parentId) =>
    select t.*, toTree(list, t.id) children
    from list t
    where t.parent_id = parentId

return toTree(allDepts, '0')
```

### 子查询
```magicscript
return
    select * from (
        select
            t.dept_id,
            count(t.id) cnt
        from users t
        group by t.dept_id
    ) sub
    where sub.cnt > 5
    order by sub.cnt desc
```