日期:2014-05-17  浏览次数:20593 次

SQL语句:产品型号相同,数量累加,只显示第一条
数据表有五个字段:
SQL code

 type ,
 encapsulation,
 sale_date,
 brand,
 quantity
 --我想要的就是如果type相同的,把数量加起来,显示其中一条
 --这样子报错:
 select distinct(type)as type ,min(encapsulation),min(sale_date),min(brand),quantity=sum(quantity) from long_goods group by type 
 --这样子数据显示又不对:
 select distinct(type)as type ,min(encapsulation),min(sale_date),min(brand),quantity=sum(quantity) from long_goods group by type
 请各位指点一下 



------解决方案--------------------
SQL code
select * from 
(select type,sum(quantity) quantity from long_goods group by type)aa
inner join
(select * from long_goods a where not exists(select 1 from long_goods where type=a.type and sale_date<a.sale_date))bb
on aa.type=bb.type

------解决方案--------------------
SQL code

SELECT IDENTITY(int,1,1) as Row,
       type ,
       encapsulation,
       sale_date,
       brand,
       quantity
INTO #tmp
FROM  long_goods

SELECT encapsulation,
       sale_date,
       brand,
       TotalQ=(
       SELECT SUM(quantity) FROM #tmp WHERE type=a.type
       )
FROM #t a
WHERE NOT EXISTS
     (
      SELECT * FROM #tmp
      WHERE type=a.type AND Row<a.Row
     )
DROP TABLE #tmp

------解决方案--------------------
SQL code
;with cte as
(select *,ROW_NUMBER() over(order by [type]) as px
 from long_goods
 )
 
select [type],encapsulation,brand,sale_date,
quantity=(select sum(quantity) from cte where [type]=a.[type])
from cte a  
WHERE NOT EXISTS (SELECT nt