代码之家 › 专栏 › 技术社区 › Ale

sqlite按较少的计数连接多个表

count group-by join sqlite

Ale · 技术社区 · 6 年前

我有这个数据库结构:

我需要展示 users 它可以执行 Task 在一个特定的日子里,首先显示出 Tasks (或者没有 任务 )分配到那天。

例如:

User A有1个 任务 当天分配的类型1。
用户 B有3个 任务 当天分配的类型1。
用户 C没有 任务 当天分配的类型1。

我想按以下顺序显示它们:用户C>用户A>用户B

我的问题是:

SELECT  *, COUNT(*)
FROM USER U
INNER JOIN USER_TASK_TYPE UT ON (U._id=UT.user_id)
LEFT JOIN TASK T ON (U._id = T.user_id)
where UT.TASK_TYPE_ID = ?
where T.CREATION_DATE = ?
GROUP BY T.user_id
ORDER BY COUNT(*) ASC;

仍然有问题,因为如果 用户 分配了一个任务,另一个 用户 没有,还是第一个 用户 先展示。

我很感激你的帮助。谢谢。

1 回复 | 直到 6 年前

MikeT 6 年前

首先,提供的架构不反映正在使用的查询:

没有 创建日期 中的列任务桌子。
这个 身份证件 列用户桌子应该是 _身份证 是的。

其次,您提供的查询将导致语法错误,原因是 在哪里? 被编码两次。

你对什么数据的描述还不清楚,比如你说:

用户A当天分配了1个类型为1的任务

什么表格存在于哪一行?

作为一个猜测,下面已经被使用

注意到:
- 存在多个不存在任务的用户
- 存在一个用户,白天不存在任务

:。-

-- DROP and Recreate/Create tables
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS task;
DROP TABLE IF EXISTS task_type;
DROP TABLE IF EXISTS user_task_type;
CREATE TABLE IF NOT EXISTS user (_id INTEGER PRIMARY KEY,  name TEXT, user_id INTEGER, user_name TEXT, password TEXT, user_type TEXT);
CREATE TABLE IF NOT EXISTS task(id INTEGER PRIMARY KEY, creation_date TEXT, description TEXT, duration int, status TEXT, user_id INTEGER, task_type_id INTEGER);
CREATE TABLE IF NOT EXISTS task_type(id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS user_task_type (id INTEGER PRIMARY KEY, user_id INTEGER, task_type_id INTEGER);

-- Add some users
INSERT INTO user (name,password,user_type) VALUES
    ('USER1','x','x'),('USER2','x','x'),('USER3','x','x'),('USER4','x','x'),('USER5','x','x'); 

-- Add some task types
INSERT INTO task_type (name) VALUES
    ('Sweep'),('Wash'),('Polish'),('Scrub');

-- Add some tasks for users
INSERT INTO task (creation_date, description, duration, status, user_id, task_type_id) VALUES
    ('2018-01-01','USER1 TASK01',10,'to be done', 1,1),
    ('2018-01-01','USER2 TASK01',10,'to be done', 2,1),
    ('2018-01-01','USER2 TASK01',10,'to be done', 2,1),
    ('2018-01-01','USER2 TASK01',10,'to be done', 2,1),
    ('2018-02-02', 'USER3 NOT ON THE DAY',10,'to be done another day',3,1) --<<<< for testing user on another day
    -- ('2018-01-01','???????',10,'????????',3,1); commented out used for testing
    ;

-- unclear what this is used for but match tasks SUPERFLUOUS?
INSERT INTO user_task_type (user_id, task_type_id) VALUES
    (1,1),(2,1),(2,1),(2,1),(3,1),
    (3,1) -- added a spurious row;
    ;

SELECT  *, COUNT(*)
FROM USER U
    JOIN USER_TASK_TYPE UT ON (U._id=UT.user_id)
    JOIN TASK T ON (U._id = T.user_id)
WHERE UT.TASK_TYPE_ID = 1
-- WHERE T.CREATION_DATE = '2018-01-01' <<<<<<<<<< Invalid used the following line
AND T.CREATION_DATE = '2018-01-01' -- <<<<<<<<<< assumed AND
GROUP BY T.user_id
ORDER BY COUNT(*) ASC;

结果是看起来和预期的一样(没有任务或当天没有任务的用户没有虚假的行)。但是,计数不是预期的,而是计数的平方。:。-

我认为计数的问题是与/使用user_task_type表有关,该表似乎是多余的和有问题的。因此,以下内容很可能符合您的要求:

SELECT  *, COUNT(*)
FROM USER U
    -- INNER JOIN USER_TASK_TYPE UT ON (U._id=UT.user_id)
    JOIN TASK T ON (U._id = T.user_id)
    JOIN task_type tt ON tt.id = t.task_type_id
WHERE  tt.id = 1 AND T.CREATION_DATE = '2018-01-01'
GROUP BY T.user_id
ORDER BY COUNT(*) ASC;

这将导致:

也就是说,用户任务类型表没有被加入,因为它似乎没有作用,并且计数与预期的一样