显示两个不同列的值的查询php mysql

Question

我有两个包含字段和值的表：

表重发

Id      Corp_resp            Id_perg     Id_quest 
12       0,2                 1             1
13       0,5                 2             1
14       0,7                 3             1
15       0,2                 4             1
16       0,3                 5             1
17       0,6                 6             1

表格菜单

Id_perg         percentagemadministrador Id_quest
1                             0.5        1    
2                             0.4        1         
3                             0.3        1          
4                             0,2        1
5                             0,1        1
6                             0,8        1

我希望将id_quest与id_perg相关联，并将corp_resp与表respostas的最后10行的percentagemadministrador相乘

表的最后10行的total=SUM(corp_resp*percentagemadministrador)。

<?php
mysql_connect("localhost","root","") or die(mysql_error());
mysql_select_db("teste") or die(mysql_error()); // selecciona a database do server escolhido

//something like that

$query ="SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
         FROM respostas 
         INNER JOIN menu 
         WHERE respostas.id_perg=menu.id_perg 
         AND respostas.id_quest=menu.id_perg 
         LIMIT id_perg=10";

$resultado = mysql_query($query) or die(mysql_error());

if($row = mysql_fetch_array($resultado)){

    echo " <center> Final: </center> ".$row['TOTAL'];
} ?>

你能帮上忙吗？

Answer 1

我认为您应该使用subselect来仅获取前10个第一个id

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas INNER JOIN menu 
WHERE respostas.id_perg=menu.id_perg 
AND respostas.id_quest=menu.id_perg 
AND id_perg in (select id_perg from respostas order by id_perg limit 10);

如果不能工作，我们可以尝试使用内部连接

SELECT SUM(menu.percentagemadministrador*respostas.corp_resp) as TOTAL 
FROM respostas 
INNER JOIN menu on ( respostas.id_perg=menu.id_perg 
     AND respostas.id_quest=menu.id_perg)  
INNER JOIN  ( select id_perg from  respostas order by id_perg limit 10) as t2 
 on (respostas.id = t2. id)