天道酬勤,学无止境

right-join

MYSQL:限制每个 whereIn() 的行(MYSQL: Limiting rows per whereIn())

问题 用户表 ID 用户评论表 编号 | 用户 ID | 内容 | created_at 我有一个用户 ID 列表,我想获取每个用户 ID 的最新 3 条评论。 SELECT * FROM user_comments WHERE user_id IN (1, 2, 3, 4, 5) ORDER BY created_at DESC LIMIT 3; 这将从所有匹配的 ID 中获取最后 3 条评论,我想要每个ID 的最后 3 条评论。 1 个没有联合的查询首选。 我已经尝试直接加入表格,但我似乎无法做到正确。 **编辑:我不能依赖 id 列进行排序,它必须使用 date 列。 谢谢。 **我的最终解决方案 SELECT user_comments.* FROM user_comments LEFT OUTER JOIN user_comments user_comments_2 ON user_comments.post_id = user_comments_2.post_id AND user_comments.id < user_comments_2.id where user_comments.post_id in (x,x,x) GROUP BY user_comments.id HAVING COUNT(*) < 3 ORDER BY user_id, created_at

2022-01-14 11:51:19    分类:技术分享    mysql   greatest-n-per-group   where-in   right-join   sql-limit

MYSQL: Limiting rows per whereIn()

users Table id user_comments Table id | user_id | content | created_at I have a list of user IDs, and I want to grab the latest 3 comments for each user id. SELECT * FROM user_comments WHERE user_id IN (1, 2, 3, 4, 5) ORDER BY created_at DESC LIMIT 3; This will grab the last 3 comments from all matching IDs, I want the last 3 comments for each ID. 1 query without unions preferred. I have tried right joining the table on itself but I cant seem to get it right. ** Edit: I cannot rely on the id column for ordering, it must use the date column. Thanks. ** My Final Solution SELECT user_comments.*

2022-01-13 23:02:42    分类:问答    mysql   greatest-n-per-group   where-in   right-join   sql-limit

在 Django 中执行正确的连接(Performing a right join in django)

问题 这是我的模型 class Student: user = ForeignKey(User) department = IntegerField() semester = IntegerField() ... class Attendance: student = ForeignKey(Student) subject = ForeignKey(Subject) month = IntegerField() year = IntergerField() present = IntegerField() total = IntegerField() students = Student.objects.filter(semester=semester) 如何在Student和Attendance模型之间执行正确的连接,以便我可以获取包含所有students和出勤情况的查询集(如果学生存在,否则为空)? 文档提到了左连接但没有提到右连接。 回答1 更改表主题的左连接 queryset.query.alias_map['subject'].join_type = "RIGHT OUTER JOIN" 回答2 您可以使用这样的查询: queryset = Student.objects.all().select_related('attendance_set') student =

2021-11-29 00:41:08    分类:技术分享    django   orm   right-join

Performing a right join in django

