table1和table2的某个字段相同如何进行关联查询?
问题:table1分类count如下,如何关联table2的单价并实现total_pass的值乘以单价算总价?
select product,type,
count(case when status='pass' then id end) total_pass,
count(sn) total_sn,
concat(round(count(case when status='pass' then id end)/count(sn)*100,2),'%') rate
from table1 group by product,type
效果:
product total_pass total_sn rate 总价
产品1 3 5 60.00% ¥30.00
产品2 4 4 100.00% ¥104.00
table1结构:
CREATE TABLE table1(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
sn VARCHAR(200) COMMENT '产品序号',
product VARCHAR(200) COMMENT '产品名称',
type VARCHAR(200) COMMENT '产品类别',
status VARCHAR(200) COMMENT '产品状态pass or ng'
) COMMENT='产品信息'
id sn product type status
1 AAAA 产品1 type1 pass
2 BBBB 产品1 type1 pass
3 CCCC 产品1 type1 pass
4 DDDD 产品1 type1 ng
5 EEEE 产品1 type1 ng
6 FFFF 产品2 type2 pass
7 GGGG 产品2 type2 pass
8 HHHHH 产品2 type2 pass
9 JJJJJJ 产品2 type2 pass
table2结构:
CREATE TABLE table2(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id',
type VARCHAR(200) COMMENT '产品类别',
price VARCHAR(200) COMMENT '单价',
) COMMENT='产品价格总表'
id type price
1 type1 ¥10.00
2 type2 ¥26.00
3 type3 ¥45.00
4 type4 ¥23.00
5 type5 ¥14.00