最受欢迎

根据另一个 MYSQL 查询结果对 MYSQL 查询进行排序

Order MYSQL query based on another MYSQL querys results

本文关键字：MYSQL 查询 排序 结果 另一个 | 更新日期: 2023-09-27

我有 2 个 sql 查询，第一个根据 AREA 和 CATEGORY 从数据库中选择产品

它是一家家具店，此查询适用于餐桌。

SELECT p.id,
   p.product_name,
   p.range_name,
   p.pic_url,
   p.hits,
   p.manufacturer,
   p.standard_price,
   p.sale_price,
   p.offer_price,
   p.discount,
   p.display_manufacturer,
   p.display_price,
   p.visible,
   p.display_model_results,
   p.model,
   p.subcat,
   p.disable_options,
   p.request_price,
   p.corner_flag,
   cf.flag_name,
   cf.flag_url,
   cf.flag_description,
   p.display_model,
   p.offer_status,
   p.disable_options_results,
   p.added_timestamp,
   p.about,
   p.keywords
FROM   products p,
   corner_flags cf
WHERE  ( p.area = 'Dining Room Furniture'
      OR p.additionarea = 'Dining Room Furniture' )
   AND ( p.subcat = 'Dining Tables'
          OR p.additionsubcat = 'Dining Tables' )
   AND p.sale_price = ''
   AND p.visible = '1'
   AND p.corner_flag = cf.flag_id
   AND ( p.sale_price = ''
          OR p.offer_price = '0' )
   AND p.visible = '1'
 ORDER  BY ( p.hits ) DESC

每次查看产品时，产品表中的字段"命中"都会增加 1。起初这还可以，但现在如果客户决定按受欢迎程度订购结果，新产品总是在列表的底部。

我们

还有另一个表格可以跟踪我们网站上的每次点击，因此我编写了一个查询来计算过去 2 周查看特定产品的次数。

SELECT Count(*) AS total,
   vtp.product_id
FROM   visitor_tracking_pages vtp,
   products p
WHERE  vtp.product_id = p.id
   AND p.subcat = 'Dining Tables'
   AND p.visible = '1'
   AND vtp.last_click >= '1380153600'
GROUP  BY vtp.product_id
ORDER  BY total DESC

此查询提供产品 ID 和过去 2 周内收到的总点击数。

我只需要一种方法将这两个查询合并为一个。显然，并非每个产品都会在过去 2 周内被查看，因此 id 需要一种返回 0 的方法，如果这有什么不同？

我查看了各种 JOIN，但我不熟悉如何编写它们。

非常感谢对您的任何帮助！

试试这个

SELECT p.id,
   p.product_name,
   p.range_name,
   p.pic_url,
   p.hits,
   p.manufacturer,
   p.standard_price,
   p.sale_price,
   p.offer_price,
   p.discount,
   p.display_manufacturer,
   p.display_price,
   p.visible,
   p.display_model_results,
   p.model,
   p.subcat,
   p.disable_options,
   p.request_price,
   p.corner_flag,
   cf.flag_name,
   cf.flag_url,
   cf.flag_description,
   p.display_model,
   p.offer_status,
   p.disable_options_results,
   p.added_timestamp,
   p.about,
   p.keywords,
   IFNULL(vtp.last_two_week_hits, 0) as last_two_week_total_hits
FROM corner_flags cf INNER JOIN  products p
     ON cf.flag_id = p.corner_flag
    LEFT JOIN
       (SELECT v.product_id as product_id,Count(*) as last_two_week_hits
            FROM   visitor_tracking_pages v
           WHERE v.last_click >= '1380153600' group by v.product_id 
        ) as vtp ON vtp.product_id = p.id
WHERE  p.visible = '1' AND ( p.area = 'Dining Room Furniture'
      OR p.additionarea = 'Dining Room Furniture' )
   AND ( p.subcat = 'Dining Tables'
          OR p.additionsubcat = 'Dining Tables' )
   AND ( p.sale_price = ''
          OR p.offer_price = '0' )
 ORDER BY last_two_week_total_hits DESC

我认为这解决了您的问题，正确的连接：

