时间戳

首页 > 解决方案 > SQL如何获取按职位过滤的用户和部门列表？

sql - SQL如何获取按职位过滤的用户和部门列表？

问题描述

请帮助我，我如何获得按职位过滤的部门用户列表？

CREATE TABLE COMPANY 
(
    ID INT GENERATED BY DEFAULT AS IDENTITY,
    NAME VARCHAR(255),
    PRIMARY KEY (ID),
    UNIQUE KEY COMPANY_NAME (NAME)
);

CREATE TABLE USER
(
    ID INT GENERATED BY DEFAULT AS IDENTITY,
    NAME VARCHAR(255),
    LASTNAME VARCHAR(255),
    DATE_OF_BIRTH DATE ,
    PRIMARY KEY (ID),
);

CREATE TABLE POSITION
(
    ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    POSITION VARCHAR (50),
    USERID INT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (USERID) REFERENCES USER(ID)
);

CREATE TABLE DEPARTMENT
(
    ID INT GENERATED BY DEFAULT AS IDENTITY,
    USER_ID INT NOT NULL,
    COMPANY_ID INT NOT NULL,
    DEPARTMENT_CATEGORY VARCHAR(50),
    PRIMARY KEY (ID),
    FOREIGN KEY (USER_ID) REFERENCES USER(ID),
    FOREIGN KEY (COMPANY_ID) REFERENCES COMPANY(ID)
);

CREATE TABLE CATEGORY 
(
    ID INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    NAME VARCHAR (50),
    PRIMARY KEY (ID)
);

CREATE TABLE DEPARTMENT_CATEGORY
(
     DEPARTMENT_ID INT NOT NULL,
     CATEGORY_ID INT NOT NULL,
     FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENT(ID),
     FOREIGN KEY (CATEGORY_ID) REFERENCES CATEGORY(ID)
);

请帮我！！

标签： sqlpostgresql

解决方案

看起来您想显示包含所需数据的部门和职位表的结果，但要具体说明您的过滤器（职位），因为您需要在 where 子句中提供它

select user_ID, department
from department as a
join user as b
on a. user_id = b. user_id
where position = 'put the specific position you wanted to be filtered here'

但是您的两个 userID 列的写法不同，因为（User_id 或 USERID）正确，当您执行任务时也不会得到失败结果

推荐阅读