having可以和and连用嘛MySQL_如何在mysql中的HAVING子句中使用AND條件？

I have 4 tables in a database, namely employee_details_table (emp_no, emp_name, dept_no), department_table (dept_no, dept_name), grade_table (grade_id, grade_name) and relation_table (emp_no, grade_id).

我在數據庫中有4個表,即employee_details_table(emp_no,emp_name,dept_no),department_table(dept_no,dept_name),grade_table(grade_id,grade_name)和relation_table(emp_no,grade_id)。

The scenario of database structure is: I need to get the emp_name and dept_name of all employees whose (grade_id = 1 AND grade_id = 2 AND grade_id = 3), i.e. employees with all the 3 grade_id. (Note: There can be employees without grade_id.)

數據庫結構的場景是:我需要獲得所有員工的emp_name和dept_name(grade_id = 1 AND grade_id = 2 AND grade_id = 3),即擁有所有3 grade_id的員工。 (注意:可能有沒有grade_id的員工。)

My MySQL query is as follows:

我的MySQL查詢如下:

SELECT *, GROUP_CONCAT( grade_id ) AS gradeid

FROM employee_details_table t1

LEFT JOIN department_table t2

USING ( dept_no )

LEFT JOIN relation_table t3

USING ( emp_no )

LEFT JOIN grade_table t4

USING ( grade_id )

GROUP BY t1.emp_no

HAVING grade_id = '1' OR grade_id = '2' OR grade_id = '3';

For the above query I get all the rows as output. I also tried using AND instead of OR but I don't get any output. I have used WHERE IN (1,2,3). I get the rows with 1 OR 2 OR 3 as grade_id. WHERE IN(1 AND 2 AND 3) does not work

對於上面的查詢,我將所有行作為輸出。我也嘗試使用AND而不是OR,但我沒有得到任何輸出。我使用過WHERE IN(1,2,3)。我得到1 OR 2 OR 3的行作為grade_id。在(1和2和3)中的哪個不起作用

Please help me correct the query.

請幫我糾正查詢。

1 个解决方案

#1

2

Should do the trick:

應該做的訣竅:

SELECT *, GROUP_CONCAT( grade_id ) AS gradeid

FROM employee_details_table t1

LEFT JOIN department_table t2

USING ( dept_no )

LEFT JOIN relation_table t3

USING ( emp_no )

LEFT JOIN grade_table t4

USING ( grade_id )

WHERE grade_id IN ('1','2','3')

GROUP BY t1.emp_no

HAVING COUNT(DISTINCT grade_id) = 3;

First, take all employees that have at least one grade within the interesting ones (1, 2 and 3), then keep only the employees that have exactly three different grades, which indirectly means they have all three of them. If you want your group_concat to have all grades (including those that are not 1, 2 or 3), you'll need to mess around with a subquery, or do some nice magic with an "IF" statement in the HAVING clause. I can give a sample of this if you like.

首先,將所有至少有一個等級的員工帶到有趣的(1,2和3),然后只保留具有三個不同等級的員工,這間接意味着他們擁有所有三個等級。如果你希望你的group_concat擁有所有等級(包括那些不是1,2或3的等級),你需要搞亂一個子查詢,或者在HAVING子句中使用“IF”語句做一些好的魔術。如果你願意,我可以給你一個樣本。