分组查询一例

在分组查询的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!!