MySQL查询语句整理

时间计算

获取当前日期

SELECT NOW(),CURDATE(),CURTIME();

获取前一天日期

SELECT  DATE_SUB(CURDATE(),INTERVAL 1 DAY);

 

获取后一天日期

SELECT  DATE_SUB(CURDATE(),INTERVAL -1 DAY);

 

年份差

SELECT TIMESTAMPDIFF(YEAR,‘2017-05-01‘, DATE_FORMAT(now(), ‘%Y-%m-%d‘))

 

月份差

SELECT TIMESTAMPDIFF(MONTH,‘2017-05-01‘, DATE_FORMAT(now(), ‘%Y-%m-%d‘))

 

天数差

SELECT datediff(DATE_FORMAT(now(), ‘%Y-%m-%d‘),DATE_FORMAT(‘2018-09-10‘,‘%Y-%m-%d‘))

 

按照年月日统计

SELECT
    COUNT(ppi.create_time) pmCount, <!-- 新增项目 -->
    SUM( fppb.receivable_money ) receivableMoneySum, <!-- 项目总额 -->
    SUM( fppb.receipts_money ) receiptsMoneySum <!-- 项目实收额 -->
FROM
    fm_project_payment_bill fppb
    LEFT JOIN pm_project_info ppi ON ppi.id = fppb.project_id
WHERE
    ppi.company_id = '1'
GROUP BY
    DATE_FORMAT(ppi.create_time, '%Y-%m-%s') <!-- 日 -->
    DATE_FORMAT(ppi.create_time, '%Y-%m') <!-- 月 -->
    DATE_FORMAT(ppi.create_time, '%Y') <!-- 年 -->

 

多表统计功能查询

SELECT
    contract_sum.contractMoneySum,  <!-- 合同款额 -->
    contract_sum.contractCount, <!-- 合同数 -->
    payment_record_sum.discountMoneySum,  <!-- 优惠额 -->
    payment_record_sum.discountMoneyCount, <!-- 优惠额笔数 -->
    payment_record_sum.receiptsMoneySum,  <!-- 实收款额 -->
    payment_record_sum.receiptsMoneyCount, <!-- 实收款额笔数 -->
    invoice_sum.invoiceMoneySum,  <!-- 开票款额 -->
    invoice_sum.invoiceMoneyCount  <!-- 开票款额笔数 -->
FROM
    im_cust_info ici
    LEFT JOIN (
        SELECT
            ppi.cust_id,
            ifnull( sum( ppc.contract_money ), 0 ) contractMoneySum, <!-- 合同款额 -->
            count( ppc.id ) contractCount <!-- 合同数 -->
        FROM
            pm_project_info ppi
            LEFT JOIN pm_project_contract ppc ON ( ppi.id = ppc.project_id )
        GROUP BY
            ppi.cust_id 
    ) contract_sum ON ( ici.id = contract_sum.cust_id )
    LEFT JOIN (
    SELECT
        fppb.cust_id,
        ifnull( sum( fppr.discount_money ), 0 ) discountMoneySum, <!-- 优惠额 -->
        sum( CASE WHEN ifnull( fppr.discount_money, 0 ) = 0 THEN 0 ELSE 1 END ) discountMoneyCount, <!-- 优惠额笔数 -->
        ifnull( sum( fppr.receipts_money ), 0 ) receiptsMoneySum, <!-- 实收款额 -->
        count( fppr.id ) receiptsMoneyCount <!-- 实收款额笔数 -->
    FROM
        fm_project_payment_bill fppb
        LEFT JOIN fm_project_payment_record fppr ON ( fppb.id = fppr.ppb_id )
    GROUP BY
        fppb.cust_id 
    ) payment_record_sum ON ( ici.id = payment_record_sum.cust_id )
    LEFT JOIN (
    SELECT
        ppi.cust_id,
        ifnull( sum( fii.invoice_money ), 0 ) invoiceMoneySum,
        count( fii.id ) invoiceMoneyCount 
    FROM
        pm_project_info ppi
        LEFT JOIN fm_invoice_info fii ON ( ppi.id = fii.project_id ) 
    GROUP BY
        ppi.cust_id 
    ) invoice_sum ON ( ici.id = invoice_sum.cust_id ) 
WHERE
    1 = 1

 

当关联查询的表存在一对多关系时

SELECT
    COUNT( 1 ) allTabNum,
    IFNULL( SUM( CASE WHEN  TIMESTAMPDIFF( MONTH, sub.actual_time, DATE_FORMAT( now( ), '%Y-%m-%d' ) ) > 2   THEN 1 ELSE 0 END ),0 ) conventionTabNum,
    IFNULL( SUM( CASE WHEN  TIMESTAMPDIFF( MONTH, sub.actual_time, DATE_FORMAT( now( ), '%Y-%m-%d' ) ) <= 2  THEN 1 ELSE 0 END ),0 ) retentionTabNum
FROM
    tb_prod_single_product psp
    LEFT JOIN tb_prod_stock_info psi ON ( psi.prod_id = psp.id )
    LEFT JOIN (
        select max(pisa.actual_time) actual_time, <!-- 查询出相同id数据中最大的入库时间 -->
                pisa.single_prod_id
        from tb_prod_inout_stock_account_detail pisa
        where IFNULL(pisa.dj_state,0) = 0 
        and IFNULL(pisa.is_delete,0) = 0
        group by pisa.prod_id  <!-- 将关联的id分组以避免笛卡尔积 -->
        ) sub ON  psp.id = sub.single_prod_id
WHERE
        IFNULL( psp.is_delete, 0 ) = 0
        AND psi.date_type = '1'

 

mysql根据汉字首字母升序排列

SELECT * FROM tableName bct ORDER BY CONVERT (colum_name USING gbk) COLLATE gbk_chinese_ci asc

 

用一条SQL语句查询出每门课都大于80 分的学生姓名

name   kecheng   fenshu
张三    语文       81
张三     数学       75
李四     语文       76
李四     数学       90
王五     语文       81
王五     数学       100
王五     英语       90

select distinct name from table where name not in (select distinct name from table where fenshu<=80)
select name from table group by name having min(fenshu)>80

 

删除除了自动编号不同, 其他都相同的学生冗余信息

自动编号   学号   姓名 课程编号 课程名称 分数
1        2005001 张三 0001     数学    69
2        2005002 李四 0001      数学    89
3        2005001 张三 0001      数学    69

delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

 

查询表A中存在ID重复三次以上的记录

select * from (select count(ID) as count from table group by ID) T where T.count>3