本文发表在 rolia.net 枫下论坛First, what's 'table' called in Chinese?
I have two tables with exactly same schema. Each table has 4 columns, they are id, period, field_name and field_value. In #delta table, I have new/updated data and #previous table has old data. I need to use the #delta data to retrieve any data from #previous where they have same id, period but different field_name. Given the following example, table #delta has two records with id as 1, period as A. My query result should come out as any records in #previous having id=1, period=A, but field_name !='field1' or field_name!='field2'. So, only two records (bottom 2, not 3rd one) from #previous meet the condition. field_name has hundreds of name in it.
I have my first query done. I have duplicated records which is understandable. So, I added distinct after 'select'. Is there faster way to run the query. This sample has 5 rows only. In actualy database, there are half million of rows on each table. Speed is important. I will run the test and post the result later.
Thanks, everyone.
--script one
create table #delta(id int, period varchar(20),field_name varchar(20),field_value float)
insert into #delta values (1,'A','field1',1)
insert into #delta values (1,'A','field2',2)
--script two
create table #previous(id int, period varchar(20),field_name varchar(20),field_value float)
insert into #previous values (2,'C','field2',3)
insert into #previous values (3,'A','field5',4)
insert into #previous values (1,'A','field1',3)
insert into #previous values (1,'A','field5',4)
insert into #previous values (1,'A','field6',1)
--script three
select * from #delta
select * from #previous
--script four
select d.id,d.period,p.field_name,p.field_value
from #delta d, #previous p
where not exists(select 1 from #delta dd
where dd.id=p.id and
dd.period=p.period and
dd.field_name=p.field_name) and
d.id=p.id and
d.period=d.period and
d.field_name!=p.field_name更多精彩文章及讨论，请光临枫下论坛 rolia.net
I have two tables with exactly same schema. Each table has 4 columns, they are id, period, field_name and field_value. In #delta table, I have new/updated data and #previous table has old data. I need to use the #delta data to retrieve any data from #previous where they have same id, period but different field_name. Given the following example, table #delta has two records with id as 1, period as A. My query result should come out as any records in #previous having id=1, period=A, but field_name !='field1' or field_name!='field2'. So, only two records (bottom 2, not 3rd one) from #previous meet the condition. field_name has hundreds of name in it.
I have my first query done. I have duplicated records which is understandable. So, I added distinct after 'select'. Is there faster way to run the query. This sample has 5 rows only. In actualy database, there are half million of rows on each table. Speed is important. I will run the test and post the result later.
Thanks, everyone.
--script one
create table #delta(id int, period varchar(20),field_name varchar(20),field_value float)
insert into #delta values (1,'A','field1',1)
insert into #delta values (1,'A','field2',2)
--script two
create table #previous(id int, period varchar(20),field_name varchar(20),field_value float)
insert into #previous values (2,'C','field2',3)
insert into #previous values (3,'A','field5',4)
insert into #previous values (1,'A','field1',3)
insert into #previous values (1,'A','field5',4)
insert into #previous values (1,'A','field6',1)
--script three
select * from #delta
select * from #previous
--script four
select d.id,d.period,p.field_name,p.field_value
from #delta d, #previous p
where not exists(select 1 from #delta dd
where dd.id=p.id and
dd.period=p.period and
dd.field_name=p.field_name) and
d.id=p.id and
d.period=d.period and
d.field_name!=p.field_name更多精彩文章及讨论，请光临枫下论坛 rolia.net