日期:2014-05-18 浏览次数:20768 次
create table A(ih varchar(10),hc varchar(10),date varchar(10))
insert into A values('1', 'Q1', '2007-01-01')
insert into A values('1', 'Q1', '2007-02-01')
insert into A values('2', 'Q2', '2007-01-05')
insert into A values('3', 'Q3', '2007-03-01')
create table B(ih varchar(10),idx int,jg decimal(18,1))
insert into B values('1', 1, 10.2)
insert into B values('1', 2, 10.3)
insert into B values('1', 3, 9.8 )
insert into B values('2', 1, 11 )
insert into B values('2', 2, 10.2)
insert into B values('3', 2, 12 )
go
select
ih = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m1
where m4.ih=m1.ih) then ih else '' end ,
hc = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m2
where m4.ih=m2.ih) then hc else '' end ,
date = case when idx=(select min(idx) from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m3
where m4.ih=m3.ih) then date else '' end ,
jg
from
(
select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih
) m4
drop table A,B
/*
ih hc date jg
---------- ---------- ---------- --------------------
1 Q1 2007-01-01 10.2
10.3
9.8
2 Q2 2007-01-05 11.0
10.2
3 Q3 2007-03-01 12.0
(所影响的行数为 6 行)
*/
------解决方案--------------------
--少了个条件
SELECT ih= CASE WHEN B.ID >= 2 THEN '' ELSE CAST(A.ih AS VARCHAR) END
,hc=CASE WHEN B.ID >= 2 THEN '' ELSE ISNULL(A.hc,'') END,
DATE = CASE WHEN
(SELECT DATE FROM
(
SELECT ID = (SELECT COUNT(1) FROM T1 WHERE A.ih = ih AND date<=a.date),*
FROM T1 A
) M
WHERE M.ID-1 = A.ID AND A.ih = M.ih ) = A.DATE
THEN ''
ELSE ISNULL(A.DATE,'')
END,
B.jg
FROM
(
SELECT ID = (SELECT COUNT(1) FROM T1 WHERE A.ih = ih AND date<=a.date),*
FROM T1 A
) A
RIGHT JOIN
(
SELECT ID = (SELECT COUNT(1) FROM T2 WHERE A.ih = ih AND idx<=a.idx),*
FROM T2 A
) B ON A.ID = B.ID AND A.ih = B.ih