select a.moveno,out_unit,out_date ,b.md_pro_id,out_qty from [move] a,move_detail b,( select out_unit,out_date=max(out_date) from [move] group by out_unit)c where a.moveno=b.moveno and a.out_unit=c.out_unit and a.out_date=c.out_date
select a.out_unit,b.md_pro_id,b.out_qyt as out_date from move a,move_detail b where a.moveno=b.moveno and a.out_date=(select max(out_date) from move c,move_detail d where c.moveno=d.moveno and c.out_unit=a.out_unit and d.md_pro_id=b.md_pro_id)
select a.out_unit,a.md_pro_id,min(a.out_date) as out_date,sum(a.out_qty) as out_qty from(select a.moveno,a.out_unit,a.out_date,b.md_pro_id,b.out_qty from move a,move_detail b where a.moveno=b.moveno) a group by out_unit,md_pro_id
select c.md_pro_id,c.out_unit,c.out_qyt from (select a.*,b.md_pro_id,b.out_qty from move a ,move_detail b where a.moveno = b.moveno) c, (select b.md_pro_id,a.out_unit,max(a.out_date) out_date from move a ,move_detail b where a.moveno = b.moveno group by b.md_pro_id,a.out_unit)d where c.md_pro_id = d.md_pro_id and c.out_unit = d.out_unit and c.out_date = d.out_date
写的优点麻烦,没有测试select a.out_unit,a.md_pro_id,a.out_qty from (select a.moveno,a.out_unit,a.out_date,b.md_pro_id,b.out_qty from move a,move_detail b where a.moveno=b.moveno) a ,( select a.out_unit,a.md_pro_id,max(a.out_date) as out_date from (select a.moveno,a.out_unit,a.out_date,b.md_pro_id,b.out_qty from move a,move_detail b where a.moveno=b.moveno) a group by out_unit,md_pro_id ) b where a.out_unit=b.out_unit and a.md_pro_id=b.a.md_pro_id and a.out_date=b.out_date order by a.out_unit,a.md_pro_id,a.out_qty
declare @s varchar(8000) set @s='' select @s=@s+',['+out_unit+']=max(case a.out_unit when '''+out_unit+''' then b.out_qty else 0 end)' from [move] group by out_unit exec(' select b.md_pro_id'+@s+' from [move] a,move_detail b,( select out_unit,out_date=max(out_date) from [move] group by out_unit)c where a.moveno=b.moveno and a.out_unit=c.out_unit and a.out_date=c.out_date group by b.md_pro_id')
蒋老师教育我们: 只要是找“最”什么的记录,首先就想到 exists 或 not exists
select a.out_unit,b.md_pro_id,b.out_qty,a.out_date from [move] a ,move_detail b where a.moveno=b.moveno and a.moveno = ( select top 1 c.moveno from [move] c ,move_detail d where c.moveno=d.moveno and a.out_unit=c.out_unit and b.md_pro_id=d.md_pro_id and b.out_qty>0 order by c.out_date desc ) order by a.out_unit,b.md_pro_id上面的语句写的得对了。但是格式和我要想的不一样。 move表: moveno out_unit out_date 00001 00023 2004-07-29 00002 00020 2004-08-08 00003 00020 2004-08-15 00004 00023 2004-08-26move_detail表: moveno md_pro_id out_qty 00001 0002340 6 00001 0003620 9 00001 0006950 10 00001 0003600 20 00002 0002301 12 00002 0002340 9 00002 0003210 23 00002 0003600 90 00003 0002301 52 00003 0009560 63 00003 0003201 25 00004 0002340 89 00004 0003620 51我想得到格式的是: md_pro_id 00023 00020 0002340 89 9 0003620 9 0 0002301 0 52 0003600 20 90 .... .... ....既得到每个收货单位每个品种的最近一次发货量,
select w.md_pro_id, (case when w.out_unit='00001' then w.out_qty else 0 end )out_0001, (case when w.out_unit='00002' then w.out_qty else 0 end )out_0002, (case when w.out_unit='00003' then w.out_qty else 0 end )out_0003, (case when w.out_unit='00004' then w.out_qty else 0 end )out_0004, (case when w.out_unit='00005' then w.out_qty else 0 end )out_0005, (case when w.out_unit='00006' then w.out_qty else 0 end )out_0006 from (select a.out_unit,b.md_pro_id,b.out_qty from [move] a ,move_detail b where a.moveno=b.moveno and a.moveno = ( select top 1 c.moveno from [move] c ,move_detail d where c.moveno=d.moveno and a.out_unit=c.out_unit and b.md_pro_id=d.md_pro_id and b.out_qty>0 order by c.out_date desc ) )w order by w.md_pro_id我写出了上面的语句,但还是不符合格式要求,得出的md_pro_id 有重复的。
select y.md_pro_id '商品编码', sum(y.out_00001)'00001最近出货量', sum(y.out_00002)'00002最近出货量', sum(y.out_00003)'00003最近出货量', sum(y.out_00004)'00004最近出货量', sum(y.out_00005)'00005最近出货量', sum(y.out_00006)'00006最近出货量' from (select w.md_pro_id, (case when w.out_unit='00001' then w.out_qty else 0 end )out_00001, (case when w.out_unit='00002' then w.out_qty else 0 end )out_00002, (case when w.out_unit='00003' then w.out_qty else 0 end )out_00003, (case when w.out_unit='00004' then w.out_qty else 0 end )out_00004, (case when w.out_unit='00005' then w.out_qty else 0 end )out_00005, (case when w.out_unit='00006' then w.out_qty else 0 end )out_00006 from ( select a.out_unit,b.md_pro_id,b.out_qty from [move] a ,move_detail b where a.moveno=b.moveno and a.moveno = (select top 1 c.moveno from [move] c ,move_detail d where c.moveno=d.moveno and a.out_unit=c.out_unit and b.md_pro_id=d.md_pro_id and b.out_qty>0 order by c.out_date desc) )w )y group by y.md_pro_id order by y.md_pro_id上面的语句已经符合格式要求了,但好像效率并不是很高,不知道各位有什么好的想法没有?
--很快啊-- 有move、move_detail两个表,其中move表结构为moveno,out_unit,out_date (出货单号、收货单位编号、出货日期), -- move_detail表结构为:moveno,md_pro_id,out_qty(出货单号、出货商品编号、出货数量),两个表的moveno均为主键,作为两个表之间的关联字段。 -- 现求:按收货单位out_unit来分,取每个商品md_pro_id 最近的一次的出货数量out_qyt, -- 既查询每个收货单位的每种商品最近的出货数量。create table #move(moveno varchar(100),out_unit varchar(1000),out_date datetime) create table #move_detail(moveno varchar(100),md_pro_id varchar(1000),out_qty bigint) insert into #move(moveno ,out_unit ,out_date) select '00001','00023','2004-07-29' union select '00002','00020','2004-08-08' union select '00003','00020','2004-08-15' union select '00004','00023','2004-08-26' insert into #move_detail(moveno ,md_pro_id ,out_qty) select '00001','0002340',6 union select '00001','0003620',9 union select '00001','0006950',10 union select '00001','0003600',20 union select '00002','0002301',12 union select '00002','0002340',9 union select '00002','0003210',23 union select '00002','0003600',90 union select '00003','0002301',52 union select '00003','0009560',63 union select '00003','0003201',25 union select '00004','0002340',89 union select '00004','0003620',51 select y.md_pro_id '商品编码', sum(y.out_00001)'00001最近出货量', sum(y.out_00002)'00002最近出货量' from (select w.md_pro_id, (case when w.out_unit='00020' then w.out_qty else 0 end )out_00001, (case when w.out_unit='00023' then w.out_qty else 0 end )out_00002 from ( select a.out_unit,b.md_pro_id,b.out_qty from #move a ,#move_detail b where a.moveno=b.moveno and a.moveno = (select top 1 c.moveno from #move c ,#move_detail d where c.moveno=d.moveno and a.out_unit=c.out_unit and b.md_pro_id=d.md_pro_id and b.out_qty>0 order by c.out_date desc) )w )y group by y.md_pro_id order by y.md_pro_id drop table #move drop table #move_detail
from move a,move_detail b
where a.moveno=b.moveno
and a.out_date=(select max(out_date)
from move c,move_detail d
where c.moveno=d.moveno
and c.out_unit=a.out_unit
and d.md_pro_id=b.md_pro_id)
from(select a.moveno,a.out_unit,a.out_date,b.md_pro_id,b.out_qty from move a,move_detail b where a.moveno=b.moveno) a
group by out_unit,md_pro_id
(select a.*,b.md_pro_id,b.out_qty from move a ,move_detail b
where a.moveno = b.moveno) c,
(select b.md_pro_id,a.out_unit,max(a.out_date) out_date
from move a ,move_detail b
where a.moveno = b.moveno
group by b.md_pro_id,a.out_unit)d
where c.md_pro_id = d.md_pro_id
and c.out_unit = d.out_unit
and c.out_date = d.out_date
md_pro_id out_unit001最近出货量 out_unit002最近出货量 outunit00n最近出货量还请高手们再想一下。
,(
select a.out_unit,a.md_pro_id,max(a.out_date) as out_date
from (select a.moveno,a.out_unit,a.out_date,b.md_pro_id,b.out_qty from move a,move_detail b where a.moveno=b.moveno) a
group by out_unit,md_pro_id
) b
where a.out_unit=b.out_unit and a.md_pro_id=b.a.md_pro_id and a.out_date=b.out_date
order by a.out_unit,a.md_pro_id,a.out_qty
set @s=''
select @s=@s+',['+out_unit+']=max(case a.out_unit when '''+out_unit+''' then b.out_qty else 0 end)'
from [move]
group by out_unit
exec('
select b.md_pro_id'+@s+'
from [move] a,move_detail b,(
select out_unit,out_date=max(out_date)
from [move]
group by out_unit)c
where a.moveno=b.moveno
and a.out_unit=c.out_unit
and a.out_date=c.out_date
group by b.md_pro_id')
只要是找“最”什么的记录,首先就想到 exists 或 not exists
from [move] a ,move_detail b
where a.moveno=b.moveno
and a.moveno = (
select top 1 c.moveno
from [move] c ,move_detail d
where c.moveno=d.moveno
and a.out_unit=c.out_unit
and b.md_pro_id=d.md_pro_id
and b.out_qty>0
order by c.out_date desc
)
order by a.out_unit,b.md_pro_id上面的语句写的得对了。但是格式和我要想的不一样。
move表:
moveno out_unit out_date
00001 00023 2004-07-29
00002 00020 2004-08-08
00003 00020 2004-08-15
00004 00023 2004-08-26move_detail表:
moveno md_pro_id out_qty
00001 0002340 6
00001 0003620 9
00001 0006950 10
00001 0003600 20
00002 0002301 12
00002 0002340 9
00002 0003210 23
00002 0003600 90
00003 0002301 52
00003 0009560 63
00003 0003201 25
00004 0002340 89
00004 0003620 51我想得到格式的是:
md_pro_id 00023 00020
0002340 89 9
0003620 9 0
0002301 0 52
0003600 20 90
.... .... ....既得到每个收货单位每个品种的最近一次发货量,
(case when w.out_unit='00001' then w.out_qty else 0 end )out_0001,
(case when w.out_unit='00002' then w.out_qty else 0 end )out_0002,
(case when w.out_unit='00003' then w.out_qty else 0 end )out_0003,
(case when w.out_unit='00004' then w.out_qty else 0 end )out_0004,
(case when w.out_unit='00005' then w.out_qty else 0 end )out_0005,
(case when w.out_unit='00006' then w.out_qty else 0 end )out_0006
from
(select a.out_unit,b.md_pro_id,b.out_qty
from [move] a ,move_detail b
where a.moveno=b.moveno
and a.moveno = (
select top 1 c.moveno
from [move] c ,move_detail d
where c.moveno=d.moveno
and a.out_unit=c.out_unit
and b.md_pro_id=d.md_pro_id
and b.out_qty>0
order by c.out_date desc
)
)w
order by w.md_pro_id我写出了上面的语句,但还是不符合格式要求,得出的md_pro_id 有重复的。
/*
有move、move_detail两个表,其中move表结构为moveno,out_unit,out_date (出货单号、收货单位编号、出货日期),
move_detail表结构为:moveno,md_pro_id,out_qty(出货单号、出货商品编号、出货数量),两个表的moveno作为关联字段。
现求:按收货单位out_unit来分,取每个商品md_pro_id 最近的一次的出货数量out_qyt,
既查询每个收货单位的每种商品最近的出货数量。
moveno out_unit out_date
00001 00023 2004-07-29
00002 00020 2004-08-08
00003 00020 2004-08-15
00004 00023 2004-08-26
move_detail表:
moveno md_pro_id out_qty
00001 0002340 6
00001 0003620 9
00001 0006950 10
00001 0003600 20
00002 0002301 12
00002 0002340 9
00002 0003210 23
00002 0003600 90
00003 0002301 52
00003 0009560 63
00003 0003201 25
00004 0002340 89
00004 0003620 51想要地结果:
md_pro_id 00020 00023
0002301 77 14
0002340 67 0
0003201 8 0
0003210 52 42
0003600 33 32
0003620 37 6
0006950 0 16
0009560 5 0*/
create table move
(
moveno varchar(10) primary key,
out_unit varchar(10) null,
out_date datetime null
)create table move_detail
(
moveno varchar(10) ,
md_pro_id varchar(10) null,
out_qty int null
)
insert move values('00001','00023','2004-07-29')
insert move values('00002','00020','2004-08-08')
insert move values('00003','00020','2004-08-15')
insert move values('00004','00023','2004-08-26')
insert move_detail values('00001','0003620',6)
insert move_detail values('00001','0006950',16)
insert move_detail values('00001','0003600',23)
insert move_detail values('00001','0002301',8)
insert move_detail values('00002','0002340',11)
insert move_detail values('00002','0003210',52)
insert move_detail values('00002','0003600',33)
insert move_detail values('00002','0002301',77)
insert move_detail values('00003','0009560',5)
insert move_detail values('00003','0003201',8)
insert move_detail values('00003','0002340',56)
insert move_detail values('00003','0003620',37)
insert move_detail values('00004','0003210',42)
insert move_detail values('00004','0002301',6)
insert move_detail values('00001','0003600',9)
select distinct out_unit into #temp from [move]
declare @s varchar(8000)
set @s=''
select @s = @s + ',[' + out_unit + '] = sum(case a.out_unit when ''' + out_unit + ''' then b.out_qty else 0 end)'
from #temp
print(@s)
exec('select b.md_pro_id' + @s +
' from [move] a inner join move_detail b on (a.moveno = b.moveno) group by b.md_pro_id ')
/*没有加时间限制,自己加上去*/
那如果加上时间限制呢?应该怎么写?
sum(y.out_00001)'00001最近出货量',
sum(y.out_00002)'00002最近出货量',
sum(y.out_00003)'00003最近出货量',
sum(y.out_00004)'00004最近出货量',
sum(y.out_00005)'00005最近出货量',
sum(y.out_00006)'00006最近出货量'
from
(select w.md_pro_id,
(case when w.out_unit='00001' then w.out_qty else 0 end )out_00001,
(case when w.out_unit='00002' then w.out_qty else 0 end )out_00002,
(case when w.out_unit='00003' then w.out_qty else 0 end )out_00003,
(case when w.out_unit='00004' then w.out_qty else 0 end )out_00004,
(case when w.out_unit='00005' then w.out_qty else 0 end )out_00005,
(case when w.out_unit='00006' then w.out_qty else 0 end )out_00006
from
( select a.out_unit,b.md_pro_id,b.out_qty
from [move] a ,move_detail b
where a.moveno=b.moveno
and a.moveno =
(select top 1 c.moveno
from [move] c ,move_detail d
where c.moveno=d.moveno
and a.out_unit=c.out_unit
and b.md_pro_id=d.md_pro_id
and b.out_qty>0
order by c.out_date desc)
)w
)y
group by y.md_pro_id
order by y.md_pro_id上面的语句已经符合格式要求了,但好像效率并不是很高,不知道各位有什么好的想法没有?
-- move_detail表结构为:moveno,md_pro_id,out_qty(出货单号、出货商品编号、出货数量),两个表的moveno均为主键,作为两个表之间的关联字段。
-- 现求:按收货单位out_unit来分,取每个商品md_pro_id 最近的一次的出货数量out_qyt,
-- 既查询每个收货单位的每种商品最近的出货数量。create table #move(moveno varchar(100),out_unit varchar(1000),out_date datetime)
create table #move_detail(moveno varchar(100),md_pro_id varchar(1000),out_qty bigint)
insert into #move(moveno ,out_unit ,out_date)
select '00001','00023','2004-07-29'
union select '00002','00020','2004-08-08'
union select '00003','00020','2004-08-15'
union select '00004','00023','2004-08-26'
insert into #move_detail(moveno ,md_pro_id ,out_qty)
select '00001','0002340',6
union select '00001','0003620',9
union select '00001','0006950',10
union select '00001','0003600',20
union select '00002','0002301',12
union select '00002','0002340',9
union select '00002','0003210',23
union select '00002','0003600',90
union select '00003','0002301',52
union select '00003','0009560',63
union select '00003','0003201',25
union select '00004','0002340',89
union select '00004','0003620',51
select y.md_pro_id '商品编码',
sum(y.out_00001)'00001最近出货量',
sum(y.out_00002)'00002最近出货量'
from
(select w.md_pro_id,
(case when w.out_unit='00020' then w.out_qty else 0 end )out_00001,
(case when w.out_unit='00023' then w.out_qty else 0 end )out_00002
from
( select a.out_unit,b.md_pro_id,b.out_qty
from #move a ,#move_detail b
where a.moveno=b.moveno
and a.moveno =
(select top 1 c.moveno
from #move c ,#move_detail d
where c.moveno=d.moveno
and a.out_unit=c.out_unit
and b.md_pro_id=d.md_pro_id
and b.out_qty>0
order by c.out_date desc)
)w
)y
group by y.md_pro_id
order by y.md_pro_id
drop table #move
drop table #move_detail
不过还是感谢你的帮忙。
结帖了。