比较结果与其他表mysql [英] compare result with other table mysql

问题描述

我有2个表我选择和计数比较项目形式2表，也比较后，我需要计算多少项目包含在其他表。

I have 2 table I select and count compare item form 2 tables, also after compare I need to compute how many item contain in other table.

select 
    results.userid,
    results.amount,
    results.type,
    results.counting
from
    (SELECT 
        userid, amount, code, count(*) as counting
    FROM
        user_buys
    join star ON (amount >= min_amount)
    group by type
    HAVING amount >= 1000) as results

< b $ b

with table below

 userid   |amount     
----------------------
  1       |   1000        
  2       |   2000   
  3       |   5500    
  4       |   8200     
  5       |   200     
  6       |   1500 
  7       |   800

我需要与其他表格同步

 min_compare|  min_amount |   type
-----------------------------------
  2         |   1000      |    1star
  2         |   2000      |    2star
  3         |   5000      |    3star
  4         |   8000      |    4star
  5         |   9000      |    5star
  6         |   10000     |    6star
  7         |   11000     |    7star

因为我们有

5 item larger 1000 it contain => 1star
3 item larger 2000 it contain => 2star
2 item larger 5000 it contain => 3star
1 item larger 8000 it contain => 4star

我的预期结果

 rankin
--------
  1star           
  2star
  3star  
  4star 

我有另一个问题，开始计数与min_compare有关，如果我添加新购买与11000它必须有更少的数字7计算7星

i have another question, count of number start relate to min_compare , if i add new buy with 11000 it must have less number with 7 to compute 7star

推荐答案

尝试此操作）

select
    star.type, star.min_amount, t.cnt, t.userids
from star
inner join (
    select  t1.type, count(t2.userid) as cnt, group_concat(t2.userid order by t2.userid) as userids
    from star t1
    inner join user_buys t2 on t1.min_amount <= t2.amount
    group by t1.type
) t on t.type = star.type
order by star.type

此处的SQLFiddle DEMO

这篇关于比较结果与其他表mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！