Mysql 交叉表查询 JOIN

“交叉表”对象是一个网格用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势.汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总求和、计数等互联网方面这种纯SQL统计的做法很少,性能低下仅供面试学习Case具有两种格式。简单Case函数和Case搜索函数。--简单Case函数 CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女 ELSE 其他 END --Case搜索函数 CASE WHEN sex 1 THEN 男 WHEN sex 2 THEN 女 ELSE 其他 END同样的我们也可以用这个方法来判断工资的等级并统计每一等级的人数。SQL代码如下:SELECT CASE WHEN salary 500 THEN 1 WHEN salary 500 AND salary 600 THEN 2 WHEN salary 600 AND salary 800 THEN 3 WHEN salary 800 AND salary 1000 THEN 4 ELSE NULL END salary_class, COUNT(*) FROM Table_A GROUP BY CASE WHEN salary 500 THEN 1 WHEN salary 500 AND salary 600 THEN 2 WHEN salary 600 AND salary 800 THEN 3 WHEN salary 800 AND salary 1000 THEN 4 ELSE NULL END;asdCreate table CJ(name char(10),subject char(10),result int)DEFAULT CHARSETutf8; insert into CJ(name,subject,result) values(张三,语文,99); insert into CJ(name,subject,result) values(张三,数学,86); insert into CJ(name,subject,result) values(张三,英语,75); insert into CJ(name,subject,result) values(李四,语文,78); insert into CJ(name,subject,result) values(李四,数学,85); insert into CJ(name,subject,result) values(李四,英语,78) select * from CJ想变成如下的交叉表select name,sum(a.result) as 合计总分, sum(case when a.subject语文 then result else null end) as 语文, sum(case when a.subject数学 then result else null end) as 数学, sum(case when a.subject英语 then result else null end) as 英语 from CJ a group by name;图解MySQL的joinMySQL CROSS JOINCROSS JOIN交叉连接又称笛卡尔积连接它会返回左表的每一行与右表的每一行进行组合的结果。数学表达如果表 A 有 M 行表 B 有 N 行CROSS JOIN 结果将有 M × N 行。使用场景CROSS JOIN 是一种数据生成工具当你需要穷举所有可能的组合时它是最高效的 SQL 写法。需要生成所有可能的颜色 × 尺寸商品属性组合。┌─────────────────┐ ┌─────────────────┐ │ colors │ │ sizes │ ├─────────────────┤ ├─────────────────┤ │ id1, 红色 │ │ id1, S │ │ id2, 蓝色 │ │ id2, M │ └─────────────────┘ │ id3, L │ 2 行 └─────────────────┘ 3 行 │ CROSS JOIN │ ▼ ┌─────────────────────┐ │ 商品属性组合(2×3) │ ├─────────────────────┤ │ 红色 | S │ │ 红色 | M │ │ 红色 | L │ │ 蓝色 | S │ │ 蓝色 | M │ │ 蓝色 | L │ └─────────────────────┘asdf-- 生成所有颜色×尺寸的SKU组合 INSERT INTO sku (product_id, color_id, size_id, sku_code) SELECT 1001 AS product_id, -- 假设商品ID为1001 c.color_id, s.size_id, CONCAT(SKU-1001-, c.color_id, -, s.size_id) AS sku_code FROM colors c CROSS JOIN sizes s; -- 结果示例 -- SKU-1001-1-1 (红色, S) -- SKU-1001-1-2 (红色, M) -- SKU-1001-1-3 (红色, L) -- SKU-1001-2-1 (蓝色, S) -- ...补历史记录数据SELECT vcc_id from ccods where telidcallout; 根据查询的vcc_id执行 INSERT INTO win_agstat_reason (vcc_id, ag_stat, stat_reason, yn, is_rest, belong_to) VALUES ({vcc_id}, 1, 忙碌, 1,, 0, 1), ({vcc_id}, 1, 小休, 1, 0, 1);需求说明从 ccods 查出所有 telid callout 的 vcc_id每个企业插入「忙碌」「小休」两行CROSS JOIN 生成两行数据忙碌、小休NOT EXISTS 保证已存在的记录不会被重复插入INSERT INTO win_agstat_reason (vcc_id, ag_stat, stat_reason, yn, is_rest, belong_to) SELECT c.vcc_id, 1, r.stat_reason, 1, 0, 1 FROM cods c CROSS JOIN ( SELECT 忙碌 AS stat_reason UNION ALL SELECT 小休 ) r WHERE c.telid callout AND NOT EXISTS ( SELECT 1 FROM win_agstat_reason w WHERE w.vcc_id c.vcc_id AND w.ag_stat 1 AND w.stat_reason r.stat_reason );