英文:
Get only the latest status by MAX function for each Application using WHERE clausule instead of ORDER BY
问题
I would like to obtain the same result of query using WHERE instead of GROUP BY. I have applications and each application has statuses with date. I need to return applications with their latest status date.
我想要通过使用 WHERE 而不是 GROUP BY 来获得相同的查询结果。我有应用程序，每个应用程序都有日期的状态。我需要返回带有其最新状态日期的应用程序。
My query with ORDER BY: (the result is correct)
我的查询使用 ORDER BY：（结果是正确的）
select a.guid, MAX(s.request_time) as last_request_time
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
group by a.guid;
result:
结果：
guid |last_request_time |
------------------------------------|-------------------|
330d32d5-2496-4cce-9d11-29e59333766a|2020-07-22 13:06:25|
5b46cda9-b954-4d8b-82cf-f1d83f77b175|2020-07-22 13:07:25|
34071189-ab3d-47ff-9ee1-aca6fa806bc9|2020-08-03 10:45:15|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|
ff98695f-e1a8-439e-8a6c-7991348b6cd7|2020-07-29 14:38:18|
I try this but it return me only the one application with the latest status date:
我尝试这个，但它只返回了具有最新状态日期的一个应用程序：
select a.guid, s.request_time
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
where request_time = (select MAX(applications_status.request_time) from applications_status );
result:
结果：
guid |request_time |
------------------------------------|-------------------|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|
Applications table
应用程序表
CREATE TABLE public.applications (
id bigserial NOT NULL,
guid varchar(40) NOT NULL,
"name" varchar(60) NOT NULL,
latest_status_date timestamp NULL,
latest_status bool NOT NULL,
id_production bigserial NOT NULL,
CONSTRAINT applications_guid_key UNIQUE (guid),
CONSTRAINT applications_pkey PRIMARY KEY (id),
CONSTRAINT uk_gtuqgycxk8ulkir3io2p49yn1 UNIQUE (guid),
CONSTRAINT fkaid_prod FOREIGN KEY (id_production) REFERENCES productions(id) ON DELETE CASCADE
);
Applications_status table
应用程序状态表
CREATE TABLE public.applications_status (
id bigserial NOT NULL,
status bool NOT NULL,
guid_application varchar(50) NOT NULL,
log varchar(200) NULL,
request_time timestamp NOT NULL,
CONSTRAINT status_pkey PRIMARY KEY (id),
CONSTRAINT fkaguid_application FOREIGN KEY (guid_application) REFERENCES applications(guid) ON UPDATE CASCADE ON DELETE CASCADE
);
Why I need this way? I try to return Applications with their latest status in Spring Boot using @Where annotation in @OneToMany relation in Entity.
为什么我需要这种方式？我尝试在Spring Boot中使用@Where注解在Entity中的@OneToMany关系中返回带有其最新状态的应用程序。
@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;
I also try to use @BatchSize(size = 1) but it doesn't work.
我还尝试使用 @BatchSize(size = 1) 但它不起作用。
英文:
I would like to obtain the same result of query using WHERE instead of GROUP BY. I have applications and each application has statuses with date. I need to return applications with their latest status date.
My query with ORDER BY: (the result is correct)
select a.guid, MAX(s.request_time) as last_request_time
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
group by a.guid ;
result:
guid |last_request_time |
------------------------------------|-------------------|
330d32d5-2496-4cce-9d11-29e59333766a|2020-07-22 13:06:25|
5b46cda9-b954-4d8b-82cf-f1d83f77b175|2020-07-22 13:07:25|
34071189-ab3d-47ff-9ee1-aca6fa806bc9|2020-08-03 10:45:15|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|
ff98695f-e1a8-439e-8a6c-7991348b6cd7|2020-07-29 14:38:18|
I try this but it return me only the one application with latest status date:
select a.guid, s.request_time
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
where request_time = (select MAX(applications_status.request_time) from applications_status );
result:
guid |request_time |
------------------------------------|-------------------|
a8961058-a6ee-4d71-b325-9aca83b22237|2020-08-03 10:45:39|
Applications table
CREATE TABLE public.applications (
id bigserial NOT NULL,
guid varchar(40) NOT NULL,
"name" varchar(60) NOT NULL,
latest_status_date timestamp NULL,
latest_status bool NOT NULL,
id_production bigserial NOT NULL,
CONSTRAINT applications_guid_key UNIQUE (guid),
CONSTRAINT applications_pkey PRIMARY KEY (id),
CONSTRAINT uk_gtuqgycxk8ulkir3io2p49yn1 UNIQUE (guid),
CONSTRAINT fkaid_prod FOREIGN KEY (id_production) REFERENCES productions(id) ON DELETE CASCADE
);
Applications_status table
CREATE TABLE public.applications_status (
id bigserial NOT NULL,
status bool NOT NULL,
guid_application varchar(50) NOT NULL,
log varchar(200) NULL,
request_time timestamp NOT NULL,
CONSTRAINT status_pkey PRIMARY KEY (id),
CONSTRAINT fkaguid_application FOREIGN KEY (guid_application) REFERENCES applications(guid) ON UPDATE CASCADE ON DELETE CASCADE
);
Why I need this way? I try to return Applications with their latest status in Spring Boot using @Where annotation in @OneToMany relation in Entity.
@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;
I also try to use @BatchSize(size = 1) but it doesn't work.
答案1
得分: 0
The question is tagged both "sql" and "postgres," so this is a Postgres solution.
Use distinct on:
select distinct on (a.guid) a.*, s.*
from public.applications_status s inner join
public.applications a
on a.guid = s.guid_application
order by a.guid, s.request_time desc;
distinct on is a very handy Postgres extension that returns one row (the "first" row) for each group in the parentheses. The particular row is based on the order by.
英文:
The question is tagged both "sql" and "postgres", so this is a Postgres solution.
Use distinct on:
select distinct on (a.guid) a.*, s.*
from public.applications_status s inner join
public.applications a
on a.guid = s.guid_application
order by a.guid, s.request_time desc;
distinct on is a very handy Postgres extension that returns one row (the "first" row) for each group in the parentheses. The particular row is based on the order by.
答案2
得分: 0
SQL 查询：
选择 a.guid，s.request_time
从公共应用程序状态 s 内联加入公共应用程序 a on a.guid = s.guid_application
其中 request_time = （选择 MAX(applications_status.request_time)
从 applications_status
其中 applications_status.guid_application = s.guid_application）;
Spring-Boot：
@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status where guid_application = applications_status.guid_application )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;
英文:
Through trial and error, I found a solution:
SQL query:
select a.guid, s.request_time
from public.applications_status s inner join public.applications a on a.guid = s.guid_application
where request_time = (select MAX(applications_status.request_time)
from applications_status
where applications_status.guid_application = s.guid_application );
Spring-Boot:
@OneToMany(cascade = CascadeType.ALL, mappedBy = "application", fetch = LAZY)
@JsonManagedReference
@Where(clause = "request_time = (SELECT MAX(applications_status.request_time) FROM applications_status where guid_application = applications_status.guid_application )")
@OrderBy("requestTime DESC")
private List<ApplicationStatus> applicationStatuses;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论