SELECT COUNT(*) AS total, p.id as product_id
FROM visitor_tracking_pages vtp RIGHT JOIN products p ON p.id=vtp.product_id
WHERE p.SUBCAT = 'Dining Tables' AND p.visible = '1' AND (vtp.last_click >= '1380153600' or vtp.last_click IS NULL)
GROUP BY p.id
ORDER BY total DESC
RIGHT(或 LEFT(JOIN

执行标准 JOIN，就像在你编写的查询中一样，然后将右(或左(表中与联接不匹配的所有行相加。

要合并您发布的两个查询，您可以执行此查询

SELECT p.id, p.product_name, p.range_name, p.pic_url, p.hits, p.manufacturer, p.standard_price, p.sale_price, p.offer_price, p.discount, p.display_manufacturer, p.display_price, p.visible, p.display_model_results, p.model, p.SUBCAT, p.disable_options, p.request_price, p.corner_flag, cf.flag_name, cf.flag_url, cf.flag_description, p.display_model, p.offer_status, p.disable_options_results, p.added_timestamp, p.about, p.keywords
FROM products p, corner_flags cf LEFT JOIN visitor_tracking_pages vtp ON vtp.product_id=p.id
WHERE (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') AND p.sale_price='' AND p.visible='1' AND p.corner_flag = cf.flag_id AND (p.sale_price= '' OR p.offer_price = '0') AND p.visible='1'
AND (vtp.last_click >= '1380153600' or vtp.last_click IS NULL)
GROUP BY p.id
ORDER BY COUNT(*), p.hits DESC

我知道这不是完美的方式，因为通常字段列表中唯一允许的字段是 GROUP BY 子句中提到的字段，但它有效，您可以在 ORDER BY 子句中使用 COUNT(*(。

执行所需操作的另一种方法是使用我编写的第一个查询(例如 *view_order*(创建一个视图，然后将其用作数字以避免使用 GROUP BY 的各种问题，使用此查询：

SELECT p.id, p.product_name, p.range_name, p.pic_url, p.hits, p.manufacturer, p.standard_price, p.sale_price, p.offer_price, p.discount, p.display_manufacturer, p.display_price, p.visible, p.display_model_results, p.model, p.SUBCAT, p.disable_options, p.request_price, p.corner_flag, cf.flag_name, cf.flag_url, cf.flag_description, p.display_model, p.offer_status, p.disable_options_results, p.added_timestamp, p.about, p.keywords
FROM products p, corner_flags cf JOIN view_order vtp ON vtp.product_id=p.id
WHERE (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') AND p.sale_price='' AND p.visible='1' AND p.corner_flag = cf.flag_id AND (p.sale_price= '' OR p.offer_price = '0') AND p.visible='1'
ORDER BY vtp.totale, p.hits DESC

感谢您的帮助！

Ravi - 您的优化有效，但由于某种原因，它停止返回过去 14 天内未查看的产品。

最后，我终于找到了一个可行的解决方案。

如果您看到任何进一步的改进，请告诉我:)

我创建了一个名为 popular_products 的新表，并编写了一个脚本来每天(通过 cron 作业(使用查询visitor_tracking_pages表的新命中计数填充此表。

删除表以在添加新结果之前删除前几天的结果。

$timestamp = strtotime("-2 week");
mysql_query("TRUNCATE TABLE  `popular_products`");
$result = mysql_query("SELECT COUNT(*) AS total, product_id 
FROM visitor_tracking_pages vtp 
WHERE  vtp.product_id != '' AND vtp.last_click >= '$timestamp' 
GROUP BY product_id");
while($row = mysql_fetch_array($result)){
mysql_query("INSERT INTO popular_products (product_id, total_views) 
VALUES ('$row[1]','$row[0]')");
}

然后，这允许我将其添加到结果中，而无需即时运行查询。

过去 2 周内未查看的产品返回为 NULL，这很好。

SELECT p.id, 
   p.product_name, 
   p.range_name, 
   p.pic_url, 
   p.hits, 
   p.manufacturer, 
   p.standard_price, 
   p.sale_price, 
   p.offer_price, 
   p.discount, 
   p.display_manufacturer, 
   p.display_price, 
   p.visible, 
   p.display_model_results, 
   p.model, 
   p.subcat, 
   p.disable_options, 
   p.request_price, 
   p.corner_flag, 
   cf.flag_name, 
   cf.flag_url, 
   cf.flag_description, 
   p.display_model, 
   p.offer_status, 
   p.disable_options_results, 
   p.added_timestamp, 
   p.about, 
   p.keywords, 
   (SELECT pp.total_views 
       FROM popular_products pp 
       WHERE pp.product_id = p.id 
   ) AS total_views 
FROM products p, corner_flags cf 
WHERE p.visible='1' 
   AND (p.AREA='Dining Room Furniture' OR p.AdditionAREA='Dining Room Furniture') 
   AND (p.SUBCAT='Dining Tables' OR p.AdditionSUBCAT='Dining Tables') 
   AND p.corner_flag = cf.flag_id 
   AND p.sale_price= '' 
ORDER BY ABS(total_views) DESC

有什么方法可以进一步过滤命中数吗？基本上，一些访问者多次在产品之间来回切换，这些结果显示为产品获得了更多实际拥有的视图。

产品visitor_tracking_pages表中的查询显示该产品总共被查看了 7 次，但实际上只有 5 个唯一身份访问者查看了它。

SELECT ip_address, product_id
FROM  `visitor_tracking_pages` 
WHERE  `product_id` =  '1562805'
AND  `last_click` >=  '1380153600'
| ip_address   | product_id |
-----------------------------
| 192.168.0.1  | 1562805    |
| 192.168.0.1  | 1562805    |
| 192.168.0.2  | 1562805    |
| 192.168.0.3  | 1562805    |
| 192.168.0.4  | 1562805    |
| 192.168.0.1  | 1562805    |
| 192.168.0.5  | 1562805    |
-----------------------------
相关文章：
最新更新
www.56WenDa.com 2012-2023 | php问答网 | php学习