有用的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() )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 "备注"