Geek Answers Handbook

SQL: select transactions in which rows do not meet the criteria within the same table

I have a table with transactions:

Transactions
------------
id | account | type | date_time             | amount
----------------------------------------------------
 1 | 001     | 'R'  | '2012-01-01 10:01:00' | 1000
 2 | 003     | 'R'  | '2012-01-02 12:53:10' | 1500
 3 | 003     | 'A'  | '2012-01-03 13:10:01' | -1500
 4 | 002     | 'R'  | '2012-01-03 17:56:00' | 2000
 5 | 001     | 'R'  | '2012-01-04 12:30:01' | 1000
 6 | 002     | 'A'  | '2012-01-04 13:23:01' | -2000
 7 | 003     | 'R'  | '2012-01-04 15:13:10' | 3000
 8 | 003     | 'R'  | '2012-01-05 12:12:00' | 1250
 9 | 003     | 'A'  | '2012-01-06 17:24:01' | -1250

and I want to select all the defined types ("R"), but not those that directly (in the order of the date_time field) have a different transaction of a different type ("A") for the same account ...

Thus, the query should call the following lines, given the previous example:

id | account |type  | date                  | amount
----------------------------------------------------
 1 | 001     | 'R'  | '2012-01-01 10:01:00' | 1000
 5 | 001     | 'R'  | '2012-01-04 12:30:01' | 1000
 7 | 003     | 'R'  | '2012-01-04 15:13:10' | 3000