Here are my models class Student: user = ForeignKey(User) department = IntegerField() semester = IntegerField() ... class Attendance: student = ForeignKey(Student) subject = ForeignKey(Subject) month = IntegerField() year = IntergerField() present = IntegerField() total = IntegerField() students = Student.objects.filter(semester=semester) How can I perform a right join between Student and Attendance models, so that I can get a queryset with all of the students and attendances` if exists for a student, else null? The documentation mentions left joins but not right joins.

2021-11-23 00:10:37    分类:问答    django   orm   right-join

如何制定查询以显示一个人参加的所有课程(How to formulate query to show all courses taken by a person)

问题 我在正确制定 MySQL 查询时遇到问题。 我尝试过的一切都没有给我需要的东西,或者给出一个语法错误。 我有三个表:Clients、Courses 和 CoursesForClients。 Clients 表只有一个人的基本坐标:ID、姓名、地址、电子邮件等。 +----------+-----------------------------+------+ | ClientID | Name | Address | etc. | +----------+-----------------------------+------+ | 10 | Joe Smith | 1 Main St. | ... | | 20 | Bob Smith | 2 Main St. | ... | | ... | ... ... | ... ... ... | ... | +----------+-----------------------------+------+ Courses 表存储课程名称及其 ID。 +----------+-----------------------+ | CourseID | Name | +----------+-----------------------+ | 100 | Intro. to Subject | | 200 | Intermediate

2021-11-09 10:24:15    分类:技术分享    mysql   group-by   left-join   right-join

How to formulate query to show all courses taken by a person

I'm having trouble formulating a MySQL query correctly. Everything I've tried doesn't give me what's needed, or gives a syntax error. I have three tables: Clients, Courses, and CoursesForClients. The Clients table just has basic coordinates for a person: ID, Name, Address, email, etc. +----------+-----------------------------+------+ | ClientID | Name | Address | etc. | +----------+-----------------------------+------+ | 10 | Joe Smith | 1 Main St. | ... | | 20 | Bob Smith | 2 Main St. | ... | | ... | ... ... | ... ... ... | ... | +----------+-----------------------------+------+ The Courses

2021-11-07 11:41:13    分类:问答    mysql   group-by   left-join   right-join

如何优化连接3个表的SQL查询(How to optimize the SQL query that joins 3 table)

问题 你好, 我有以下 SQL 查询需要大约 4 秒才能运行: select o.id, tu.status_type, m.upload_date from (select order_id, max(last_updated) as maxudate from tracking_update group by order_id) t inner join tracking_update tu on t.order_id=tu.order_id and t.maxudate=tu.last_updated right join fgw247.order o on t.order_id=o.id left join manifest m on o.manifest_id=m.id where (m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') 该查询连接以下 3 个表: 订单、清单和 Tracking_Update。 查询将返回满足以下条件的订单: 任何交货状态下 <30 天的订单订单 > 30 天且不是交货状态 如果该订单的最新 tracking_update

2021-09-30 04:21:33    分类:技术分享    mysql   join   inner-join   right-join

仅比较对象左侧或右侧(compare-object left or right side only)

问题 快速问题 有没有更好(即更有效/更简洁)的方法来做到这一点? compare-object $a $b | ?{$_.SideIndicator -eq '<='} 细节 Compare-Object提供参数-excludeDifferent和-includeEqual以允许您修改获得的结果。 使用两者会给你一个内部连接仅使用-includeEqual为您提供完整的外连接只使用-excludeDifferent是没有意义的; 因为默认情况下相等的项目被排除在外,所以它现在将排除所有内容。 -includeLeft 、 -excludeLeft或类似选项没有选项。 目前要进行右侧为空的左外连接(即参考对象中的项目不在差异对象中),我需要按照上面的代码手动过滤结果。 我错过了什么/有更好的方法吗? http://ss64.com/ps/compare-object.html 回答1 该 cmdlet 没有类似的选项,但是您可以创建一个过滤器(例如在您的个人资料中),然后使用它来过滤结果:类似于 filter leftside{ param( [Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)] [ValidateNotNullOrEmpty()] [PSCustomObject] $obj )

2021-09-18 11:44:01    分类:技术分享    powershell   left-join   diff   right-join   compareobject

How to optimize the SQL query that joins 3 table

Howdie do, I have the following SQL query which takes about 4 seconds to run: select o.id, tu.status_type, m.upload_date from (select order_id, max(last_updated) as maxudate from tracking_update group by order_id) t inner join tracking_update tu on t.order_id=tu.order_id and t.maxudate=tu.last_updated right join fgw247.order o on t.order_id=o.id left join manifest m on o.manifest_id=m.id where (m.upload_date >= '2015-12-12 00:00:00') or (m.upload_date <='2015-12-12 00:00:00' and tu.status_type != 'D' and tu.status_type != 'XD') The query joins the following 3 tables: Order, Manifest and

2021-09-05 14:19:25    分类:问答    mysql   join   inner-join   right-join

compare-object left or right side only

Quick Question Is there a better (i.e. more efficient / more concise) way to do this? compare-object $a $b | ?{$_.SideIndicator -eq '<='} Detail Compare-Object gives paramenters -excludeDifferent and -includeEqual to allow you to amend which results you get. using both gives you an inner join using just -includeEqual gives you a full outer join using just -excludeDifferent is pointless; as by default equal items are excluded, so it will now exclude everything. There are no options for -includeLeft, -excludeLeft or similar. Currently to do a left outer join where the right side is null (i.e

2021-07-12 05:13:01    分类:问答    powershell   left-join   diff   right-join   compareobject