md/mysql/技巧学习.md

查询相关

连表查询

统计相关(关联统计数量)

b表为售出的表情况,a表记录了总数量,现在需要查询指定商品2的售出情况.应该为 0/33

1. 关联表数据查询

2. 表结构 a表字段数据 | id | total | | --- | --- | | 1 | 23 | | 2 | 33 | b表字段数据 | aId | | 1 | | 1 | | 1 | 查询思路,使用count函数进行查询,count中会统计所有非null的值,所以判断后应该将false转换为null

   # 查询1
   select ff.*,count(aId = 1 or null) as pay
   from 
   (select total from a where id = 1) as ff,
   b;
   # 查询2
   select ff.*,count(aId = 2 or null) as pay
   from 
   (select total from a where id = 2) as ff,
   b;
   

   查询相关结果 1.结果1 | total | pay | | --- | --- | | 23 | 3 |

2.结果2 | total | pay | | --- | --- | | 33 | 0 |