( , 2 , 3 "" ... 4 "" 6 ", 7 ( 003 # 2, 7 " "), 8 ( 003, 9, 7, : 8...

Joins, Where, , ...

:

:

   SELECT trans.type as type,
          trans.amount as amount,
          trans.date_time as dt,
          trans.account as acct,
     FROM Transactions trans
INNER JOIN ( SELECT t.type AS type, t.acct AS acct, t.date_time AS date_time
               FROM Transactions t
              WHERE t.date_time > trans.date_time
           ORDER BY t.date_time DESC
          ) AS nextTrans
       ON nextTrans.acct = trans.acct
    WHERE trans.type IN ('R')
      AND nextTrans.type NOT IN ('A')
 ORDER BY DATE(trans.date_time) ASC

, JOIN MySQL.

:

   SELECT trans.type as type,
          trans.amount as amount,
          trans.date_time as dt,
          trans.account as acct,
     FROM Transactions trans
    WHERE trans.type IN ('R')
      AND trans.datetime <
          ( SELECT t.date_time AS date_time
               FROM Transactions t
              WHERE t.account = trans.account
           ORDER BY t.date_time DESC
          ) AS nextTrans
       ON nextTrans.acct = trans.acct

 ORDER BY DATE(trans.date_time) ASC

, WHERE MySQL, , ...

:

, . :

SELECT *
  FROM (SELECT t1.*, tFlagged.id AS cancId, tFlagged.type AS cancFlag
          FROM transactions t1
     LEFT JOIN (SELECT t2.*
                  FROM transactions t2
              ORDER BY t2.date_time ASC ) tFlagged
            ON (t1.account=tFlagged.account
                  AND
                t1.date_time < tFlagged.date_time)
         WHERE t1.type = 'R'
      GROUP BY t1.id) tCanc
 WHERE tCanc.cancFlag IS NULL
    OR tCanc.cancFlag <> 'A'

, _time. _time. id , .

select , "A", , . , "R", ...

:

+----+---------+------+---------------------+--------+--------+----------+
| id | account | type | date_time           | amount | cancId | cancFlag |
+----+---------+------+---------------------+--------+--------+----------+
|  1 | 001     |   R  | 2012-01-01 10:01:00 |   1000 |      5 | R        |
|  5 | 001     |   R  | 2012-01-04 12:30:01 |   1000 |   NULL | NULL     |
|  7 | 003     |   R  | 2012-01-04 15:13:10 |   3000 |      8 | R        |
+----+---------+------+---------------------+--------+--------+----------+

, ... !

, . ( , ), ~ 10 000 1 .44 . , ... ( , , ? ? ?)

+5
5

, . -, , . , 10 12, 11 .

> select * from transactions order by date_time;
+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+
|  1 |       1 | R    | 2012-01-01 10:01:00 |   1000 |
|  2 |       3 | R    | 2012-01-02 12:53:10 |   1500 |
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 |
|  4 |       2 | R    | 2012-01-03 17:56:00 |   2000 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 |
|  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 |
| 12 |       3 | A    | 2012-01-08 00:00:00 |  -1250 |
| 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 |
| 13 |       3 | A    | 2012-01-10 00:00:00 |  -1500 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 |
+----+---------+------+---------------------+--------+
16 rows in set (0.00 sec)

" , ":

SELECT t2.*,
       MAX(t1.date_time) AS prev_date
FROM transactions t1
JOIN transactions t2
ON (t1.account = t2.account
   AND t2.date_time > t1.date_time)
GROUP BY t2.account,t2.date_time
ORDER BY t2.date_time;

+----+---------+------+---------------------+--------+---------------------+
| id | account | type | date_time           | amount | prev_date           |
+----+---------+------+---------------------+--------+---------------------+
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 | 2012-01-02 12:53:10 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 | 2012-01-01 10:01:00 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 | 2012-01-03 17:56:00 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 | 2012-01-03 13:10:01 |
|  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 | 2012-01-04 15:13:10 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 | 2012-01-05 12:12:00 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 | 2012-01-06 17:24:01 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 | 2012-01-07 00:00:00 |
| 12 |       3 | A    | 2012-01-08 00:00:00 |  -1250 | 2012-01-07 05:00:00 |
| 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 | 2012-01-04 13:23:01 |
| 13 |       3 | A    | 2012-01-10 00:00:00 |  -1500 | 2012-01-08 00:00:00 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 | 2012-01-09 00:00:00 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 | 2012-01-11 04:00:00 |
+----+---------+------+---------------------+--------+---------------------+
13 rows in set (0.00 sec)

, . , , , "", "R", -

SELECT
  t3.*,transactions.*
FROM
  transactions
  JOIN
  (SELECT t2.*,
          MAX(t1.date_time) AS prev_date
   FROM transactions t1
   JOIN transactions t2
   ON (t1.account = t2.account
      AND t2.date_time > t1.date_time)
   GROUP BY t2.account,t2.date_time) t3
  ON t3.account = transactions.account
     AND t3.prev_date = transactions.date_time
     AND t3.type='A'
     AND transactions.type='R'
     AND t3.amount + transactions.amount = 0
  ORDER BY t3.date_time;


+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount | prev_date           | id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
|  3 |       3 | A    | 2012-01-03 13:10:01 |  -1500 | 2012-01-02 12:53:10 |  2 |       3 | R    | 2012-01-02 12:53:10 |   1500 |
|  6 |       2 | A    | 2012-01-04 13:23:01 |  -2000 | 2012-01-03 17:56:00 |  4 |       2 | R    | 2012-01-03 17:56:00 |   2000 |
|  9 |       3 | A    | 2012-01-06 17:24:01 |  -1250 | 2012-01-05 12:12:00 |  8 |       3 | R    | 2012-01-05 12:12:00 |   1250 |
| 15 |       2 | A    | 2012-01-11 04:00:00 |  -2000 | 2012-01-09 00:00:00 | 14 |       2 | R    | 2012-01-09 00:00:00 |   2000 |
+----+---------+------+---------------------+--------+---------------------+----+---------+------+---------------------+--------+
4 rows in set (0.00 sec)

, - . LEFT JOIN, :

SELECT
  transactions.*
FROM
  transactions
LEFT JOIN
  (SELECT
     transactions.id
   FROM
     transactions
     JOIN
     (SELECT t2.*,
             MAX(t1.date_time) AS prev_date
      FROM transactions t1
      JOIN transactions t2
      ON (t1.account = t2.account
         AND t2.date_time > t1.date_time)
      GROUP BY t2.account,t2.date_time) t3
     ON t3.account = transactions.account
        AND t3.prev_date = transactions.date_time
        AND t3.type='A'
        AND transactions.type='R'
        AND t3.amount + transactions.amount = 0) t4
  USING(id)
  WHERE t4.id IS NULL
    AND transactions.type = 'R'
  ORDER BY transactions.date_time;

+----+---------+------+---------------------+--------+
| id | account | type | date_time           | amount |
+----+---------+------+---------------------+--------+
|  1 |       1 | R    | 2012-01-01 10:01:00 |   1000 |
|  5 |       1 | R    | 2012-01-04 12:30:01 |   1000 |
|  7 |       3 | R    | 2012-01-04 15:13:10 |   3000 |
| 10 |       3 | R    | 2012-01-07 00:00:00 |   1250 |
| 11 |       3 | R    | 2012-01-07 05:00:00 |   4000 |
| 16 |       2 | R    | 2012-01-12 00:00:00 |   5000 |
+----+---------+------+---------------------+--------+
+1

( 2) :

 SELECT trans.tp as type,
trans.id as id,
 trans.amount as amount, 
trans.date_time as dt, 
trans.account as acct
FROM Transactions trans
WHERE trans.tp = 'R' 
AND trans.account NOT IN (SELECT t.account AS acct
   FROM Transactions t
 WHERE t.date_time > trans.date_time
 AND t.tp = 'A'
AND t.amount = (trans.amount)-((trans.amount)*2)
  ORDER BY t.date_time DESC
 )  ;
+1

MSSQL. mysql. , , . id = 7 id = 3.

mssql

create table Transactions(id int,account varchar(5),  tp char(1),date_time datetime,amount int)

insert into Transactions values (1,'001','R','2012-01-01 10:01:00',1000)
insert into Transactions values (2,'003','R','2012-01-02 12:53:10',1500)
insert into Transactions values (3,'003','A','2012-01-03 13:10:01',-1500)
insert into Transactions values (4,'002','R','2012-01-03 17:56:00',2000)
insert into Transactions values (5,'001','R','2012-01-04 12:30:01',1000)
insert into Transactions values (6,'002','A','2012-01-04 13:23:01',-2000)
insert into Transactions values (7,'003','R','2012-01-04 15:13:10',3000)


select t.id, t.account, t.date_time, t.amount
from Transactions t
where t.tp = 'R'
and not exists
(
    select account, date_time
    from Transactions
    where tp = 'A'
    and account = t.account
    and t.date_time < date_time
)
+1

_time - ( , ID) - :

SELECT t1.*
FROM transactions t1 JOIN transactions t2 ON(t2.id = t1.id + 1)
WHERE t1.type = 'R'
  AND NOT((t2.type = 'A') AND ((t1.amount + t2.amount) = 0))

. ID ; .

0

To improve your query, try the following:

SELECT t1.*, tFlagged.id AS cancId, tFlagged.tp AS cancFlag FROM t t1
LEFT JOIN t tFlagged
ON t1.account = tFlagged.account AND t1.date_time < tFlagged.date_time
WHERE t1.tp = 'R' 
GROUP BY t1.id
HAVING tFlagged.tp is null or tFlagged.tp <> 'A'

It will work much faster ... hopefully providing the same results: P

0
source

More articles:


All Articles