22,209
社区成员
发帖
与我相关
我的任务
分享
-- 头表 HO
--明细表 DO
-- HO (单号 , 供应商 ,单日期)
--1 001 2011/01/01
--2 001 2011/01/02
--3 001 2011/01/03
--4 002 2012/01/01
--5 002 2012/01/02
--6 002 2012/01/03
create table HO(单号 int, 供应商 varchar(50), 单日期 varchar(50))
insert into HO values(1, '001' ,'2011/01/01')
insert into HO values(2, '001' ,'2011/01/02')
insert into HO values(3, '001' ,'2011/01/03')
insert into HO values(4, '002' ,'2012/01/01')
insert into HO values(5, '002' ,'2012/01/02')
insert into HO values(6 ,'002' ,'2012/01/03')
create table DO(单号 int, 商品 varchar(50), 价格 int)
insert into DO values(1 ,'a', 10)
insert into DO values(1 ,'b', 20)
insert into DO values(1 ,'c', 30)
insert into DO values(2 ,'a', 40)
insert into DO values(2 ,'c', 50)
insert into DO values(3 ,'a', 60)
insert into DO values(3 ,'b', 70)
insert into DO values(3 ,'c', 80 )
insert into DO values(4 ,'a', 90)
insert into DO values(4 ,'b', 100)
insert into DO values(4 ,'c', 110)
insert into DO values(5 ,'c', 130)
insert into DO values(6 ,'a', 140)
-- DO (单号 , 商品 , 价格 )
--1 a 10
--1 b 20
--1 c 30
--2 a 40
--2 c 50
--3 a 60
--3 b 70
--3 c 80
--4 a 90
--4 b 100
--4 c 110
--5 c 130
--6 a 140
--取出的结构为
--3 001 2011/01/03 a 60
--6 002 2012/01/03 a 140
--3 001 2011/01/03 b 70
--4 002 2012/01/01 b 100
--3 001 2011/01/03 c 80
--5 002 2012/01/02 c 120
;with cte as (
select H.*,D.商品,D.价格 from HO H JOIN DO D ON H.单号=D.单号 )
select
(select top 1 单号 from cte t2 where t2.商品=t1.商品 and t2.供应商=t1.供应商 and t2.单日期=max(t1.单日期)) '单号',t1.供应商,max(单日期) 单日期,t1.商品,
(select top 1 价格 from cte t2 where t2.商品=t1.商品 and t2.供应商=t1.供应商 and t2.单日期=max(t1.单日期)) '价格'
from cte t1
group by t1.商品,t1.供应商
order by t1.商品
单号 供应商 单日期 商品 价格
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
3 001 2011/01/03 a 60
6 002 2012/01/03 a 140
3 001 2011/01/03 b 70
4 002 2012/01/01 b 100
3 001 2011/01/03 c 80
5 002 2012/01/02 c 130
(6 行受影响)
create table HO
(单号 int, 供应商 varchar(6), 单日期 date)
insert into HO
select 1, '001', '2011/01/01' union all
select 2, '001', '2011/01/02' union all
select 3, '001', '2011/01/03' union all
select 4, '002', '2012/01/01' union all
select 5, '002', '2012/01/02' union all
select 6, '002', '2012/01/03'
create table DO
(单号 int, 商品 varchar(4), 价格 int)
insert into DO
select 1, 'a', 10 union all
select 1, 'b', 20 union all
select 1, 'c', 30 union all
select 2, 'a', 40 union all
select 2, 'c', 50 union all
select 3, 'a', 60 union all
select 3, 'b', 70 union all
select 3, 'c', 80 union all
select 4, 'a', 90 union all
select 4, 'b', 100 union all
select 4, 'c', 110 union all
select 5, 'c', 130 union all
select 6, 'a', 140
;with t as
(select HO.单号,HO.供应商,HO.单日期,DO.商品,DO.价格
from HO
inner join DO on HO.单号=DO.单号
)
select
(select top 1 单号 from t t2
where t2.商品=t1.商品 and t2.供应商=t1.供应商 and t2.单日期=max(t1.单日期)) '单号',
t1.供应商,max(单日期) 单日期,t1.商品,
(select top 1 价格 from t t2
where t2.商品=t1.商品 and t2.供应商=t1.供应商 and t2.单日期=max(t1.单日期)) '价格'
from t t1
group by t1.商品,t1.供应商
order by t1.商品
单号 供应商 单日期 商品 价格
----------- ------ ---------- ---- -----------
3 001 2011-01-03 a 60
6 002 2012-01-03 a 140
3 001 2011-01-03 b 70
4 002 2012-01-01 b 100
3 001 2011-01-03 c 80
5 002 2012-01-02 c 130