SQL相关

列别名不能用于where子句

 

动态sql，某一列值作为子查询的表名，或者某一列值为一个sql语句，直接查询结果

 

内存表

 

按日期分组

 select count(*),substr(t.date,1,10)  from table t where  t.date like '2010-03%' group by substr(t.date,1,10) 

 

over开窗函数

 

防止插入重复数据

INSERT INTO data_group_call_link (
    groupcall_id,
    data_id,
    data_phone,
    data_source
)(
    SELECT
        "3" groupcall_id,
        uuid data_id,
        item_phone data_phone,
        "k" data_source
    FROM
        data_item_b23209a6517c42da9f7338290a181f16
    WHERE
        NOT EXISTS (
            SELECT
                *
            FROM
                data_group_call_link
            WHERE
                item_phone = data_group_call_link.data_phone
            OR item_owner IS NOT NULL
        
)

 

根据条件做count操作

SELECT
    p.container_name DATA,
    count(t.data_source) num,
    sum(
        CASE
        WHEN t.call_flag > "0" THEN
            1
        ELSE
            0
        END
    ) complete
FROM
    data_group_call_link t
JOIN data_container p ON t.data_source = p.data_table
WHERE
    t.groupcall_id = "3"
GROUP BY
    t.data_source