子查询

Example

import emysql, { DefineTable } from '@aicblock/emysql'

// 数据库实例化
const mysql = new emysql({
  password: '[db登录密码]',
  user: '[db登录用户名]',
  database: '访问数据库名称'
})

// 初始化数据库
await mysql.init()
// 创建表结构
const t_user = DefineTable({
  tableName: 't_user',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '名称'
    },
    {
      name: 'dep_id',
      dataType: 'INT',
      notNull: true,
      comments: '部门id'
    }
  ]
})
const t_department = DefineTable({
  tableName: 't_department',
  columns: [
    {
      name: 'id',
      dataType: 'INT',
      primaryKey: true,
      autoIncrement: true,
      comments: '主键id'
    },
    {
      name: 'name',
      dataType: 'VARCHAR',
      length: 45,
      notNull: true,
      comments: '部门名称'
    },
    {
      name: 'p_id',
      dataType: 'INT',
      notNull: true,
      comments: '父部门id'
    }
  ]
})
// 创建表
await mysql.table.create([t_user, t_department])

// t_user 数据
const user = [
  { name: 'name1', dep_id: 1 },
  { name: 'name2', dep_id: 2 },
  { name: 'name3', dep_id: 3 },
  { name: 'name4', dep_id: 4 },
  { name: 'name5', dep_id: 5 }
]
// t_department 数据
const department = [
  { name: 'dep_name1', p_id: 0 },
  { name: 'dep_name2', p_id: 0 },
  { name: 'dep_name3', p_id: 1 },
  { name: 'dep_name4', p_id: 2 },
  { name: 'dep_name5', p_id: 0 }
]

// 查询 p_id = 0 的所属部门用户数据执行子查询
const result = await mysql.query({
  t: t_user,
  fields: ['name'],
  condition: {
    dep_id: {
      type: 'IN',
      subquery: { t: t_department, fields: ['id'], condition: { p_id: 0 } }
    }
  }
})
// result = [{ name: 'name1' }, { name: 'name2' }, { name: 'name5' }]