英文:
Get the details of latest transaction details of all employees based on selected transaction type
问题
$transaction_types = ['Add', 'Update', 'Read'];
$output = '';
$query = "SELECT * FROM transactions
INNER JOIN employees ON employees.employee_id = transactions.employee_id
WHERE transactions.transaction_type IN ('" . implode("','", $transaction_types) . "')
GROUP BY transactions.employee_id
ORDER BY DATE(transactions.transaction_date) DESC
";
$stmt = $connect->prepare($query);
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
$output .= '<table>
<thead>
<tr>
<th>Transaction ID</th>
<th>Employee Name</th>
<th>Transaction Date</th>
<th>Transaction Type</th>
<th>Remarks</th>
</tr>
</thead>
<tbody>';
$result = $stmt->fetchAll();
foreach ($result as $row) {
$output .= '<tr>
<td>' . $row->transaction_id . '</td>
<td>' . $row->employee_name . '</td>
<td>' . $row->transaction_date . '</td>
<td>' . $row->transaction_type . '</td>
<td>' . $row->remarks . '</td>
</tr>';
}
$output .= '</tbody></table>';
}
}
echo $output;
英文:
everyone. Some one who can help me to get the latest transaction details of employees based on selected transaction type.
Below is the sample tables.
employees Table:
| employee_id | employee_name |
|---|---|
| 1 | John Doe |
| 2 | Jane Doe |
| 3 | Teri Dactyl |
| 4 | Allie Grater |
transactions Table:
| transaction_id | employee_id | transaction_date | transaction_type | remarks |
|---|---|---|---|---|
| 1 | 1 | 2021-03-28 | Add | test 1 |
| 2 | 3 | 2022-09-07 | Add | test 2 |
| 3 | 2 | 2019-08-01 | Add | test 3 |
| 4 | 4 | 2023-06-05 | Read | test 4 |
| 5 | 4 | 2023-05-12 | Add | test 5 |
| 6 | 2 | 2020-02-01 | Read | test 6 |
| 7 | 3 | 2022-11-15 | Update | test 7 |
| 8 | 1 | 2020-06-14 | Add | test 8 |
| 9 | 1 | 2020-01-14 | Update | test 9 |
| 10 | 2 | 2023-12-31 | Delete | test 10 |
Then now, I want to get list of employees and their latest transaction details where transaction_type is either [Add, Update, Read]
Below is my desired output:
| Transaction ID | Employee Name | Transaction Date | Transaction Type | Remarks |
|---|---|---|---|---|
| 4 | Allie Grater | 2023-06-05 | Read | test 4 |
| 7 | Teri Dactyl | 2022-11-15 | Update | test 7 |
| 1 | John Doe | 2021-03-28 | Add | test 1 |
As you can see, Jane Doe is not in the list, because her last transaction type is Delete which is not in the selected transaction type [Add, Update, Read]
Below is my Code:
<?php
$transaction_types = ['Add', 'Update', 'Read'];
$output = '';
$query = "SELECT * FROM transactions
INNER JOIN employees ON employees.employee_id = transactions.employee_id
WHERE transactions.transaction_type IN ('".implode("', '", $transaction_types)."')
GROUP BY transactions.employee_id
ORDER BY DATE(transactions.transaction_date) DESC
";
$stmt = $connect->prepare($query);
if ($stmt->execute()) {
if ($stmt->rowCount() > 0) {
$output .= '<table>
<thead>
<tr>
<th>Transaction ID</th>
<th>Employee Name</th>
<th>Transaction Date</th>
<th>Transaction Type</th>
<th>Remarks</th>
</tr>
</thead>
<tbody>';
$result = $stmt->fetchAll();
foreach ($result as $row) {
$output .= '<tr>
<td>'.$row->transaction_id.'</td>
<td>'.$row->employee_name.'</td>
<td>'.$row->transaction_date.'</td>
<td>'.$row->transaction_type.'</td>
<td>'.$row->remarks.'</td>
</tr>';
}
$output .= '</tbody></table>';
}
}
echo $output;
?>
答案1
得分: 1
你首先需要从结果集中删除具有 Delete 的用户，然后使用 row_number() 窗口函数与 CTE 选择最大日期。
WITH cte
AS (
SELECT t.transaction_id AS transaction_id
,t.transaction_date AS transaction_date
,row_number() OVER (
PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
) AS rn
,e.employee_name AS employee_name
,t.transaction_type AS transaction_type
,t.remarks AS remarks
FROM employees e
JOIN transactions t ON t.employee_id = e.employee_id
WHERE e.employee_name NOT IN (
SELECT e.employee_name
FROM employees e
JOIN transactions t ON t.employee_id = e.employee_id
WHERE transaction_type = 'Delete'
)
)
SELECT cte.transaction_id
,cte.transaction_date
,cte.employee_name
,cte.transaction_type
,cte.remarks
FROM cte
WHERE rn = 1;
demo 这里
英文:
you first have to remove the users with Delete from your results set and then use the row_number() window function with CTE to select the max dates
WITH cte
AS (
SELECT t.transaction_id AS transaction_id
,t.transaction_date AS transaction_date
,row_number() OVER (
PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
) AS rn
,e.employee_name AS employee_name
,t.transaction_type AS transaction_type
,t.remarks AS remarks
FROM employees e
JOIN transactions t ON t.employee_id = e.employee_id
WHERE e.employee_name NOT IN (
SELECT e.employee_name
FROM employees e
JOIN transactions t ON t.employee_id = e.employee_id
WHERE transaction_type = 'Delete'
)
)
SELECT cte.transaction_id
,cte.transaction_date
,cte.employee_name
,cte.transaction_type
,cte.remarks
FROM cte
WHERE rn = 1;
demo here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论