如何在一个查询中获取表数据?我有下面的表格:
标签名称: calorieTracker
Creat Table calorieTracker(c_id serial NOT NULL PRIMARY KEY, caloriesConsumption jsonb);
INSERT INTO public."calorieTracker" ("caloriesConsumption")
VALUES ('[{"C_id":"1",,"calorie":88,"date":"19/08/2020"},{"C_id":2,"date":"19/08/2020","calorie":87}]');表名: watertracker
create table watertracker(wt_id serial not null primary key, wt_date varchar, wt_goal float,wt_cid int);
INSERT INTO public.watertracker (wt_id,wt_date,wt_goal,wt_cid)
VALUES (2,'2020-08-19',5.5,2);我在这里寻找我想写的查询,其中日期是19/08/2020(在calorieTracker表和水跟踪器表)和wt_cid是2(水跟踪器表)和c_id是2(calorieTracker表),然后返回数据。
发布于 2020-08-19 21:45:38
因为你没有提到你想要什么输出,所以我假设你想要来自caloriesConsumption的符合问题中提到的条件的JSON对象:
基于上面的假设,尝试这个查询:
with cte as (
select
c_id,
jsonb_array_elements("caloriesConsumption") "data"
from "calorieTracker"
)
select
t1.*
from cte t1 inner join watertracker t2
on t2.wt_cid=cast(t1.data->>'c_id' as int)
and t2.wt_date=t1.data->>'date' 如果您想要watertracker的结果,那么只需用t2.*替换t1.*即可。
https://stackoverflow.com/questions/63480845
复制相似问题