分组查询一例

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