sqlite - SQLite 连接多个表,按更少的 COUNT 排序
问题描述
我有这个数据库结构:
我需要显示users
可以Task
在某一特定日期执行确定类型的结果,首先显示分配给那一天的结果较少Tasks
(或没有任何结果)。Task
例如:
User
ATask
当天分配了 1 个类型 1。User
BTask
当天分配了 3 个类型 1。User
Task
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;
仍然有问题,因为如果一个User
分配了 1 个任务,而另一个User
没有分配,则仍然首先User
显示第一个任务。
我会很感激任何帮助。谢谢。
解决方案
首先,提供的模式不反映正在使用的查询:-
- 任务表中没有creation_date列。
- 用户表的id列应该是_id。
其次,由于WHERE被编码两次,您提供的查询将导致语法错误。
您对存在哪些数据的描述不清楚,例如您说:-
用户 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;
这导致: -
- 即 user_task_type 表尚未加入,因为它似乎没有任何用途,并且计数符合预期
推荐阅读
- aws-lambda - AWS S3 存储桶 - 使用 Python lamda 将所有 xmls 文件从一个 S3 存储桶移动到另一个 S3 存储桶
- api - Laravel 5 - Web 和 API 路由冲突
- sql - 无效的数据类型 PL SQL (Oracle)
- javascript - 当来自对象的数据发生更改而不重新加载页面时自动更新页面的一部分
- javascript - 在 JavaScript 中的方法中添加额外的括号?
- docker - 无法连接到 docker
- javascript - Get element from object (array) by its class
- firebase - 如何在 React Native 中从 firebase 实时数据库中获取当前用户数据
- ruby-on-rails - 修复 'Gem::Requirement::BadRequirementError: Illformed requirements [">=1 <2"]' 错误
- react-native - 当我们的应用程序已经在后台由于诸如 SignIn with Google 之类的提示时如何处理应用程序状态更改