英文:
How to order the result of a SQL query (using INTERSECT)?
问题
我有2个表，users 和 Q。
我想选择一些特定的 users（具有 user_level = N），这些用户还必须在第二个表 Q 中满足条件 Q_test = -1 和 Q_dummy 不为空。
以下的查询可以正常工作（尽管可能不是最优解）：
SELECT * FROM `users` WHERE `user_id` IN (
SELECT `user_id` FROM `users` WHERE `user_level` = 'N'
INTERSECT
SELECT `Q_id` FROM `Q` WHERE `Q_test` = '-1' AND `Q_dummy` != ''
);
现在我想知道如何按照 Q_submission_date 对结果进行排序。我尝试使用 AS 操作符但没有成功...
以下是表的结构：
表 users：
CREATE TABLE `users` (
`user_id` int NOT NULL,
`user_level` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
表 Q：
CREATE TABLE `Q` (
`Q_id` int NOT NULL,
`Q_test` varchar(255) DEFAULT NULL,
`Q_dummy` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
基本上，Q_id 与 user_id 是相同的：
ALTER TABLE `Q`
ADD CONSTRAINT `MY_C` FOREIGN KEY (`Q_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
英文:
I have 2 tables users and Q.
I would like to select some specific users (having
user_level= N ) that also have Q_test = -1 AND Q_dummy !='' in a second table Q.
The following works nicely (albeit not optimal I guess).
SELECT * from `users` WHERE `user_id` In (SELECT `user_id` FROM `users` WHERE `user_level`= 'N' INTERSECT SELECT `Q_id` FROM `Q` WHERE `Q_test` = '-1' AND `Q_dummy` !='');
I am now wondering how to order the result by Q_submission_date.
I tried to use the As operator without any success ....
TABLES
CREATE TABLE `users` (
`user_id` int NOT NULL,
`user_level` varchar(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT 'N',
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `Q` (
`Q_id` int NOT NULL,
`Q_test` varchar(255) DEFAULT NULL
`Q_dummy` varchar(255) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
And basically Q_id is identical to user_id as :
ALTER TABLE `Q`
ADD CONSTRAINT `MY_C` FOREIGN KEY (`Q_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
答案1
得分: 1
只需将以下内容添加到您现有的查询中：
ORDER BY (SELECT q_dummy FROM q
WHERE q.q_id = users.user_id);
英文:
Just add
ORDER BY (SELECT q_dummy FROM q
WHERE q.q_id = users.user_id);
To your existing query.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论