mysql查询

d7v8vwbk 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(272)

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

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;

这是我的零售商表，我想从中计算该表中的零售商总数
这是我的访问信息表，我想从中统计访问的零售商数量、反馈数量、承诺订单数量、库存数量和付款数量，这些都是在访问信息表的活动类型列中定义的

sql mysql mysql-workbench

来源：https://stackoverflow.com/questions/52271109/mysql-query-to-fetch-results-from-two-tables

1条答案

按热度按时间

oogrdqng1#

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

赞(0）回复(0）举报 2021-06-21

我来回答

mysql查询

1条答案

相关问题

热门标签

最新问答