如何通过比较两行来选择值 [英] how to select values by comparing two rows
本文介绍了如何通过比较两行来选择值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨朋友们,
i需要从表中获取不同的 KEY_ACCT TPOS_INTRA
条件是
1) NUM_ACCT_TYPE 0和1
2)的帐户在选择时请记住 KEY_ACCT,CDE_MSDW_SEC 值相同
KEY_ACCT NUM_ACCT_TYPE CDE_MSDW_SEC
----------------- --------- ------------- ------------
1999-09-16 16:19:09.000385 0 0000AA34H
1999-09-16 16:19:09.000385 1 0000AA34H
1999-10-06 14:42:43.000615 0 0000ABD09
1999-10-06 14:42:43.000615 0 0000ACR64
1999-10-06 14:42:43.000615 0 0000AFW65
1999-10-06 14:42:43.000615 0 0000AHD48
1999-10-06 14:42:43.000615 0 0000AHH17
1999-10-06 14:42:43.000615 0 0000AWA68
1999-10-06 14:42:43.000615 0 0000CJQ59
1999-10-06 14:42:43.000615 0 000000360
1999-11-15 15:41:46.000731 0 000004465
1999-11-15 15:41:46.000731 1 000004465
< br $>
我要求的结果是
1999-09-16 16:19:09.000385
1999-11-15 15:41:46.000731 >
*新增
查询哪个我我正在使用
SELECT A.KEY_ACCT,A.NUM_ACCT_TYPE,A.CDE_MSDW_SEC
FROM DBQPEDB.TPOS_INTRA JOIN DBQPEDB.TPOS_INTRA B
ON A.KEY_ACCT = B.KEY_ACCT AND A.CDE_MSDW_SEC = B.CDE_MSDW_SEC WHERE
A.NUM_ACCT_TYPE IN ( 0 , 1 ) FETCH FIRST 10 仅限ROWS
请参阅示例sql http://sqlfiddle.com/#! 2 / 5e351f / 1 [ ^ ]
解决方案
请试试这个
SELECT A.KEY_ACCT,A.NUM_ACCT_TYPE,A.CDE_MSDW_SEC
FROM DBQPEDB.TPOS_INTRA with ( NOLOCK )其中 A.CDE_MSDW_SEC in
(
SELECT B.CDE_MSDW_SEC FROM DBQPEDB .TPOS_INTRA B with ( NOLOCK )其中 B.NUM_ACCT_TYPE = ' 1'
)和 A.NUM_ACCT_TYPE = ' 0'
Hi friends,
i need to fetch distinct KEY_ACCT from the table TPOS_INTRA
criteria is
1) The account which have NUM_ACCT_TYPE both 0 and 1
2) while selecting keep in mind both KEY_ACCT,CDE_MSDW_SEC values are same
KEY_ACCT NUM_ACCT_TYPE CDE_MSDW_SEC
-------------------------- ------------- ------------
1999-09-16 16:19:09.000385 0 0000AA34H
1999-09-16 16:19:09.000385 1 0000AA34H
1999-10-06 14:42:43.000615 0 0000ABD09
1999-10-06 14:42:43.000615 0 0000ACR64
1999-10-06 14:42:43.000615 0 0000AFW65
1999-10-06 14:42:43.000615 0 0000AHD48
1999-10-06 14:42:43.000615 0 0000AHH17
1999-10-06 14:42:43.000615 0 0000AWA68
1999-10-06 14:42:43.000615 0 0000CJQ59
1999-10-06 14:42:43.000615 0 000000360
1999-11-15 15:41:46.000731 0 000004465
1999-11-15 15:41:46.000731 1 000004465
my required result is
1999-09-16 16:19:09.000385
1999-11-15 15:41:46.000731
*Newly added
Query which i am using
SELECT A.KEY_ACCT, A.NUM_ACCT_TYPE, A.CDE_MSDW_SEC
FROM DBQPEDB.TPOS_INTRA A JOIN DBQPEDB.TPOS_INTRA B
ON A.KEY_ACCT = B.KEY_ACCT AND A.CDE_MSDW_SEC=B.CDE_MSDW_SEC WHERE
A.NUM_ACCT_TYPE IN (0,1) FETCH FIRST 10 ROWS ONLY
see sample sql http://sqlfiddle.com/#!2/5e351f/1[^]
解决方案
Please try this
SELECT A.KEY_ACCT, A.NUM_ACCT_TYPE, A.CDE_MSDW_SEC FROM DBQPEDB.TPOS_INTRA A with (NOLOCK) where A.CDE_MSDW_SEC in ( SELECT B.CDE_MSDW_SEC FROM DBQPEDB.TPOS_INTRA B with (NOLOCK) where B.NUM_ACCT_TYPE = '1' ) and A.NUM_ACCT_TYPE = '0'
这篇关于如何通过比较两行来选择值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文