mysql在查询字段使用子查询

最新推荐文章于 2024-05-30 11:28:09 发布

q_q_hh

最新推荐文章于 2024-05-30 11:28:09 发布

阅读量1.3k

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_21298819/article/details/127867987

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

mysql在查询字段使用子查询

仅仅作为一个mysql语法格式使用

第一种：

SELECT 
(select count(a.id) from t_customer a where a.fd_business_man_id = b.id and a.del_flag = 0 ) as '客户数',
(select count(a.id) from  t_customer_follow a where a.fd_follow_man_id = b.id and a.del_flag = 0 ) as '客户跟进数',
(SELECT count(s.id) from t_contacts s LEFT JOIN t_customer a on s.fd_customer_id = a.id where a.fd_business_man_id = b.id and s.del_flag = 0) as '联系人',
(select count(a.id) from t_commercial_chance a where a.fd_business_man_name = b.id and a.del_flag = 0 ) as '商机数'
from  t_resource_info b  WHERE b.fd_user_id = 'wangxia';

第二种：

SELECT 
	,count(DISTINCT(ta.id) ) as '客户数'
	,count(DISTINCT(tb.id) ) as '客户跟进数'
	,count(DISTINCT(tc.id) ) as '联系人'
	,count(DISTINCT(td.id) ) as '商机数'
from 
t_resource_info t 
  join (SELECT a.id,a.fd_business_man_id from t_customer a where a.del_flag = 0) ta on ta.fd_business_man_id = t.id 
  join  (SELECT a.id,a.fd_follow_man_id from t_customer_follow a where a.del_flag =0) tb on tb.fd_follow_man_id = t.id  
  join (SELECT s.id,a.fd_business_man_id from t_contacts s LEFT JOIN t_customer a on s.fd_customer_id = a.id and s.del_flag = 0 ) tc on tc.fd_business_man_id = t.id
 join (SELECT a.id,a.fd_business_man_name from t_commercial_chance a where a.del_flag = 0) td on td.fd_business_man_name = t.id 
where t.fd_user_id = 'XXX';