将子查询添加到网格查询 [英] Adding subquery to a grid query

问题描述

继这个问题，我有另一个查询，我需要从数据中的所有负数减去10的值。不幸的是，我只是不确定如何实现上一个问题中提供的相同子查询。

有问题的查询是

  SELECT 10 *（c.customer_x / 10），10 *（c.customer_y / 10），
 COUNT（*）as num_orders，
 SUM（o.order_total）
 FROM t_customer c 
 JOIN t_order o 
 ON c.customer_id = o.customer_id 
 GROUP BY c.customer_x / 10，c.customer_y / 10 
 ORDER BY SUM（o.order_total）DESC; 
  

计算每个网格平方的订单总数。

解决方案

您的原始查询在下面的查询中没有太多变化。唯一的区别是新的连接和一个添加到 SELECT 列表中的项：

  SELECT 10 *（c.customer_x / 10）AS col1，
 10 *（c.customer_y / 10）AS col2，
 COUNT（*）AS num_orders，
 SUM o.order_total）AS order_total_sum 
 FROM 
（
 SELECT customer_id，
 CASE WHERE customer_x <0 THEN customer_x  -  10 ELSE customer_x END AS customer_x，
 CASE WHEN customer_y < 0 THEN customer_y  -  10 ELSE customer_y END AS customer_y 
 FROM t_customer 
）c 
 INNER JOIN t_order o 
 ON c.customer_id = o.customer_id 
 GROUP BY c.customer_x / 10，
 c.customer_y / 10 
 ORDER BY SUM（o.order_total）DESC 
  

请注意，您可以在不使用我已使用的子查询的情况下解决此问题。然而，子查询使得它更具可读性，并让我们可以整齐地计算调整的 customer_x 和 customer_y 值。 p>

Following from this question, I have another query that I need to subtract a value of 10 from for all negative numbers in the data. Sadly, I'm just not sure how to implement the same subquery as is given in the previous question.

The query in question is

SELECT 10 * (c.customer_x / 10), 10 * (c.customer_y / 10),
COUNT(*) as num_orders,
SUM(o.order_total)
FROM t_customer c 
JOIN t_order o
ON c.customer_id = o.customer_id
GROUP BY c.customer_x / 10, c.customer_y / 10
ORDER BY SUM(o.order_total) DESC;

which calculates the order totals from each grid square.

解决方案

Your original query doesn't change much in the query below. The only difference is the new join and one more term added to the SELECT list:

SELECT 10 * (c.customer_x / 10) AS col1,
       10 * (c.customer_y / 10) AS col2,
       COUNT(*) AS num_orders,
       SUM(o.order_total) AS order_total_sum
FROM
(
    SELECT customer_id,
           CASE WHEN customer_x < 0 THEN customer_x - 10 ELSE customer_x END AS customer_x,
           CASE WHEN customer_y < 0 THEN customer_y - 10 ELSE customer_y END AS customer_y
    FROM t_customer
) c
INNER JOIN t_order o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_x / 10,
         c.customer_y / 10
ORDER BY SUM(o.order_total) DESC

Note that you could solve this without the use of the subquery which I have used. However, the subquery makes it much more readable, and lets us compute the adjusted customer_x and customer_y values neatly.

这篇关于将子查询添加到网格查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！