doutan6286
2018-01-31 12:30
浏览 57
已采纳

通过值的总和基于另一个表对表数据进行排序

I was wondering if its possible to sort a table based on the sums of values in another table. The issue im facing is the following: im running this query on my database:

<?php
$sql = "SELECT thread_id, SUM(upvoted) AS upvoted FROM upvotes GROUP BY thread_id ORDER BY upvoted LIMIT :start, {$number_threads}";
$stmt = $conn->prepare($sql);
$stmt->bindValue(':start', (int) $start, PDO::PARAM_INT);
$stmt->execute();

Thereafter im making this while loop

while($result = $stmt->fetch()) {
    // do something with the following variables
    $result['thread_id'];
    $result['thread_content'];
    $result['thread_ownerid'];                        
}

But the columns thread_id, thread_content and thread_ownerid do not exist inside the table upvotes. They only exist inside a table called threads. The most straightforward solution to this problem is joining. However, the problem with joining is that the values in the table upvotes are all non-unique. so a thread_id that is unique in threads can for instance be present in 5432 rows with corresponding -1 en 1 values in the column upvoted in the table upvotes.

My idea to go around this problem is: Make an array/dictionary with indices/keys that link the thread_id with the sum of all its upvotes and then join this array/dict with the table threads.

This is one theoretical way of accesing the desired variables, but any way that allows me to acces them will be greatly appreciated.

Thanks a lot in advance for investing time in my question!

图片转代码服务由CSDN问答提供 功能建议

我想知道是否可以根据另一个表中的值的总和对表进行排序。 面临的问题如下:我在我的数据库上运行此查询:

 &lt;?php 
 $ sql =“SELECT thread_id,SUM(upvoted)AS upvoted FROM  upvotes GROUP BY thread_id ORDER BY upvoted LIMIT:start,{$ number_threads}“; 
 $ stmt = $ conn-&gt; prepare($ sql); 
 $ stmt-&gt; bindValue(':start',(int)  $ start,PDO :: PARAM_INT); 
 $ stmt-&gt; execute(); 
   
 
 

此后即可进行此循环 \ n

  while($ result = $ stmt-&gt; fetch()){
 //使用以下变量执行某些操作
 $ result ['thread_id']; 
 $ result ['thread_content'  ]; 
 $ result ['thread_ownerid'];  
} 
   
 
 

但是表upvotes中不存在列thread_id,thread_content和thread_ownerid。 它们只存在于名为threads的表中。 这个问题最直接的解决方案是加入。 但是,加入的问题是表upvotes中的值都是非唯一的。 所以一个在线程中唯一的thread_id可以例如存在于5432行中,并且在表格upvotes中上升的列中具有相应的-1 en 1值。

我想解决这个问题 是:使用索引/键创建一个数组/字典,将thread_id与其所有upvotes的总和相关联,然后将此数组/ dict与表线程连接。

这是一种理论方法 访问所需的变量,但任何允许我访问它们的方式都将非常感激。

非常感谢您在我的问题中投入时间! < / DIV>

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douaoj0994 2018-01-31 13:14
    已采纳

    Yes, you can do it, but you have to add this value as a new column in your select result like this:

    select a.id,a.other,
            (select count(*) 
             FROM table2 AS B 
             WHERE b.id=a.id) AS c
    FROM table1  AS a
    ORDER BY c;
    

    The C value would be the count (or sum) of some values in other table

    打赏 评论