MySQL搜索条件与排名 [英] MySql search ranking with criteria

问题描述

我有一个名为 customers 的表，用于保存客户的数据

I have table named customers that keeps the customer's data

id  | fname  | lname  
--- | ------ | ------  
 1  | John   | Smith
 2  | Mike   | Bolton
 3  | Liz    | John
 4  | Mark   | Jobs

我还有一个名为 calls 的表，该表保留了对每个客户的每次呼叫.

And i have another table named calls that keeps each call made to each customer.

id |     timestamp     | customer_id | campaign | answered |
 1 |2016-09-05 15:24:08|      1      |  2016-09 |     1    |
 2 |2016-09-05 15:20:08|      2      |  2016-09 |     1    |
 3 |2016-08-05 15:20:08|      2      |  2016-08 |     1    |
 4 |2016-08-05 13:20:08|      3      |  2016-08 |     1    |
 5 |2016-08-01 15:20:08|      3      |  2016-08 |     0    |
 5 |2016-08-01 12:20:08|      4      |  General |     1    | 

广告系列常规不计入计算.

我需要根据每个客户的通话记录按通话质量的排名来获取订购的客户的列表.

I need to get a list of customers ordered by ranking of calling quality based on each customer calling history.

此列表用于致电客户，以便:

This list is use to call the customers in order that:

• 尚未在实际的致电广告系列中被致电(例如， 2016-09 )
• 通话次数减少
• 最佳接听百分比(已接听电话总数/已接听电话总数)

它应该看起来像这样:

| id | fname  | lname | %ans | called actual campaign | total calls | rank |
|----|--------|-------|------|------------------------|-------------|------|
| 4  | Mark   | Jobs  | N/A  |          no            |      0      |   1  |
| 3  | Liz    | John  |  50  |          no            |      2      |   2  |
| 1  | John   | Smith | 100  |          yes           |      1      |   3  | No Show  
| 2  | Mike   | Bolton| 100  |          yes           |      2      |   4  | No Show  

请帮助我！

推荐答案

该查询将计算每个客户对指定广告系列的总通话和已接电话

The query which counts for each customer total calls and answered calls for the specified campaign

select 
    c.id,
    count(*) as total_calls,
    sum(case when answered=1 then 1 else 0 end) as answered_calls
from customer c
     join calls cs on c.id=cs.customer_id
where cs.campaign='2016-09'
group by c.id

然后，您可以将上面的查询用作要订购的子查询

Then you can use the query above as a subquery to order

select sub.id, (@rank:=@rank+1) as rank
from (the subquery above) sub, (select @rank:=1)
order by 
  case when sub.total_calls=0 then 0 else 1,
  sub.total_calls, 
  sub.answered_calls*100/sub.total_calls

您可以在结果查询中包括任何所需的列

You can include any desired columns in the result query

这篇关于MySQL搜索条件与排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！