分組查詢一例

在分組查詢的select列表裡面列只能為groupby裡面的列，否則只能放在彙總函式裡面。那麼查詢出來的資訊就不完整，下面通過下面該查詢讓更多的列被查詢出來。

 

input為商品入庫表，total為數量，unitprice為單價，product_id為外鍵引用自input_categories表

CREATE TABLE [dbo].[input](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [product_id] [int] NOT NULL,
    [unitprice] [float] NULL,
    [total] [int] NULL,

 

input_categories位商品表（productname為商品名稱）：

CREATE TABLE [dbo].[input_categories](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [productname] [nvarchar](50) NOT NULL)

 

 

現在要查詢的是每一種商品最後一次入庫的單價，以及該種商品的總和。

先看看兩個表的資料先：

可用通過以下查詢實現：

代碼

select * from
(
select *,ran=row_number() over(partition by productname order by id desc)
from
(select c.id,b.productname,b.total,c.unitprice from
(select productname,sum(total) as total from
(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) t
group by productname) b,(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) c
where b.productname=c.productname) h
)g
where g.ran<=1

 

下面來分解一下該查詢：

1.因為兩個表有主外鍵關係，所以通過聯集查詢，把兩張表合二為一。

select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id

2.然後進行分組統計

select productname,sum(total) as total from
(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) t
group by productname

3.分組後資訊只剩下productname，total了，為了讓更多的資訊包涵進來和可以進行一次串連查詢（步驟2和步驟1的串連查詢）

select c.id,b.productname,b.total,c.unitprice from
(select productname,sum(total) as total from
(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) t
group by productname) b,(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) c
where b.productname=c.productname

4.通過row_number()來插入一個序列。

select *,ran=row_number() over(partition by productname order by id desc)
from
(select c.id,b.productname,b.total,c.unitprice from
(select productname,sum(total) as total from
(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) t
group by productname) b,(select i.id,ic.productname,i.unitprice,i.total 
from input as i,input_categories as ic where i.product_id=ic.id) c
where b.productname=c.productname) h
)g

5.最後，搞定最後一次入庫的單價，id最大的ran剛好為1，所以篩選一下ran=1的記錄就OK了。

大功告成拉，oh yeah!!