This topic has been archived. It cannot be replied.
-
工作学习 / 专业技术讨论 / 请教一个SQL QUERY问题，比较两个table data and get the difference.
-madeinchina(强力万能胶);
2007-8-16
{1985}
(#3873346@0)
-
table就叫“表”。
-newkid(newkid);
2007-8-16
{551}
(#3873462@0)
-
你用的是什么DB? 不同的DB有不同的feature，solution也会不一样。不知道用JOIN是不是会快一点，你可以试试。容我再想想有没有其它tricky点的方法。select DISTINCT
p.id,p.period,p.field_name,p.field_value
from #delta d
JOIN #previous p
ON d.id = p.id
AND d.period = p.period
AND d.field_name != p.field_name
AND d.field_value != p.field_value
-bdbs(不多不少);
2007-8-16
{203}
(#3873499@0)
-
嗯，把最后一行去掉就符合他的要求了。我又把楼主的问题看了一下，两张表都有50万行，我的第二种方法显然不行了。
-newkid(newkid);
2007-8-16
{70}
(#3873606@0)
-
newkid's two query returns right answer. bdbs's query returns is not that right.the middle row (1,'A','field1',3) of #previous should not return. because in #delta table, row 1 has (1,'A','field1',x) already.
I am using SQL 2005(+sp2).
I need to dump some data in to do the performance comparing. Will give you update tomorrow.
-madeinchina(强力万能胶);
2007-8-16
{261}
(#3873789@0)
-
我审题不清，考虑到DISTINCT的performance不好，看来只有newkid的方法一可行了。
-bdbs(不多不少);
2007-8-16
(#3873901@0)
-
DISTINCT 作用于一个大数据集是会有PERFORMANCE问题，不知道楼主对查询结果有个估计没有，是什么数量级的，如果集合不大那问题也不大。这些都不好说，必须实际测试了才知道。
-newkid(newkid);
2007-8-16
(#3873964@0)
-
too long post. no time to read, but I wonder if "minus" would help in this? do not know if SQL S has a minus though
-win(秋天的菠菜);
2007-8-16
(#3873913@0)
-
Seems you shot every single post whole day long, no matter what topic related, involve, just deeply involved。。。
-noproblem(。o O);
2007-8-16
(#3873944@0)