有如下数据:假设全部是1月份数据ITEM_CODE(编号) PRICE(单价) QUANTITY(数量) INDATE(日期) UNIT(单位)
001 1.5 100 2011-01-01 KG
001 2.5 800 2011-01-02 KG
001 3.5 300 2011-01-05 KG
002 2.0 500 2011-01-11 KG
002 4.0 600 2011-01-08 KG
002 5.0 100 2011-01-02 KG
003 4.0 100 2011-01-08 KG
003 5.0 100 2011-01-13 KG
003 6.0 100 2011-01-14 KG
......我想要的结果:单价取当月最后日期的单价,数量要加总,日期显示分年份和月份显示。ITEM_CODE(编号) PRICE(单价) QUANTITY(数量) YEAR(年) MONTH(月) UNIT(单位)
001 3.5 1200 2011 1 KG
002 2.0 1200 2011 1 KG
003 6.0 300 2011 1 KG
数据库环境:sql 2000,语句尽量简单的,不要存储过程。
001 1.5 100 2011-01-01 KG
001 2.5 800 2011-01-02 KG
001 3.5 300 2011-01-05 KG
002 2.0 500 2011-01-11 KG
002 4.0 600 2011-01-08 KG
002 5.0 100 2011-01-02 KG
003 4.0 100 2011-01-08 KG
003 5.0 100 2011-01-13 KG
003 6.0 100 2011-01-14 KG
......我想要的结果:单价取当月最后日期的单价,数量要加总,日期显示分年份和月份显示。ITEM_CODE(编号) PRICE(单价) QUANTITY(数量) YEAR(年) MONTH(月) UNIT(单位)
001 3.5 1200 2011 1 KG
002 2.0 1200 2011 1 KG
003 6.0 300 2011 1 KG
数据库环境:sql 2000,语句尽量简单的,不要存储过程。
declare @t table
(ITEM_CODE varchar(3),PRICE numeric(2,1),QUANTITY int,INDATE datetime,UNIT varchar(2))
insert into @t
select '001',1.5,100,'2011-01-01','KG' union all
select '001',2.5,800,'2011-01-02','KG' union all
select '001',3.5,300,'2011-01-05','KG' union all
select '002',2.0,500,'2011-01-11','KG' union all
select '002',4.0,600,'2011-01-08','KG' union all
select '002',5.0,100,'2011-01-02','KG' union all
select '003',4.0,100,'2011-01-08','KG' union all
select '003',5.0,100,'2011-01-13','KG' union all
select '003',6.0,100,'2011-01-14','KG'select
ITEM_CODE,
PRICE=(select sum(PRICE) from @t where ITEM_CODE=t.ITEM_CODE) ,
QUANTITY=(select sum(QUANTITY) from @t where ITEM_CODE=t.ITEM_CODE) ,
year=year(INDATE),month=month(INDATE),UNIT
from @t t
where INDATE=(select max(INDATE) from @t where ITEM_CODE=t.ITEM_CODE)/*
ITEM_CODE PRICE QUANTITY year month UNIT
--------- --------------------------------------- ----------- ----------- ----------- ----
003 15.0 300 2011 1 KG
002 11.0 1200 2011 1 KG
001 7.5 1200 2011 1 KG
*/
select
ITEM_CODE,
PRICE=(select sum(PRICE) from 你的表名 where ITEM_CODE=t.ITEM_CODE) ,
QUANTITY=(select sum(QUANTITY) from 你的表名 where ITEM_CODE=t.ITEM_CODE) ,
year=year(INDATE),month=month(INDATE),UNIT
from 你的表名 t
where INDATE=(select max(INDATE) from 你的表名 where ITEM_CODE=t.ITEM_CODE)
order by 1
select
ITEM_CODE,PRICE,QUANTITY=(select sum(QUANTITY) from 你的表名 where ITEM_CODE=t.ITEM_CODE) ,
year=year(INDATE),month=month(INDATE),UNIT
from 你的表名 t
where INDATE=(select max(INDATE) from 你的表名 where ITEM_CODE=t.ITEM_CODE)
order by 1/*
ITEM_CODE PRICE QUANTITY year month UNIT
--------- --------------------------------------- ----------- ----------- ----------- ----
001 3.5 1200 2011 1 KG
002 2.0 1200 2011 1 KG
003 6.0 300 2011 1 KG
*/
from
(
select ITEM_CODE,
sum(QUANTITY) as QUANTITY,max(UNIT) as UNIT,max(INDATE) as INDATE from #tab group by ITEM_CODE
) tab
create table #tab
(
ITEM_CODE nvarchar(3),
PRICE nvarchar(5),
QUANTITY int,
INDATE nvarchar(11),
UNIT nvarchar(5)
)insert into #tab(ITEM_CODE,PRICE,QUANTITY,INDATE,UNIT)
select '001','1.5','100','2011-01-01','KG'
union all
select '001','2.5','800','2011-01-02','KG'
union all
select '001','3.5','300','2011-01-05','KG'
union all
select '002','2.0','500','2011-01-11','KG'
union all
select '002','4.0','600','2011-01-08','KG'
union all
select '002','5.0','100','2011-01-02','KG'
union all
select '003','4.0','100','2011-01-08','KG'
union all
select '003','5.0','100','2011-01-13','KG'
union all
select '003','6.0','100','2011-01-14','KG'
select ITEM_CODE,QUANTITY,Year(INDATE) as [Year],Month(INDATE) as [Month],UNIT,PRICE=(select top 1 PRICE from #tab where ITEM_CODE=tab.ITEM_CODE order by INDATE desc)
from
(
select ITEM_CODE,
sum(QUANTITY) as QUANTITY,max(UNIT) as UNIT,max(INDATE) as INDATE from #tab group by ITEM_CODE
) tab
select a.ITEM_CODE,a.PRICE,(select sum(c.QUANTITY) from #t c where c.ITEM_CODE=a.ITEM_CODE)QUANTITY ,month(a.INDATE) m,a.UNIT from #t a where price
=(select max(b.PRICE) from #t b where b.ITEM_CODE=a.ITEM_CODE )
select a.ITEM_CODE,a.PRICE,(select sum(c.QUANTITY) from #t c where c.ITEM_CODE=a.ITEM_CODE)QUANTITY ,month(a.INDATE) m,a.UNIT from #t a where a.INDATE
=(select max(b.INDATE) from #t b where b.ITEM_CODE=a.ITEM_CODE ) order by a.ITEM_CODE asc
create table wang
(ITEM_CODE varchar(6), PRICE decimal(4,1), QUANTITY int, INDATE date, UNIT varchar(5))insert into wang
select '001', 1.5, 100, '2011-01-01', 'KG' union all
select '001', 2.5, 800, '2011-01-02', 'KG' union all
select '001', 3.5, 300, '2011-01-05', 'KG' union all
select '002', 2.0, 500, '2011-01-11', 'KG' union all
select '002', 4.0, 600, '2011-01-08', 'KG' union all
select '002', 5.0, 100, '2011-01-02', 'KG' union all
select '003', 4.0, 100, '2011-01-08', 'KG' union all
select '003', 5.0, 100, '2011-01-13', 'KG' union all
select '003', 6.0, 100, '2011-01-14', 'KG'
select t.ITEM_CODE,
(select t2.PRICE from wang t2
where t2.ITEM_CODE=t.ITEM_CODE
and t2.INDATE=(select max(INDATE) from wang t3 where t3.ITEM_CODE=t.ITEM_CODE)) PRICE,
sum(QUANTITY) QUANTITY,
year(min(INDATE)) year,
month(min(INDATE)) month,
max(UNIT) UNIT
from wang t
group by t.ITEM_CODEITEM_CODE PRICE QUANTITY year month UNIT
--------- ------------ ----------- ----------- ----------- -----
001 3.5 1200 2011 1 KG
002 2.0 1200 2011 1 KG
003 6.0 300 2011 1 KG(3 row(s) affected)
datepart(mm,s.INDATE)as 'MONTH(月)' ,s.unit
from A n join (
select * from A a where indate=(select max(indate) from A b where a.ITEM_CODE=b.ITEM_CODE))s on n.ITEM_CODE=s.ITEM_CODE
group by n.ITEM_CODE,s.PRICE,s.INDATE,s.unit
ITEM_CODE PRICE QUANTITY(数量) YEAR(年) MONTH(月) unit
----------- --------------------------------------- ------------ ----------- ----------- --------------------------------------------------
1 3.50 1200 2011 1 KG
2 2.00 1200 2011 1 KG
3 6.00 300 2011 1 KG(3 行受影响)
--001 1.5 100 2011-01-01 KG
--001 2.5 800 2011-01-02 KG
--001 3.5 300 2011-01-05 KG
--002 2.0 500 2011-01-11 KG
--002 4.0 600 2011-01-08 KG
--002 5.0 100 2011-01-02 KG
--003 4.0 100 2011-01-08 KG
--003 5.0 100 2011-01-13 KG
--003 6.0 100 2011-01-14 KG
--我想要的结果:单价取当月最后日期的单价,数量要加总,日期显示分年份和月份显示。
--SQL code
--ITEM_CODE(编号) PRICE(单价) QUANTITY(数量) YEAR(年) MONTH(月) UNIT(单位)
--001 3.5 1200 2011 1 KG
--002 2.0 1200 2011 1 KG
--003 6.0 300 2011 1 KG
if OBJECT_ID('A')is not null
drop table A
go
create table A (ITEM_CODE INT, PRICE decimal(18, 2) ,QUANTITY INT, INDATE varchar(50),unit varchar(50))
INSERT INTO A
select '001',1.5,100,'2011-01-01','KG' union all
select '001',2.5,800,'2011-01-02','KG' union all
select '001',3.5,300,'2011-01-05','KG' union all
select '002',2.0,500,'2011-01-11','KG' union all
select '002',4.0,600,'2011-01-08','KG' union all
select '002',5.0,100,'2011-01-02','KG' union all
select '003',4.0,100,'2011-01-08','KG' union all
select '003',5.0,100,'2011-01-13','KG' union all
select '003',6.0,100,'2011-01-14','KG'select n.ITEM_CODE,s.PRICE,sum(n.QUANTITY) as 'QUANTITY(数量)' ,datepart(yy, s.INDATE)as 'YEAR(年)' ,
datepart(mm,s.INDATE)as 'MONTH(月)' ,s.unit
from A n join (
select * from A a where indate=(select max(indate) from A b where a.ITEM_CODE=b.ITEM_CODE))s on n.ITEM_CODE=s.ITEM_CODE
group by n.ITEM_CODE,s.PRICE,s.INDATE,s.unit
ITEM_CODE PRICE QUANTITY(数量) YEAR(年) MONTH(月) unit
----------- --------------------------------------- ------------ ----------- ----------- --------------------------------------------------
1 3.50 1200 2011 1 KG
2 2.00 1200 2011 1 KG
3 6.00 300 2011 1 KG(3 行受影响)