gpt4 book ai didi

java - SQL从一个表中删除+一个可连接的表?

转载 作者:IT王子 更新时间:2023-10-29 06:29:35 26 4
gpt4 key购买 nike

我有三个表:logactivity 和可连接的 (many2many) log_activity(带有 log_idactivity_id + 作为列的附加信息数据)。

我想从loglog_activity 中删除。

我想保留特定用户的所有日志,而只保留其他用户的 100 行。这意味着我想删除所有匹配 WHERE log.user_id != 1 的行,但最后 100 行(ORDER BY log.timestamp DESC)。

我还想从可连接的 log_activity 中删除与被删除的日志相关的所有条目。 activity 表应该被触及。

我认为 db.delete(TABLE_NAME, whereClause , whereArgs); 在这种情况下没有帮助..

那么有人能够想出一个有效的解决方案吗?


更新更新更新更新更新更新更新更新更新更新


受 Jacob Eggers 和 plafond 的回答以及进一步研究的启发,我现在正在尝试这样,但它还不起作用:

CREATE TABLE IF NOT EXISTS log ( 
_id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL,
timestamp LONG NOT NULL
);

CREATE TABLE IF NOT EXISTS log_activity (
_id INTEGER PRIMARY KEY AUTOINCREMENT,
log_id INTEGER NOT NULL,
activity_id INTEGER NOT NULL,
points INTEGER NOT NULL,
FOREIGN KEY(log_id) REFERENCES log(_id) ON DELETE CASCADE,
FOREIGN KEY(activity_id) REFERENCES activity(_id) ON DELETE CASCADE
);

现在是安卓部分:

SQLiteDatabase db = openHelper.getWritableDatabase();
db.execSQL("PRAGMA foreign_keys = ON;");
db.execSQL(CREATE_LOG); // see sql above
db.execSQL(CREATE_ACTIVITY); // not shown here, but like the sql-creates above
db.execSQL(CREATE_LOG_ACTIVITY); // see sql above

// ... insert some data ...
INSERT INTO "log" VALUES(1,1,1307797289000);
INSERT INTO "log" VALUES(2,1,1307710289000);
INSERT INTO "log" VALUES(3,2,1308089465000);
INSERT INTO "log" VALUES(4,2,1308079465000);

INSERT INTO "log_activity" VALUES(1,1,1,1);
INSERT INTO "log_activity" VALUES(2,1,2,2);
INSERT INTO "log_activity" VALUES(3,2,1,1);
INSERT INTO "log_activity" VALUES(4,2,2,2);
INSERT INTO "log_activity" VALUES(5,3,1,1);
INSERT INTO "log_activity" VALUES(6,3,2,2);
INSERT INTO "log_activity" VALUES(7,4,1,1);
INSERT INTO "log_activity" VALUES(8,4,2,2);

// check count of logs
Cursor c = db.query(false, "log", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "log count before: "+c.getCount());

// check count of log_activities
Cursor c2 = db.query(false, "log_activity", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "la count before: "+c2.getCount());

// delete some log-rows
long userId = 1;
int keepXLogsOfOthers = 1;
String del = "DELETE FROM log" +
" WHERE user_id != " + userId +
" AND log._id NOT IN (" +
" SELECT _id" +
" FROM (" +
" SELECT _id" +
" FROM log" +
" WHERE user_id != " + userId +
" ORDER BY timestamp DESC" +
" LIMIT " + keepXLogsOfOthers +
" ) logs_of_others_to_keep" +
");";
db.execSql(del);

// check count of logs
Cursor c3 = db.query(false, "log", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "log count after: "+c3.getCount());

// check count of log_activities
Cursor c4 = db.query(false, "log_activity", null, null, null, null, null, "_id asc", null);
android.util.Log.d("TEST", "la count after: "+c4.getCount());

输出:

06-16 10:40:01.748: DEBUG/TEST(451): log count before: 4
06-16 10:40:01.748: DEBUG/TEST(451): la count before: 8
06-16 10:40:01.828: DEBUG/TEST(451): log count after: 3
06-16 10:40:01.838: DEBUG/TEST(451): la count after: 8

这意味着它自己的 DELETE 操作没问题(我还检查了是否删除了正确的行,这解决了第一个问题!!),但是 ON DELETE CASCADE 不起作用...为什么?

最佳答案

您可以创建一个触发器来自动执行此操作。

CREATE TRIGGER [delete_log_joins]
BEFORE DELETE
ON [log]
FOR EACH ROW
BEGIN
DELETE FROM log_activity WHERE log_activity.log_id = old.id;
END

要选择删除除最新的 100 条日志之外的所有日志,您可以执行如下操作:

delete * from log where log.id not in (
select id
from (
select l.id
from log l
where l.id in (
select top 100 l2.id
from log l2
where l2.user_id = l.user_id
order by log.timestamp desc
)
) the_tops
);

我不确定它的性能如何,也许有人可以改进它。

关于java - SQL从一个表中删除+一个可连接的表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/6365372/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com