MySql 查询从两个表中获取结果

Question

我想从零售商表中获取零售商总数，并从访问信息表中获取其他属性。此查询为零售商总数和访问过的零售商提供了正确的结果，但为反馈计数、 promise 订单计数等两列提供了错误的结果。

select count(distinctrow tbl_retailer.retailer_id) as total_retailers,count(distinctrow tbl_visit_info.retailer_id) as visited_retailers,
SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
from tbl_visit_info,tbl_retailer 
where visitor_id=175 and tbl_retailer.sr_id=175;

This is my retailer table from which I want to count total retailers from this table

This is my visit_info table from which I want to count visited_retailers, feedback count, Promise_order_count_stock_count and payment count which are defined as in activity_type column in visit_info table

Answer 1

有两个子查询，每个表一个。 JOIN 结果。

select * from

(select sr_id, count(distinctrow retailer_id) as total_retailers
 from tbl_retailer 
 group by sr_id) tr

join

(select visitor_id,
        count(distinctrow retailer_id) as visited_retailers,
        SUM( IF( tbl_visit_info.activity_type = 1 ,1,0)) AS feedback,
        SUM( IF( tbl_visit_info.activity_type = 2 ,1,0)) AS promise_order,
        SUM( IF( tbl_visit_info.activity_type = 3 ,1,0)) AS stock,
        SUM( IF( tbl_visit_info.activity_type = 6 ,1,0)) AS payment
 from tbl_visit_info
 group by visitor_id) tvi

on tvi.visitor_id = tr.sr_id
where tvi.visitor_id = 175

只需删除 WHERE 子句即可获取有关所有访问者的信息!

顺便说一句，这里可能不需要distinctrow。