有用的sql笔记（工作总结）

1、查询当前月（数字为0表示当前月份，1表示上个月，-1表示下个月，以此类推）

SELECT DATE_FORMAT((CURDATE() - INTERVAL [数字] MONTH), '%Y-%m') as `date`

 

 2、添加数据库表字段

ALTER TABLE t_performance_group_plan ADD COLUMN submit_approval_time DATETIME DEFAULT NULL COMMENT '提交审批时间' AFTER financial;

 3、insert根据select查询结果进行插入

INSERT INTO t_performance_group_detail (group_id, member_name) 
SELECT 
  tp.`id`,
  t1.`nick_name` 
FROM
  sys_user t1 

 4、查询结果中根据A字段分组，将B字段合并展示（使用函数 group_concat（）  ）

SELECT 
  'FNS' AS `source_code`,
  CONCAT(`am`.`month_id`, '-out') AS `source_trx_id`,
  `am`.`month_id` AS `month_id`,
  `ai`.`sp_no` AS `approval_number`,
  `tu`.`NAME` AS `applicant_by_name`,
  `tu`.`user_code` AS `applicant_by`,
  `td`.`dept_name` AS `dept_name`,
  `td`.`dept_id` AS `dept_id`,
  `ai`.`apply_time` AS `apply_time`,
  (
    CASE
      WHEN (`ai`.`reimburse_type` = 1) 
      THEN '个人报销' 
      WHEN (`ai`.`reimburse_type` = 2) 
      THEN '公司报销' 
      ELSE NULL 
    END
  ) AS `reimburse_type`,
  `ai`.`sp_status` AS `sp_status`,
  `ai`.`country` AS `country`,
  `md`.`dic_value` AS `currency`,
  `tpm`.`payment_method_name` AS `payment_method`,
  `ft1`.`name` AS `apply_type`,
  `ai`.`payee` AS `payee`,
  `ai`.`company` AS `company`,
  `ai`.`accounting_date` AS `accounting_date`,
  `ai`.`accounting_month` AS `accounting_month`,
  `am`.`apply_accountid` AS `fee_bear_code`,
  `ft2`.`name` AS `fee_type`,
  `center`.`costcenter_name` AS `costcenter_name`,
  `ft3`.`name` AS `finance_expend_type`,
  `am`.`apply_fee` AS `apply_fee`,
  IF(
    (`ai`.`apply_category` = 1),
    `am`.`now_offset_fee`,
    0
  ) AS `writeOffFee`,
  `ca`.`pre_sp_no`,
  `am`.`tax_rate` AS `tax_rate`,
  `am`.`no_tax_money` AS `no_tax_money`,
  `am`.`tax_money` AS `tax_money`,
  `am`.`invoice_number` AS `invoice_number`,
  IF(
    (`ai`.`more_invoice` = 0),
    `ai`.`transfer_status`,
    `am`.`transfer_status`
  ) AS `transferStatus`,
  `am`.`payment_due_date` AS `payment_due_date`,
  `am`.`start_time` AS `start_time`,
  `am`.`end_time` AS `end_time`,
  `am`.`fee_desc` AS `fee_desc`,
  `ai`.`is_transfer` AS `is_transfer`,
  2 AS `environment`,
  `bid`.`batch_id` AS `sync_batch_id` 
