如何通过比较两行来选择值 [英] 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.0​​00731 0 000004465 
 1999-11-15 15：41：46.0​​00731 1 000004465 

< br $>
我要求的结果是

1999-09-16 16：19：09.000385

1999-11-15 15：41：46.0​​00731




*新增



查询哪个我我正在使用

  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屋！