如何排除列中出现的结果？

  • Z时代
  • 2024-01-10
  • 分类：问答

此查询返回季度销售额，但我想隐藏order_date列以显示在结果中。 order_date仅用于确定该商品是否在季度内购买。如何排除列中出现的结果？

SELECT item.item_id, item.item_name, item.item_type, item.item_price, SUM(orderdetail.order_quantity) AS 'Quantity Ordered', 
SUM(item.item_price*orderdetail.order_quantity) AS 'Total Sales Per Item', orders.order_date FROM item 
JOIN orderdetail ON item.item_id = orderdetail.item_id 
JOIN orders ON orderdetail.order_id = orders.order_id 
GROUP BY item.item_id, item.item_name, item.item_price, item.item_type 
HAVING order_date BETWEEN '2017-01-01' AND '2017-03-31' 
ORDER BY item.item_id;

回答：

从SELECT拆下色谱柱，把条件一WHERE条款，而不是HAVING条款：

SELECT i.item_id, i.item_name, i.item_type, i.item_price, 
     SUM(od.order_quantity) AS `Quantity Ordered`, 
     SUM(i.item_price * od.order_quantity) AS `Total Sales Per Item` 
FROM item i JOIN 
    orderdetail od 
    ON i.item_id = od.item_id JOIN 
    orders o 
    ON od.order_id = o.order_id 
WHERE o.order_date BETWEEN '2017-01-01' AND '2017-03-31' 
GROUP BY i.item_id, i.item_name, i.item_price, i.item_type 
ORDER BY i.item_id;

备注：

  • 查询使用表别名，使查询更易于编写和阅读。

  • 条件应该在WHERE条款中。这是更高效的，因为数据在聚合之前被过滤而不是之后。我不确定o.order_date应该在group by。它应该只在那里，如果你想每个日期单独的行。

回答：

不包括其下的选择是为:)

SELECT item.item_id, item.item_name, item.item_type, item.item_price, SUM(orderdetail.order_quantity) AS 'Quantity Ordered', 
    SUM(item.item_price*orderdetail.order_quantity) AS 'Total Sales Per Item' FROM item 
    JOIN orderdetail ON item.item_id = orderdetail.item_id 
    JOIN orders ON orderdetail.order_id = orders.order_id 
    GROUP BY item.item_id, item.item_name, item.item_price, item.item_type 
    HAVING order_date BETWEEN '2017-01-01' AND '2017-03-31' 
    ORDER BY item.item_id

回答：

刚从select列表中删除它那样简单。这不会妨碍你在where或having条款使用它：

SELECT item.item_id, item.item_name, item.item_type, item.item_price, SUM(orderdetail.order_quantity) AS 'Quantity Ordered', 
SUM(item.item_price*orderdetail.order_quantity) AS 'Total Sales Per Item' 
-- order_date removed here 
FROM item 
JOIN orderdetail ON item.item_id = orderdetail.item_id 
JOIN orders ON orderdetail.order_id = orders.order_id 
GROUP BY item.item_id, item.item_name, item.item_price, item.item_type 
HAVING order_date BETWEEN '2017-01-01' AND '2017-03-31' 
ORDER BY item.item_id;

以上是 如何排除列中出现的结果？ 的全部内容， 来源链接： utcz.com/qa/258319.html

回到顶部