两表合并后，去掉重复数据的问题（在线等）谢谢
因为数据很大，故按月存放，现在表 wx200703数据如下：
sh ph cd rq
lc1 055 66.80 20070315
lc1 055 66.30 20070320
lc1 056 66.40 20070315
lc1 056 65.90 20070320
lc1 057 63.60 20070315
lc1 057 63.10 20070320
。。。
表 wx200704数据如下：
sh ph cd rq
lc1 055 63.80 20070410
lc1 057 60.60 20070410
lc1 058 63.10 20070420
。。。
想查出两表合并后，sh,ph相同的情况下最后日期的数据：
sh ph cd rq
lc1 055 63.80 20070410
lc1 057 60.60 20070410
lc1 056 65.90 20070320
lc1 058 63.10 20070420
[解决办法]
select * from tab,(select sh,ph from tab group by sh,ph having count(sh) > 1 and count(ph) > 1) a where tab.sh = a.sh and tab.ph = a.ph
相同的记录
[解决办法]
if object_id( 'pubs..wx200703 ') is not null
drop table wx200703
go
create table wx200703(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.80, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.30, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 66.40, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 65.90, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.60, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.10, '20070320 ')
go
if object_id( 'pubs..wx200704 ') is not null
drop table wx200704
go
create table wx200704(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '055 ', 63.80, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '057 ', 60.60, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '058 ', 63.10, '20070420 ')
go
select t1.* from
(
select * from wx200703
union all
select * from wx200704
) t1,
(
select sh,ph,max(rq) rq from
(
select * from wx200703
union all
select * from wx200704
) t2 group by sh,ph
) t3
where t1.sh = t3.sh and t1.ph = t3.ph and t1.rq = t3.rq
order by t1.sh,t1.ph
drop table wx200703,wx200704
/*
sh ph cd rq
---------- ---------- -------------------- ----------
lc1 055 63.80 20070410
lc1 056 65.90 20070320
lc1 057 60.60 20070410
lc1 058 63.10 20070420
（所影响的行数为 4 行）
*/
[解决办法]
declare @tab table(sh varchar(20),ph varchar(20),cd decimal(18,2),rq char(8))
insert @tab select * from wx200704 union all select * from wx200704
select * from @tab a where not exists(select 1 from @tab where sh=a.sh and ph=a.ph and rq> a.rq)
[解决办法]
借（dawugui(潇洒老乌龟)）的表结构，try:
create table wx200703(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.80, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.30, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 66.40, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 65.90, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.60, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.10, '20070320 ')
go
create table wx200704(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '055 ', 63.80, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '057 ', 60.60, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '058 ', 63.10, '20070420 ')
go
SELECT * FROM wx200703 AS A
WHERE NOT EXISTS(SELECT 1 FROM wx200703 AS B WHERE A.sh=B.sh AND A.ph=B.ph AND B.rq> A.rq)
AND NOT EXISTS(SELECT 1 FROM wx200704 AS B WHERE A.sh=B.sh AND A.ph=B.ph AND B.rq> A.rq)
UNION ALL
SELECT * FROM wx200704 AS A
WHERE NOT EXISTS(SELECT 1 FROM wx200704 AS B WHERE A.sh=B.sh AND A.ph=B.ph AND B.rq> A.rq)
AND NOT EXISTS(SELECT 1 FROM wx200703 AS B WHERE A.sh=B.sh AND A.ph=B.ph AND B.rq> A.rq)
DROP TABLE wx200703,wx200704
[解决办法]
借（dawugui(潇洒老乌龟)）的表结构，try:
create table wx200703(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.80, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '055 ', 66.30, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 66.40, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '056 ', 65.90, '20070320 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.60, '20070315 ')
insert into wx200703(sh,ph,cd,rq) values( 'lc1 ', '057 ', 63.10, '20070320 ')
go
create table wx200704(sh varchar(10),ph varchar(10),cd decimal(18,2),rq varchar(10))
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '055 ', 63.80, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '057 ', 60.60, '20070410 ')
insert into wx200704(sh,ph,cd,rq) values( 'lc1 ', '058 ', 63.10, '20070420 ')
go
--将两表合并
select *
into wx
from
(select * from wx200703
union all
select * from wx200704)
t
--合并后用一个Delete语句即可完成
delete A from wx A ,wx B where A.sh=B.sh and A.ph=B.ph and A.rq <B.rq
[解决办法]
select * from (
select * from wx200703
union all
select * from wx200704) a
where (select count(1)+1 from (
select * from wx200703
union all
select * from wx200704) b where a.ph=b.ph and a.sh=b.sh and a.rq <b.rq)=1
order by sh,ph