GROUP BY在MySQL中优先考虑

Question

I have the following query.

$query_assignments = "SELECT * FROM tb_scheduler_assignments 
                      WHERE company_id = '".$company_id."' OR 
                      dept_id = '".$dept_id."' OR 
                      user_id = '".$user_id."' ORDER BY 
                      due_date GROUP BY purchase_id";

What I'd like is a single query solution that would keep the results for user_id over dept_id and dept_id over company_id.

For example:

• if the same purchase_id occurs for rows that were gotten via dept_id and user_id, then I only want the result for the user_id;
• if the same purchase_id occurs for rows that were gotten via company_id and user_id, then I only want the result for the user_id

Answer 1

First, you're interpolating variables in your SQL, which suggests you might be vulnerable to SQL injection. Just to make sure. PHP should offer prepared statements, or some escaping function.

Second, your SQL statement won't compile because you're using GROUP BY a but selecting * which includes at least three more columns.

Third, it sounds like you're misunderstanding SQL in thinking that it might, in a query such as you're trying to formulate (without UNION ALL), retrieve duplicate rows, i.e. the same row multiple times because it matches multiple criteria. This is not so.