具有别名的GROUP BY子句？ [英] GROUP BY clause with alias?

问题描述

有谁知道为什么我无法在此查询中对 TotalSales 进行分组，如果有的话，我该如何解决这个问题：

 选择coalesce（Author_ID，'所有作者'）作为Author_ID 
，当Author_ID为null时，则''else coalesce（Book_ID，'All Books'）结束为Book_ID 
，TotalQuantity 
，coalesce（TotalSales，'No Sales'）as TotalSales 
 from（
选择author_id作为Author_ID 
，book_id作为Book_ID 
，总数（数量）为TotalQuantity 
，总额（数量*订单价格）as TotalSales 
 from a_bkinfo.book_authors 
使用（book_id）
加入a_bkorders.order_details author_sequence = 1 
组由Author_id，Book_ID，TotalSales和汇总
）tbl; 
  

当作者没有图书销售时，我想在TotalSales下包含'no sales'。这是更新的版本。我不积极，这是正确的，但我确实有输出，似乎解决了这个问题。这里是：

  select coalesce（Author_ID，'所有作者'）作为Author_ID 
，Author_ID为null的情况然后''else coalesce（Book_ID，'All Books'）以Book_ID 
结尾，NumOrders 
，coalesce（TotalSales，'No Sales'）为TotalSales 
 from（选择author_id作为Author_ID 
，book_id作为Book_ID 
，count（Distinct order_id）AS NumOrders 
，（从a_bkorders.order_details中选择sum（quantity * order_price））作为TotalSales 
 from a_bkorders.order_headers 
加入a_bkorders.order_details使用（order_id）
使用（book_id）
加入a_bkinfo.book_authors其中author_sequence = 1 
由Author_ID，Book_ID，TotalSales和rollup组成）tbl; 
  

解决方案

UPDATED2

1. 看起来您不需要在GROUP BY中包含TotalSales。看着你的查询，这没有任何意义。

为了包含尚未出售的书籍，您必须使用外连接

这就是说您的查询可能看起来像

  SELECT COALESCE（author_id，'所有作者'）author_id 
，COALESCE（book_id，IF（author_id IS NULL，'All Books'，'Subtotal'））book_id 
，COALESCE （total_quantity，'没有书'）total_quantity 
，COALESCE（total_sales，'No Sales'）total_sales 
 FROM 
（
 SELECT author_id 
，b.book_id 
，SUM（quantity）total_quantity 
，SUM（quantity * order_price）total_sales 
 FROM book_authors b LEFT JOIN order_details d 
 ON b.book_id = d.book_id 
 WHERE author_sequence = 1 
 GROUP BY Author_id，Book_ID WITH ROLLUP  - 这里不需要TotalSales 
）q; 
  

示例输出：

 
 + ------------- + ----------- + ---------------- +  - ----------- + 
 | author_id | book_id | total_quantity | total_sales | 
 + ------------- + ----------- + ---------------- +  - ----------- + 
 | 1 | 1 | 12 | 278.50 | 
 | 1 | 3 |没有书|没有销售| 
 | 1 |小计| 12 | 278.50 | 
 | 3 | 2 | 5 | 75.75 | 
 | 3 |小计| 5 | 75.75 | 
 |所有作者|所有图书| 17 | 354.25 | 
 + ------------- + ----------- + ---------------- +  - ----------- + 
 

这是 SQLFiddle 演示

Does anyone know why I am not able to group TotalSales in this query and if so how can I fix this:

select coalesce(Author_ID, 'All Authors') as Author_ID
, case when Author_ID  is null then ' ' else coalesce(Book_ID, 'All Books') end as Book_ID
, TotalQuantity  
, coalesce(TotalSales, 'No Sales') as TotalSales   
from (    
     select  author_id as Author_ID 
            , book_id as Book_ID
            ,  sum(quantity) as TotalQuantity  
            ,  sum(quantity * order_price) as TotalSales   
            from a_bkinfo.book_authors   
            join a_bkorders.order_details using (book_id)
            where author_sequence = 1           
            group by Author_id, Book_ID, TotalSales with rollup
     ) tbl;

I wanted to include 'no sales' under TotalSales when an author has no book sales. Here is the updated version. I'm not positive it is correct but I do have output which seems to solve the problem. Here it is:

select coalesce(Author_ID, 'All Authors') as Author_ID
, case when Author_ID is null then ' ' else coalesce(Book_ID, 'All Books') end as Book_ID 
, NumOrders 
, coalesce(TotalSales, 'No Sales') as TotalSales
     from ( select author_id as Author_ID 
            , book_id as Book_ID
            , count(Distinct order_id) AS NumOrders
            ,(Select sum(quantity * order_price) from a_bkorders.order_details) as TotalSales
         from a_bkorders.order_headers
         join a_bkorders.order_details using (order_id) 
         join a_bkinfo.book_authors using (book_id)  
        where author_sequence = 1        
         group by Author_ID, Book_ID, TotalSales with rollup) tbl;

解决方案

UPDATED2

1. It looks like you don't need to include TotalSales in GROUP BY. Looking at your query it just doesn't make any sense. Just ditch it from the inner select.

2. To include books that have not been sold you have to use an outer join

That being said your query might look like

SELECT COALESCE(author_id, 'All Authors') author_id
     , COALESCE(book_id, IF(author_id IS NULL, 'All Books', 'Subtotal')) book_id
     , COALESCE(total_quantity, 'No books') total_quantity
     , COALESCE(total_sales, 'No Sales') total_sales   
 FROM 
(    
 SELECT author_id 
      , b.book_id 
      , SUM(quantity) total_quantity  
      , SUM(quantity * order_price) total_sales   
   FROM book_authors b LEFT JOIN order_details d
     ON b.book_id = d.book_id
  WHERE author_sequence = 1           
  GROUP BY Author_id, Book_ID WITH ROLLUP  -- you don't need TotalSales here
) q;

Sample output:

+-------------+-----------+----------------+-------------+
| author_id   | book_id   | total_quantity | total_sales |
+-------------+-----------+----------------+-------------+
| 1           | 1         | 12             | 278.50      |
| 1           | 3         | No books       | No Sales    |
| 1           | Subtotal  | 12             | 278.50      |
| 3           | 2         | 5              | 75.75       |
| 3           | Subtotal  | 5              | 75.75       |
| All Authors | All Books | 17             | 354.25      |
+-------------+-----------+----------------+-------------+

Here is SQLFiddle demo

这篇关于具有别名的GROUP BY子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！