FROM
  `t_accounting_month` `am` 
  LEFT JOIN `t_apply_info` `ai` 
    ON `ai`.`sp_no` = `am`.`sp_no` 
  LEFT JOIN `t_user` `tu` 
    ON `ai`.`applyer_userid` = `tu`.`USER_ID` 
  LEFT JOIN `t_dept` `td` 
    ON `ai`.`applyer_partyid` = `td`.`dept_id` 
  LEFT JOIN `t_unit` `unit` 
    ON `ai`.`applyer_accountid` = `unit`.`unit_id` 
  LEFT JOIN `t_unit_costcenter_map` `center` 
    ON `am`.`cost_center` = `center`.`costcenter_code` 
  LEFT JOIN 
    (SELECT 
      apply_sp_no,
      GROUP_CONCAT(DISTINCT ca.`pre_sp_no`) AS pre_sp_no 
    FROM
      connect_apply ca 
    WHERE ca.`apply_sp_no` IN 
      (SELECT 
        `ai`.`sp_no` AS `approval_number` 
      FROM
        `t_accounting_month` `am` 
        LEFT JOIN `t_apply_info` `ai` 
          ON `ai`.`sp_no` = `am`.`sp_no` 
        LEFT JOIN `t_user` `tu` 
          ON `ai`.`applyer_userid` = `tu`.`USER_ID` 
        LEFT JOIN `t_dept` `td` 
          ON `ai`.`applyer_partyid` = `td`.`dept_id` 
        LEFT JOIN `t_unit` `unit` 
          ON `ai`.`applyer_accountid` = `unit`.`unit_id` 
        LEFT JOIN `t_unit_costcenter_map` `center` 
          ON `am`.`cost_center` = `center`.`costcenter_code` 
        LEFT JOIN `m_dictionary` `md` 
          ON `ai`.`apply_currency` = `md`.`dic_key` 
        LEFT JOIN `t_payment_method` `tpm` 
          ON `ai`.`pay_method` = `tpm`.`payment_method_code` 
        LEFT JOIN `t_batch_id_month` `bid` 
          ON `bid`.`month` = DATE_FORMAT(`ai`.`accounting_date`, '%Y-%m') 
        LEFT JOIN `t_fee_type` `ft1` 
          ON `ft1`.`code` = `ai`.`apply_type` 
        LEFT JOIN `t_fee_type` `ft2` 
          ON `ft2`.`code` = `am`.`fee_type` 
        LEFT JOIN `t_fee_type` `ft3` 
          ON `ft3`.`code` = `am`.`finance_expend_type` 
      WHERE (
          (`ai`.`sp_status` IN (20, 10, 13)) 
          AND (
            `am`.`apply_accountid` NOT IN ('COMPANY', 'FINA')
          ) 
          AND (`tu`.`STATUS` = 1) 
          AND (`am`.`fas_transfer_status` = 0) 
          AND (
            DATE_FORMAT(`ai`.`accounting_date`, '%Y-%m') = "2022-05"
          )
        ) 
      GROUP BY `ai`.`sp_no`) 
      AND ca.`status` = 0 
      AND ca.`pre_type` = 1 
    GROUP BY ca.`apply_sp_no`) `ca` 
    ON `ca`.`apply_sp_no` = `ai`.`sp_no` 
  LEFT JOIN `m_dictionary` `md` 
    ON `ai`.`apply_currency` = `md`.`dic_key` 
  LEFT JOIN `t_payment_method` `tpm` 
    ON `ai`.`pay_method` = `tpm`.`payment_method_code` 
  LEFT JOIN `t_batch_id_month` `bid` 
    ON `bid`.`month` = DATE_FORMAT(`ai`.`accounting_date`, '%Y-%m') 
  LEFT JOIN `t_fee_type` `ft1` 
    ON `ft1`.`code` = `ai`.`apply_type` 
  LEFT JOIN `t_fee_type` `ft2` 
    ON `ft2`.`code` = `am`.`fee_type` 
  LEFT JOIN `t_fee_type` `ft3` 
    ON `ft3`.`code` = `am`.`finance_expend_type` 
WHERE (
    (`ai`.`sp_status` IN (20, 10, 13)) 
    AND (
      `am`.`apply_accountid` NOT IN ('COMPANY', 'FINA')
    ) 
    AND (`tu`.`STATUS` = 1) 
    AND (`am`.`fas_transfer_status` = 0) 
    AND (
      DATE_FORMAT(`ai`.`accounting_date`, '%Y-%m') = "2022-05"
    )
  ) 
GROUP BY `am`.`month_id` 

 5、查询 | 删除数据表中根据多字段查询存在多条重复数据（删除重复数据，留下id最小的一个）

SELECT * FROM afee a
WHERE (a.fee) IN (SELECT fee FROM afee GROUP BY fee HAVING COUNT(*) > 1)
AND id NOT IN (SELECT MIN(id) FROM afee GROUP BY fee HAVING COUNT(*)>1)

DELETE 
FROM
  afee 
WHERE id IN 
  (SELECT 
    res.id 
  FROM
    (SELECT 
      a.id 
    FROM
      afee a 
    WHERE (a.fee) IN 
      (SELECT 
        fee 
      FROM
        afee 
      GROUP BY fee 
      HAVING COUNT(1) > 1) 
      AND a.id NOT IN 
      (SELECT 
        MIN(id) 
      FROM
        afee 
      GROUP BY fee 
      HAVING COUNT(1) > 1)) AS res)

注：此种方式删除时，子查询结果应“包一层”，否则会抛出You can't specify target table 'afee' for update in FROM clause错误

6、更新数据库表字段类型

ALTER TABLE t_save_apply_info MODIFY COLUMN all_fee DECIMAL(12,2) COMMENT "总费用";

7、增加字段

alter table 表名 add 字段名 字段类型（长度） comment "备注"