该应用程序为贷款分配系统。我有用户列表。根据技能和任务状态，我需要获取合格的用户列表。有三个与用户相关的表格-'user_info'，'user_skill'和'assignment_details'。一个与贷款相关的表，用于说明已分配了哪些用户贷款，即'loan_mapping'表。要获取符合条件的列表，我有两个主要条件。
- user_skill表中的skill_id应该为156。
- 每个用户的Assignment_status应该为“打开” OR ，assignment_status =“ Limited”以及“ assignment_limited_threshold” VALUE应该大于分配给该用户的贷款总数。 （换句话说，每个用户的总贷款分配计数不应超过这个assignment_limited_threshold值）
user_id是与用户相关的表的联接条件。
下表定义如下：
CREATE TABLE public.user_info(
user_id integer NOT NULL DEFAULT
nextval('lsa_user_info_user_id_seq'::regclass),email_id character varying(255),user_name character varying(255),lob_id integer NOT NULL,status_id integer,CONSTRAINT user_info_pkey PRIMARY KEY (user_id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.lsa_user_info
OWNER TO postgres;
CREATE TABLE public.user_skill
(
user_id integer NOT NULL,skill_id integer NOT NULL,CONSTRAINT user_skill_pkey PRIMARY KEY (user_id,skill_id),CONSTRAINT fk_user_skill_skill_id FOREIGN KEY (skill_id)
REFERENCES public.skill (skill_id) MATCH SIMPLE
ON UPDATE NO actION ON DELETE NO actION,CONSTRAINT fk_user_skill_user_id FOREIGN KEY (user_id)
REFERENCES public.user_info (user_id) MATCH SIMPLE
ON UPDATE NO actION ON DELETE NO actION )
WITH (
OIDS=FALSE
);
ALTER TABLE public.user_skill
OWNER TO postgres;
CREATE TABLE public.assignment_details
(
user_id integer NOT NULL,role_id integer NOT NULL,assignment_status character varying,assignment_limited_threshold integer,CONSTRAINT lsa_assignment_details_pkey PRIMARY KEY (user_id,role_id,lob_id )
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.assignment_details
OWNER TO postgres;
CREATE TABLE public.loan_mapping
(
loan_number bigint NOT NULL,spoc_id integer NOT NULL,loan_spoc_map_id integer NOT NULL DEFAULT nextval
('lsa_loan_spoc_mapping_loan_spoc_map_id_seq'::regclass),lob_id integer,CONSTRAINT loan_spoc_mapping_pk PRIMARY KEY (loan_spoc_map_id),CONSTRAINT fk_loan_spoc_loan_number FOREIGN KEY (loan_number)
REFERENCES public.loan_details (loan_number) MATCH SIMPLE
ON UPDATE NO actION ON DELETE NO actION,CONSTRAINT fk_loan_spoc_spoc_id FOREIGN KEY (spoc_id)
REFERENCES public.user_info (user_id) MATCH SIMPLE
ON UPDATE NO actION ON DELETE NO actION
)
WITH (
OIDS=FALSE
);
ALTER TABLE public.loan_mapping
OWNER TO postgres;
例如表记录如下，
-----user_info ----
user_id | user_name | lob_id | email_id | status_id
111 abc 5 abc@gmail.com 2
222 def 5 def@gmail.com 6
333 ghi 5 ghi@gmail.com 2
444 jkl 5 jkl@hotmail.com 2
----------- user_skill ----------------
user_id | skill_id
111 156
222 156
333 156
444 156
----- assignment_details ----------------
user_id | role_id | lob_id | Assignment_status | Assignment_limited_threshold
111 11 5 Open
222 11 5 No
333 11 5 Limited 2
444 11 5 Limited 10
------------loan_mapping-----------------
loan_number | spoc_id | loan_spoc_map_id | lob_id | appName
222333 111 23 5 SS
222444 333 24 5 SS
222555 333 25 5 SS
333452 444 26 5 SS
777445 444 27 5 SS
预期输出为：
user_id
---------
111
444
我尝试的查询未给出预期的结果
select ui.user_id from user_info ui
inner join user_skill lus on lus.skill_id = 156 and lus.user_id =
ui.user_id
inner join assignment_details ad on
ui.lob_id=ad.lob_id and ui.user_id=ad.user_id and
ui.lob_id=5 and ui.status_id=2 where ad.lob_id=5 and
ad.role_id=11 and ad.assignment_status='Open' OR
( ad.assignment_status='Limited'
and ad.assignment_limited_threshold > ( select count(loan_number)
from loan_mapping where lob_id=5 and
app_name ='SS'))
您能根据我的预期输出编写查询吗？预先感谢