mysql query on an audit table to get latest row where no delete row exists for each user and type

问题: so im sure this won't tax anyone but new to all this! looking for a way to pull out data from an audit table which gives a view of all the created rows where they have not...

问题:

so im sure this won't tax anyone but new to all this! looking for a way to pull out data from an audit table which gives a view of all the created rows where they have not subsequently been deleted. so by example...

CREATE TABLE `test1` (
    `tID` INT(11) NULL DEFAULT NULL,
    `tDate` DATE NULL DEFAULT NULL,
    `tName` VARCHAR(50) NULL DEFAULT NULL,
    `tType` VARCHAR(50) NULL DEFAULT NULL,
    `tAction` VARCHAR(50) NULL DEFAULT NULL
)

INSERT INTO `test1` VALUES (1, '2019-02-01', 'Bob', 'a', 'Create');

INSERT INTO `test1` VALUES (2, '2019-02-02', 'Frank', 'a', 'Create');

INSERT INTO `test1` VALUES (3, '2019-02-03', 'Jim', 'b', 'Create');

INSERT INTO `test1` VALUES (4, '2019-02-04', 'Frank', 'a', 'Delete');

INSERT INTO `test1` VALUES (5, '2019-02-05', 'Bob', 'b', 'Create');

INSERT INTO `test1` VALUES (6, '2019-02-06', 'Bob', 'a', 'Delete');

INSERT INTO `test1` VALUES (7, '2019-02-07', 'Bob', 'a', 'Create');

INSERT INTO `test1` VALUES (8, '2019-02-08', 'Frank', 'b', 'Create');

INSERT INTO `test1` VALUES (9, '2019-02-09', 'Bob', 'b', 'Delete');

INSERT INTO `test1` VALUES (10, '2019-02-10', 'Bob', 'b', 'Create');

INSERT INTO `test1` VALUES (11, '2019-02-11', 'kate', 'a', 'Create');

INSERT INTO `test1` VALUES (12, '2019-02-12', 'kate', 'a', 'Delete');

Want the output to be something like...

"Bob"   "2019-02-07"    "a"

"Jim"   "2019-02-03"    "b"

"Bob"   "2019-02-10"    "b"

"Frank" "2019-02-08"    "b"

so i can can get max where create easy enough but how should i exclude the ones that have been deleted after creation?

this gets me close... but i need to remove the ones with a later deleted row.. SELECT max(tDate), tname, tType FROM test1 WHERE tAction = 'create' GROUP BY tname, ttype

thanks for any help! j.


回答1:

Try using correlated subquery

DEMO

select * from test1 a where a.taction='Create' and not exists
( 
  select 1 from test1 b where a.tname=b.tname and a.ttype=b.ttype
  and a.tdate<b.tdate and b.taction<>'Create'
)

OUTPUT:

tID tDate       tName   tType   tAction
3   2019-02-03  Jim      b      Create
7   2019-02-07  Bob      a      Create
8   2019-02-08  Frank    b      Create
10  2019-02-10  Bob      b      Create

回答2:

select  *
from    test1 t1
where   t1.tAction <> 'Delete'
        and not exists
        (
        select  *
        from    test1 t2
        where   t2.tName = t1.tName  -- same user
                and t2.tType = t1.tType -- and same type
                and t2.tAction = 'Delete'  -- deleted
                and t1.tDate < t2.tDate  -- at a later time
        )

回答3:

SELECT
  tDate,
  tname,
  tType
FROM test1
WHERE tAction <> 'Delete'

I hope this is the query you are looking for. Reference SQL: How to perform string does not equal

  • 发表于 2019-02-14 18:18
  • 阅读 ( 189 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除