mysql 数据库 ui查询_mysql 日常查询语句

前言：最近在核对两个系统的数据，通过数据库统一查询后，简单一些。写了以下的查询语句，留在这里当做备份。

期间因为select语句执行顺序，遇到where语句引用别名错误的问题，将select语句的顺序整理如下：

SELECT语句的完整语法为：MySQL允许在HAVING子句中使用别名，sql不允许。(7) SELECT

(8) DISTINCT

(1) FROM

(3) JOIN

(2) ON

(4) WHERE

(5) GROUP BY

(6) HAVING

(9) ORDER BY

(10) LIMIT

1、查询两个系统的合计金额

SELECT SUM(价税合计) FROM crm_income AS ci;

SELECT SUM(价税合计) FROM u8_income AS ui;

2、查询明细数据

因为底表中没有唯一标识，一个发货退货单号对应着好几条存货，所以首先创建一个新表，分组汇总，用发货退货单号作为主键。

-- crm收入

#将crm收入查询结果保存到新表中

CREATE TABLE C_income AS

SELECT ci.`发货退货单号`,SUM(ci.`价税合计`)AS 合计,ci.`负责人所属部门`,ci.`客户名称`,ci.`业务员名称`

FROM crm_income AS ci

GROUP BY ci.`发货退货单号`

ORDER BY ci.`发货退货单号`;

#查看列名

SHOW FULL COLUMNS FROM C_income;

#修改列名

ALTER TABLE C_income CHANGE COLUMN 业务员名称 业务员 VARCHAR(30);

ALTER TABLE c_income CHANGE COLUMN 负责人所属部门 部门 VARCHAR(30);

#更改列类型

ALTER TABLE C_income MODIFY 业务员名称 VARCHAR(30);

#查询新表中的重复值

select COUNT(*) AS repeat_count,`发货退货单号` ,SUM(`合计`)

from C_income

GROUP BY `发货退货单号`

HAVING COUNT(*)>1;

-- u8收入

#将u8收入查询结果保存到新表中

CREATE TABLE u_income AS

SELECT ui.`发货退货单号`,SUM(ui.`价税合计`)AS 合计,ui.`部门名称`,ui.`客户名称`,ui.`业务员名称`

FROM u8_income AS ui

GROUP BY ui.`发货退货单号`

ORDER BY ui.`发货退货单号`;

#查看列名

SHOW FULL COLUMNS FROM u_income;

#修改列名

ALTER TABLE u_income CHANGE COLUMN 业务员名称 业务员 VARCHAR(30);

ALTER TABLE u_income CHANGE COLUMN 部门名称 部门 VARCHAR(30);

#查询新表中的重复值

select COUNT(*) AS repeat_count,`发货退货单号` ,SUM(`合计`)

from u_income

GROUP BY `发货退货单号`

HAVING COUNT(*)>1;

查看调整完底表后的数据是否一致

#查看4个表的合计是否相同

SELECT SUM(价税合计) FROM crm_income AS ci;

SELECT SUM(价税合计) FROM u8_income AS ui;

SELECT SUM(合计) FROM c_income;

SELECT SUM(合计) FROM u_income;

3、比对明细数据

#crm有此订单，U8中没有此订单

SELECT csi.`发货退货单号`,SUM(合计)AS 合计 FROM c_income AS csi

GROUP BY csi.`发货退货单号`

HAVING csi.`发货退货单号` not in (select usi.`发货退货单号` FROM u_income AS usi);

#u8有此订单，crm中没有此订单

#明细

SELECT usi.`发货退货单号`,SUM(合计)AS 合计 FROM u_income AS usi

GROUP BY usi.`发货退货单号`

HAVING usi.`发货退货单号` not in (select csi.`发货退货单号` FROM c_income AS csi);

#合计

SELECT SUM(合计)AS 合计 FROM u_income AS usi

WHERE usi.`发货退货单号` not in (select csi.`发货退货单号` FROM c_income AS csi);

-- crm、u8两者都有此订单，但是对应明细数据不同

#合计

SELECT usi.`发货退货单号`,usi.`合计`,csi.`发货退货单号`,csi.`合计`

FROM u_income as usi

INNER JOIN c_income AS csi

ON usi.`发货退货单号`=csi.`发货退货单号`

WHERE usi.`合计`!=csi.`合计`

#业务员

#先将业务员字段中的'(密码)'替换

UPDATE c_income SET 业务员=REPLACE(业务员,'(密码)','')

UPDATE u_income SET 业务员=REPLACE(业务员,'(密码)','')

#比对数据

SELECT usi.`发货退货单号`,usi.`合计`,usi.`业务员`,csi.`发货退货单号`,csi.`合计`,csi.`业务员`

FROM u_income as usi

INNER JOIN c_income AS csi

ON usi.`发货退货单号`=csi.`发货退货单号`

WHERE usi.`业务员`!=csi.`业务员`

#部门

SELECT usi.`发货退货单号`,usi.`合计`,usi.`业务员`,usi.`部门`,csi.`发货退货单号`,csi.`合计`,csi.`业务员`,csi.`部门`

FROM u_income as usi

INNER JOIN c_income AS csi

ON usi.`发货退货单号`=csi.`发货退货单号`

WHERE usi.`部门`!=csi.`部门`

4、比对分组数据

-- 部门

#通过部门分组

SELECT usi.`部门`,SUM(usi.`合计`)AS 合计 FROM `u_income` AS usi

GROUP BY usi.`部门`;

SELECT csi.`部门`,SUM(csi.`合计`)AS 合计 FROM `c_income` AS csi

GROUP BY csi.`部门`;

#crm部门分组汇总

SELECT

CASE

WHEN csi.`部门`='央企事业二部' THEN'军工央企事业部'

WHEN csi.`部门`='密码应用业务部' THEN'密码应用事业部'

WHEN csi.`部门`='山东大区运维组' THEN'山东大区'

WHEN csi.`部门`IN('审计内控部','离职人员及其他','公共安全事业部','销售管理部')THEN'其他'

ELSE csi.`部门`

END AS 部门,SUM(`合计`)AS 合计

FROM `c_income`AS csi

GROUP BY

CASE

WHEN csi.`部门`='央企事业二部' THEN'军工央企事业部'

WHEN csi.`部门`='密码应用业务部' THEN'密码应用事业部'

WHEN csi.`部门`='山东大区运维组' THEN'山东大区'

WHEN csi.`部门`IN('审计内控部','离职人员及其他','公共安全事业部','销售管理部')THEN'其他'

ELSE csi.`部门`

END;

#单据对应的部门不一样

SELECT csi.`发货退货单号`,

CASE

WHEN csi.`部门`='央企事业二部' THEN'军工央企事业部'

WHEN csi.`部门`='密码应用业务部' THEN'密码应用事业部'

WHEN csi.`部门`='山东大区运维组' THEN'山东大区'

WHEN csi.`部门`IN('审计内控部','离职人员及其他','公共安全事业部','销售管理部')THEN'其他'

ELSE csi.`部门`

END crm部门,

csi.`业务员`,csi.`合计`,usi.`发货退货单号`,usi.`部门`,usi.`业务员`,usi.`合计`

FROM c_income AS csi

RIGHT JOIN u_income AS usi

ON csi.`发货退货单号`=usi.`发货退货单号`

HAVING crm部门<>usi.`部门`;