时间戳

首页 > 解决方案 > MySql 查询从两个表中获取结果

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

问题描述

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

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;

这是我的零售商表，我想从该表中计算零售商总数

这是我的visit_info 表，我想从中计算visited_retailers、反馈计数、Promise_order_count_stock_count 和付款计数，它们在visit_info 表的activity_type 列中定义

标签： mysqlsqlmysql-workbench

解决方案

有两个子查询，每个表一个。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这里可能不需要。

推荐阅读