表1
docentry cardcode docdate docduedate
1 A001 2012-01-01 2012-03-25
2 A001 2012-03-27 2012-03-31
3 B001 2012-05-01 2012-05-07
4 C001 2012-05-20 2012-06-15
表2
docentry itemcode price
1 ak-530 10
1 ak-cc 8
2 ak-530 99
3 mp-a52 7
4 ak-530 25
需求结果:
itemcode docdate docduedate A001 DOCDATE DOCDUEDATE C001
ak-530 2012-03-27 2012-03-31 99 2012-05-20 2012-06-15 25
就是需要按不同厂商(cardcode)相同物料号(itemcode)与最近的DOCDATE来对比不同厂商的PRICE。
10 个解决方案
#1
为何ak-530 10的这笔没了呢.
#2
create table t1(docentry int,cardcode varchar(10),docdate date,docduedate date);
insert into t1 values
(1 ,'A001' ,'2012-01-01' ,'2012-03-25')
,(2 ,'A001' ,'2012-03-27' ,'2012-03-31')
,(3 ,'B001' ,'2012-05-01' ,'2012-05-07')
,(4 ,'C001' ,'2012-05-20' ,'2012-06-15');
create table t2(docentry int,itemcode varchar(100),price int);
insert into t2 values
(1,'ak-530', 10)
,(1, 'ak-cc', 8)
,(2, 'ak-530', 99)
,(3, 'mp-a52' ,7)
,(4, 'ak-530' ,25);
-- 假设这里只有三种类型
select
itemcode
,sum(case when cardcode='A001' then price else 0 end) as A001
,max(case when cardcode='A001' then docdate else '' end) as A001docdate
,max(case when cardcode='A001' then docduedate else '' end) as A001docduedate
,sum(case when cardcode='B001' then price else 0 end) as B001
,max(case when cardcode='B001' then docdate else '' end) as B001docdate
,max(case when cardcode='B001' then docduedate else '' end) as B001docduedate
,sum(case when cardcode='C001' then price else 0 end) as C001
,max(case when cardcode='C001' then docdate else '' end) as C001docdate
,max(case when cardcode='C001' then docduedate else '' end) as C001docduedate
from t2 a join (select * from t1 x where not exists(select 1 from t1 where cardcode=x.cardcode and docdate>x.docdate)) b
on a.docentry=b.docentry
group by itemcode
/*
itemcode A001 A001docdate A001docduedate B001 B001docdate B001docduedate C001 C001docdate C001docduedate
------------ ----------- ----------- -------------- ----------- ----------- -------------- ----------- ----------- --------------
ak-530 99 2012-03-27 2012-03-31 0 1900-01-01 1900-01-01 25 2012-05-20 2012-06-15
mp-a52 0 1900-01-01 1900-01-01 7 2012-05-01 2012-05-07 0 1900-01-01 1900-01-01
*/
#3
能不能写个动态的.还有.日期那是错误的应该是取最近的一笔数据
#4
因为docdate日期取最近的一笔.所以没有ak-530 10这笔.
#5
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([docentry] int,[cardcode] varchar(4),[docdate] datetime,[docduedate] datetime)
insert [tb1]
select 1,'A001','2012-01-01','2012-03-25' union all
select 2,'A001','2012-03-27','2012-03-31' union all
select 3,'B001','2012-05-01','2012-05-07' union all
select 4,'C001','2012-05-20','2012-06-15'
go
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([docentry] int,[itemcode] varchar(6),[price] int)
insert [tb2]
select 1,'ak-530',10 union all
select 1,'ak-cc',8 union all
select 2,'ak-530',99 union all
select 3,'mp-a52',7 union all
select 4,'ak-530',25
go
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when a.cardcode='''+cardcode+''' then a.docdate end) as [docdate'+cardcode+'],'
+'max(case when a.cardcode='''+cardcode+''' then a.docduedate end) as [docduedate'+cardcode+'],'
+'max(case when a.cardcode='''+cardcode+''' then b.price end) as ['+cardcode+']'
from
(select distinct cardcode from tb1) t
set @sql='select b.itemcode,'+@sql
+' from tb1 a join tb2 b on a.docentry=b.docentry'
+' where not exists(select 1 from tb1 where cardcode=a.cardcode and docdate>a.docdate)'
+' group by b.itemcode'
exec (@sql)
/**
itemcode docdateA001 docduedateA001 A001 docdateB001 docduedateB001 B001 docdateC001 docduedateC001 C001
-------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- -----------
ak-530 2012-03-27 00:00:00.000 2012-03-31 00:00:00.000 99 NULL NULL NULL 2012-05-20 00:00:00.000 2012-06-15 00:00:00.000 25
mp-a52 NULL NULL NULL 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 7 NULL NULL NULL
(2 行受影响)
**/
#6
例如ak-530,对应docentry 1,2,4
取最近的一笔docdate,应该是"4 C001 2012-05-20 2012-06-15"这一笔,
结果中为何又有ak-530 99
#7
厂商cardcode不同,一个是A001,一个是C001.
当有相同厂商CARDCODE与相同ITEMCODE时就取最近的数据与不同CARDCODE相同ITEMCODE做比较
#8
create table tab1
(docentry int, cardcode varchar(5),
docdate date, docduedate date)
insert into tab1
select 1, 'A001', '2012-01-01', '2012-03-25' union all
select 2, 'A001', '2012-03-27', '2012-03-31' union all
select 3, 'B001', '2012-05-01', '2012-05-07' union all
select 4, 'C001', '2012-05-20', '2012-06-15'
create table tab2
(docentry int, itemcode varchar(8), price int)
insert into tab2
select 1, 'ak-530', 10 union all
select 1, 'ak-cc', 8 union all
select 2, 'ak-530', 99 union all
select 3, 'mp-a52', 7 union all
select 4, 'ak-530', 25
with c as
(select b.docentry,b.itemcode,b.price,
a.cardcode,a.docdate,a.docduedate,
row_number() over(partition by b.itemcode,a.cardcode
order by a.docdate desc) rn
from tab2 b inner join tab1 a on b.docentry=a.docentry
)
select itemcode,
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docduedate,
[A001],
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docduedate,
[B001],
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docduedate,
[C001]
from (select itemcode,cardcode,price from c where rn=1)d
pivot(max(price) for cardcode in([A001],[B001],[C001])) e
/*
itemcode docdate docduedate A001 docdate docduedate B001 docdate docduedate C001
-------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ---------- -----------
ak-530 2012-03-27 2012-03-31 99 NULL NULL NULL 2012-05-20 2012-06-15 25
ak-cc 2012-01-01 2012-03-25 8 NULL NULL NULL NULL NULL NULL
mp-a52 NULL NULL NULL 2012-05-01 2012-05-07 7 NULL NULL NULL
(3 row(s) affected)
*/
#9
PIVOT
#10
问题已解决,因工作关系,无法上网,现结贴
#1
为何ak-530 10的这笔没了呢.
#2
create table t1(docentry int,cardcode varchar(10),docdate date,docduedate date);
insert into t1 values
(1 ,'A001' ,'2012-01-01' ,'2012-03-25')
,(2 ,'A001' ,'2012-03-27' ,'2012-03-31')
,(3 ,'B001' ,'2012-05-01' ,'2012-05-07')
,(4 ,'C001' ,'2012-05-20' ,'2012-06-15');
create table t2(docentry int,itemcode varchar(100),price int);
insert into t2 values
(1,'ak-530', 10)
,(1, 'ak-cc', 8)
,(2, 'ak-530', 99)
,(3, 'mp-a52' ,7)
,(4, 'ak-530' ,25);
-- 假设这里只有三种类型
select
itemcode
,sum(case when cardcode='A001' then price else 0 end) as A001
,max(case when cardcode='A001' then docdate else '' end) as A001docdate
,max(case when cardcode='A001' then docduedate else '' end) as A001docduedate
,sum(case when cardcode='B001' then price else 0 end) as B001
,max(case when cardcode='B001' then docdate else '' end) as B001docdate
,max(case when cardcode='B001' then docduedate else '' end) as B001docduedate
,sum(case when cardcode='C001' then price else 0 end) as C001
,max(case when cardcode='C001' then docdate else '' end) as C001docdate
,max(case when cardcode='C001' then docduedate else '' end) as C001docduedate
from t2 a join (select * from t1 x where not exists(select 1 from t1 where cardcode=x.cardcode and docdate>x.docdate)) b
on a.docentry=b.docentry
group by itemcode
/*
itemcode A001 A001docdate A001docduedate B001 B001docdate B001docduedate C001 C001docdate C001docduedate
------------ ----------- ----------- -------------- ----------- ----------- -------------- ----------- ----------- --------------
ak-530 99 2012-03-27 2012-03-31 0 1900-01-01 1900-01-01 25 2012-05-20 2012-06-15
mp-a52 0 1900-01-01 1900-01-01 7 2012-05-01 2012-05-07 0 1900-01-01 1900-01-01
*/
#3
能不能写个动态的.还有.日期那是错误的应该是取最近的一笔数据
#4
因为docdate日期取最近的一笔.所以没有ak-530 10这笔.
#5
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([docentry] int,[cardcode] varchar(4),[docdate] datetime,[docduedate] datetime)
insert [tb1]
select 1,'A001','2012-01-01','2012-03-25' union all
select 2,'A001','2012-03-27','2012-03-31' union all
select 3,'B001','2012-05-01','2012-05-07' union all
select 4,'C001','2012-05-20','2012-06-15'
go
if object_id('[tb2]') is not null drop table [tb2]
go
create table [tb2]([docentry] int,[itemcode] varchar(6),[price] int)
insert [tb2]
select 1,'ak-530',10 union all
select 1,'ak-cc',8 union all
select 2,'ak-530',99 union all
select 3,'mp-a52',7 union all
select 4,'ak-530',25
go
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
+'max(case when a.cardcode='''+cardcode+''' then a.docdate end) as [docdate'+cardcode+'],'
+'max(case when a.cardcode='''+cardcode+''' then a.docduedate end) as [docduedate'+cardcode+'],'
+'max(case when a.cardcode='''+cardcode+''' then b.price end) as ['+cardcode+']'
from
(select distinct cardcode from tb1) t
set @sql='select b.itemcode,'+@sql
+' from tb1 a join tb2 b on a.docentry=b.docentry'
+' where not exists(select 1 from tb1 where cardcode=a.cardcode and docdate>a.docdate)'
+' group by b.itemcode'
exec (@sql)
/**
itemcode docdateA001 docduedateA001 A001 docdateB001 docduedateB001 B001 docdateC001 docduedateC001 C001
-------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- -----------
ak-530 2012-03-27 00:00:00.000 2012-03-31 00:00:00.000 99 NULL NULL NULL 2012-05-20 00:00:00.000 2012-06-15 00:00:00.000 25
mp-a52 NULL NULL NULL 2012-05-01 00:00:00.000 2012-05-07 00:00:00.000 7 NULL NULL NULL
(2 行受影响)
**/
#6
例如ak-530,对应docentry 1,2,4
取最近的一笔docdate,应该是"4 C001 2012-05-20 2012-06-15"这一笔,
结果中为何又有ak-530 99
#7
厂商cardcode不同,一个是A001,一个是C001.
当有相同厂商CARDCODE与相同ITEMCODE时就取最近的数据与不同CARDCODE相同ITEMCODE做比较
#8
create table tab1
(docentry int, cardcode varchar(5),
docdate date, docduedate date)
insert into tab1
select 1, 'A001', '2012-01-01', '2012-03-25' union all
select 2, 'A001', '2012-03-27', '2012-03-31' union all
select 3, 'B001', '2012-05-01', '2012-05-07' union all
select 4, 'C001', '2012-05-20', '2012-06-15'
create table tab2
(docentry int, itemcode varchar(8), price int)
insert into tab2
select 1, 'ak-530', 10 union all
select 1, 'ak-cc', 8 union all
select 2, 'ak-530', 99 union all
select 3, 'mp-a52', 7 union all
select 4, 'ak-530', 25
with c as
(select b.docentry,b.itemcode,b.price,
a.cardcode,a.docdate,a.docduedate,
row_number() over(partition by b.itemcode,a.cardcode
order by a.docdate desc) rn
from tab2 b inner join tab1 a on b.docentry=a.docentry
)
select itemcode,
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[A001] and f.cardcode='A001') docduedate,
[A001],
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[B001] and f.cardcode='B001') docduedate,
[B001],
(select max(docdate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docdate,
(select max(docduedate) from c f
where f.rn=1 and f.itemcode=itemcode and f.price=[C001] and f.cardcode='C001') docduedate,
[C001]
from (select itemcode,cardcode,price from c where rn=1)d
pivot(max(price) for cardcode in([A001],[B001],[C001])) e
/*
itemcode docdate docduedate A001 docdate docduedate B001 docdate docduedate C001
-------- ---------- ---------- ----------- ---------- ---------- ----------- ---------- ---------- -----------
ak-530 2012-03-27 2012-03-31 99 NULL NULL NULL 2012-05-20 2012-06-15 25
ak-cc 2012-01-01 2012-03-25 8 NULL NULL NULL NULL NULL NULL
mp-a52 NULL NULL NULL 2012-05-01 2012-05-07 7 NULL NULL NULL
(3 row(s) affected)
*/
#9
PIVOT
#10
问题已解决,因工作关系,无法上网,现结贴