匹配两行的SQL条件。

I am trying to extract the data from SQL developer which matches two row conditions. Both the rows has one unique value ( ID ) and table name is abc.tcd

我试图从SQL developer中提取匹配两行条件的数据。这两行都有一个惟一的值(ID)，表名是abc.tcd

ID  =  Type =   GL code = amount
1   =   Debit =  0701  =  10000
1  =    credit = 0601 =   10000
1  =    Credit=  0501  =  1000
1   =   Debit=   0401   = 1000
2   =   Debit =  0701  =  9000
2   =   credit = 0801  =  9000
3   =   Debit  = 0701  =  6000
3   =   credit = 0601  =  6000

Condition 1 :

条件1:

GL code = '0701'   having Type = 'Debit'

condition 2 :

条件2:

GL code = '0601'   having Type = 'Credit'

Expected output :

预期的输出:

ID    Type    GL code  amount

1  =    Debit  = 0701 =   10000
1  =    credit = 0601 =   10000
1  =    Credit = 0501 =   1000
1  =    Debit  = 0401 =   1000
3  =    Debit  = 0701 =   6000
3  =    credit = 0601 =   6000

Output should display all the rows based on ID

输出应该基于ID显示所有的行

2 个解决方案

#1

If I understand the question correctly, you want to extract all the rows for some ID where two different rows fulfill two different conditions. you could use a couple of in operators:

如果我正确地理解了这个问题，您想要为某个ID提取所有的行，其中两个不同的行满足两个不同的条件。你可以使用几个in操作符:

SELECT *
FROM   mytable
WHERE  id IN (SELECT id
              FROM   my_table
              WHERE  GLCode = '0701' AND Type = 'Debit')
        AND id IN (SELECT id
                   FROM   my_table
                   WHERE  GLCode = '0601' AND Type = 'Credit')

Of course, this can be easily translated to use the exists operator:

当然，这可以很容易地翻译为使用现有操作符:

SELECT *
FROM   mytable a
WHERE  EXISTS (SELECT *
               FROM   my_table b
               WHERE  a.id = b.id AND b.GLCode = '0701' AND b.Type = 'Debit')
        AND EXISTS (SELECT *
                    FROM   my_table c
                    WHERE  a.id = c.id AND c.GLCode = '0601' AND c.Type = 'Credit')

A more elegant way might be to have all the conditions in a single query with ors and count how many of them are fulfilled:

一种更优雅的方法可能是在单个查询中包含所有条件，并计算其中有多少满足:

SELECT *
FROM   mytable
WHERE  id IN (SELECT   id
              FROM     my_table
              WHERE    (GLCode = '0701' AND Type = 'Debit') OR
                       (GLCode = '0601' AND Type = 'Credit')
              GROUP BY id
              HAVING   COUNT(*) = 2)

#2

Another alternative:

另一个选择:

SELECT * FROM MyTable
INNER JOIN
(
  SELECT ID
  FROM MyTable
  WHERE (GLCode = '0701' AND Type = 'Debit') OR (GLCode = '0601' AND Type = 'Credit')
  GROUP BY ID
  HAVING COUNT(DISTINCT GLCode) = 2 AND COUNT(DISTINCT Type) = 2
) X
ON MyTable.ID = x.ID;

SqlFiddle here

SqlFiddle这里

Basically "find the ids having two distinct rows meeting the criteria". We then return all rows with this ID

基本上，“查找具有符合条件的两个不同行的id”。然后用这个ID返回所有行

Edit

编辑

Your real query would look like:

你真正的问题应该是:

SELECT *
FROM tbaadm.ctd 
INNER JOIN
(SELECT Tran_id 
    FROM tbaadm.ctd 
    WHERE ((GL_SUB_HEAD_CODE = '06106' AND PART_TRAN_TYPE = 'C') 
          OR (GL_SUB_HEAD_CODE = '29101' AND PART_TRAN_TYPE = 'D'))
       AND (tran_date >= '01-12-2014' AND tran_date < '30-12-2014')
    GROUP BY Tran_id
    HAVING COUNT(DISTINCT GL_SUB_HEAD_CODE) = 2 AND COUNT(DISTINCT PART_TRAN_TYPE) = 2
) X
ON tbaadm.ctd = x.Tran_id;

The parenthesis around the ANDs are obviously redundant due to operator precedence, but might help with readability?

由于运算符优先级的原因，在和周围的括号显然是多余的，但是可能有助于提高可读性吗?

Also, note with Date range checking that it is convention to include start date but exclude end date, viz x >= start and x < end

另外，请注意，根据日期范围检查，约定包括开始日期，但不包括结束日期，即x >=开始，x < end

#1