如何在选择子句中使用 join in from 子句执行 Postgresql 子查询

Question

我有一个表一个用户和其他routine_streak我必须使用user_id从另一个表中获取用户名我想使用join从用户表中获取用户名，routine_streak我在postgresql查询中user_id...

这是我的查询：

select date(datetime) as dates,
        user_id,
        sum(CASE When status='completed' Then 1 Else 0 End ) as completed,
        sum(CASE When status='incompleted' Then 1 Else 0 End ) as incompleted,
        sum(CASE When status!='' Then 1 Else 0 End ) as total 
from routine_streak 
where user_id ='"+user_id+"' 
and datetime  between '"+start+"' and '"+end+"' 
group by user_id,date(datetime)

输出应：

user_id:11,
user name:abc,
dates: 2019-01-30,
completed: 1
incompleted: 1,
total: 2

表名：routine_streak

CREATE TABLE IF NOT EXISTS routine_streak(
    id integer DEFAULT nextval('routine_streak_id_seq'::regclass) NOT NULL,
    user_id integer NOT NULL,
    wayid integer NOT NULL,
    status character varying(50)  DEFAULT NULL::character varying NULL,
    day character varying(50)  DEFAULT NULL::character varying NULL,
    streak character varying(50)  DEFAULT NULL::character varying NULL,
    datetime timestamp without time zone NULL,
    CONSTRAINT "routine_streak_pkey" PRIMARY KEY (id)
 );
COMMENT ON TABLE public.routine_streak IS 'null';

第二个表：用户

CREATE TABLE IF NOT EXISTS user(
    id integer DEFAULT nextval('user_id_seq'::regclass) NOT NULL,
    cognito_identity_id character varying NOT NULL,
    username character varying NOT NULL,
    email character varying NULL,
    phone_number character varying NULL,
    name character varying NULL,
    password character varying(15)  NULL,
    status character(50)  DEFAULT NULL::bpchar NULL,
    CONSTRAINT "profile_pkey" PRIMARY KEY (id)
 );

Answer 1

尝试以下查询：-

select date(rs.datetime) as dates,
        rs.user_id,
        us.username,
        sum(CASE When rs.status='completed' Then 1 Else 0 End ) as completed,
        sum(CASE When rs.status='incompleted' Then 1 Else 0 End ) as incompleted,
        sum(CASE When rs.status!='' Then 1 Else 0 End ) as total 
from routine_streak rs
join "user" us on rs.user_id = us.id
where rs.user_id ='"+user_id+"' 
and rs.datetime  between '"+start+"' and '"+end+"' 
group by rs.user_id,us.username,date(rs.datetime)