MySQL使用不同表的字段对结果进行排序

I have 3 tables of the following structure

我有3个以下结构的表

    products
    {
        pid
        pname
        plocation...
    }

    services
    {
        s_id
        s_name
        s_location...
    }

    txn
    {
        tid
        pid...
    }

I am using the following query to get the values I require.

我使用以下查询来获取我需要的值。

    $dbquery = "SELECT DISTINCT products.pid AS id,
                   products.pname AS name,
                   products.p_desc AS description,
                   products.p_loc AS location,
                   products.category AS category,
                   products.p_uid AS userid,
                   products.add_date AS dateadded,
                   products.isaproduct AS whatisit
              FROM products
             WHERE products.pname
              LIKE '%".$keyword."%'
               AND category = '".$refine_value."'
             UNION
             SELECT DISTINCT services.s_id AS id,
                   services.s_name AS name,
                   services.s_desc AS description,
                   services.s_category AS category,
                   services.s_uid AS userid,
                   services.s_location AS location,
                   services.date_added AS dateadded,
                   services.isaservice AS whatisit
              FROM services
             WHERE services.s_name
              LIKE '%".$keyword."%'
               AND s_category = '".$refine_value."'

I need to order the values I select by getting the count of pid in txn table which I get in the query above.

我需要通过获取上面查询中获得的txn表中的pid计数来对我选择的值进行排序。

How can I do this?

我怎样才能做到这一点?

1 个解决方案

#1

First of all, use parameterized queries instead of building a query string like that. It bothers me.

首先,使用参数化查询而不是像这样构建查询字符串。这让我很烦。

But it looks like all you're really needing to do is to group by the pid column in the txn table.

但看起来你真正需要做的就是按照txn表中的pid列进行分组。

Define a view like

定义一个类似的视图

SELECT pid, COUNT(tid) tid_count
FROM txn
GROUP BY pid

And join it into your query, then order by tid_count.

并将其加入您的查询,然后通过tid_count订购。

#1