json-like-sql插件使用说明

import Json from "json-like-sql";

const tmp = [
    {
        taskIdTX: 1,
        dateTime: '13',
        taskLocationId: '11',
    },
    {
        taskIdTX: 2,
        dateTime: '12',
        taskLocationId: '1',
    },
    {
        taskIdTX: 3,
        dateTime: '166',
        taskLocationId: '1', 
    },
    {
        taskIdTX: 33,
        dateTime: '16',
        taskLocationId: '1',
    },
    {
        taskIdTX: 4,
        dateTime: '17',
        taskLocationId: '2', 
    }
]
const tt = [
    {
        dateTime: '12', 
        age: '123',
        name: '123',
        xx: '11',
        yy: '13',
    },
    {
        dateTime: '13', 
        age: '33',
        name: '44',
        xx: '55',
        yy: '66',
    }
]


const db = new Json(tmp)

// 1.条件查询
db.select(['taskLocationId', 'dateTime as dt']).get()

查询结果
[
    {"taskLocationId":"11","dt":"13"},
    {"taskLocationId":"1","dt":"12"},
    {"taskLocationId":"1","dt":"166"},
    {"taskLocationId":"2","dt":"17"},
    {"taskLocationId":"1","dt":"16"}
]


// 2.条件查询
db.select(['taskLocationId', 'dateTime as dt']).groupBy(['taskLocationId']).get()

查询结果
[
    {
        "taskLocationId":"11",
        "children":[
            {"taskLocationId":"11","dt":"13"}
        ]
    },
    {  
        "taskLocationId":"1",
        "children":[
            {
                "taskLocationId":"1","dt":"12"
            },
            {
                "taskLocationId":"1","dt":"166"
            },
            {   
                "taskLocationId":"1","dt":"16"
            }
        ]
    },
    {
        "taskLocationId":"2",
        "children":[{"taskLocationId":"2","dt":"17"}]
    }
]

// 3.条件查询
db.groupBy(['taskLocationId'])
    .select([
        'taskLocationId as id', 
        'count(dateTime) as count',
        'dateTime as dt',
        'sum(dateTime) as sum',
        'min(dateTime) as min',
        'max(dateTime) as max',
        'avg(dateTime) as avg',
        'concat(dateTime) as dtstr',
    ])
    .get()

查询结果
[
    {
        "id":"11",
        "count":1,
        "sum":13,
        "min":13,
        "max":13,
        "avg":13,
        "dtstr":"13",
        "children":[
            {
                "dt":"13",
                "id":"11"
            }
        ]
    },
    {
        "id":"1",
        "count":3,
        "sum":194,
        "min":12,
        "max":166,
        "avg":64.66666666666667,
        "dtstr":"12、166、16",
        "children":[
            {
                "dt":"12",
                "id":"1"
            },
            {
                "dt":"166",
                "id":"1"
            },
            {
                "dt":"16",
                "id":"1"
            }
        ]
    },
    {
        "id":"2",
        "count":1,
        "sum":17,
        "min":17,
        "max":17,
        "avg":17,
        "dtstr":"17",
        "children":[
            {
                "dt":"17",
                "id":"2"
            }
        ]
    }
]

// 4.条件查询
db.select(['dateTime'])
    .leftJoin(tt, 'dateTime')
    .get()

查询结果
[
    {"dateTime":"13","age":"33","name":"44","xx":"55","yy":"66"},
    {"dateTime":"12","age":"123","name":"123","xx":"11","yy":"13"},
    {"dateTime":"166"},
    {"dateTime":"17"},
    {"dateTime":"16"}
]

// 5.条件查询
db.select(['dateTime'])
    .innerJoin(tt, 'dateTime')
    .get()

查询结果
[
    {"dateTime":"13","age":"33","name":"44","xx":"55","yy":"66"},{"dateTime":"12","age":"123","name":"123","xx":"11","yy":"13"}
]