获取所有员工的最新交易类型详细信息。

huangapple go评论85阅读模式
英文:

Get the details of latest transaction details of all employees based on selected transaction type

问题

  1. $transaction_types = ['Add', 'Update', 'Read'];
  2. $output = '';
  3. $query = "SELECT * FROM transactions
  4. INNER JOIN employees ON employees.employee_id = transactions.employee_id
  5. WHERE transactions.transaction_type IN ('" . implode("','", $transaction_types) . "')
  6. GROUP BY transactions.employee_id
  7. ORDER BY DATE(transactions.transaction_date) DESC
  8. ";
  9. $stmt = $connect->prepare($query);
  10. if ($stmt->execute()) {
  11. if ($stmt->rowCount() > 0) {
  12. $output .= '<table>
  13. <thead>
  14. <tr>
  15. <th>Transaction ID</th>
  16. <th>Employee Name</th>
  17. <th>Transaction Date</th>
  18. <th>Transaction Type</th>
  19. <th>Remarks</th>
  20. </tr>
  21. </thead>
  22. <tbody>';
  23. $result = $stmt->fetchAll();
  24. foreach ($result as $row) {
  25. $output .= '<tr>
  26. <td>' . $row->transaction_id . '</td>
  27. <td>' . $row->employee_name . '</td>
  28. <td>' . $row->transaction_date . '</td>
  29. <td>' . $row->transaction_type . '</td>
  30. <td>' . $row->remarks . '</td>
  31. </tr>';
  32. }
  33. $output .= '</tbody></table>';
  34. }
  35. }
  36. 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:

  1. &lt;?php
  2. $transaction_types = [&#39;Add&#39;, &#39;Update&#39;, &#39;Read&#39;];
  3. $output = &#39;&#39;;
  4. $query = &quot;SELECT * FROM transactions
  5. INNER JOIN employees ON employees.employee_id = transactions.employee_id
  6. WHERE transactions.transaction_type IN (&#39;&quot;.implode(&quot;&#39;, &#39;&quot;, $transaction_types).&quot;&#39;)
  7. GROUP BY transactions.employee_id
  8. ORDER BY DATE(transactions.transaction_date) DESC
  9. &quot;;
  10. $stmt = $connect-&gt;prepare($query);
  11. if ($stmt-&gt;execute()) {
  12. if ($stmt-&gt;rowCount() &gt; 0) {
  13. $output .= &#39;&lt;table&gt;
  14. &lt;thead&gt;
  15. &lt;tr&gt;
  16. &lt;th&gt;Transaction ID&lt;/th&gt;
  17. &lt;th&gt;Employee Name&lt;/th&gt;
  18. &lt;th&gt;Transaction Date&lt;/th&gt;
  19. &lt;th&gt;Transaction Type&lt;/th&gt;
  20. &lt;th&gt;Remarks&lt;/th&gt;
  21. &lt;/tr&gt;
  22. &lt;/thead&gt;
  23. &lt;tbody&gt;&#39;;
  24. $result = $stmt-&gt;fetchAll();
  25. foreach ($result as $row) {
  26. $output .= &#39;&lt;tr&gt;
  27. &lt;td&gt;&#39;.$row-&gt;transaction_id.&#39;&lt;/td&gt;
  28. &lt;td&gt;&#39;.$row-&gt;employee_name.&#39;&lt;/td&gt;
  29. &lt;td&gt;&#39;.$row-&gt;transaction_date.&#39;&lt;/td&gt;
  30. &lt;td&gt;&#39;.$row-&gt;transaction_type.&#39;&lt;/td&gt;
  31. &lt;td&gt;&#39;.$row-&gt;remarks.&#39;&lt;/td&gt;
  32. &lt;/tr&gt;&#39;;
  33. }
  34. $output .= &#39;&lt;/tbody&gt;&lt;/table&gt;&#39;;
  35. }
  36. }
  37. echo $output;
  38. ?&gt;

答案1

得分: 1

你首先需要从结果集中删除具有 Delete 的用户,然后使用 row_number() 窗口函数与 CTE 选择最大日期。

  1. WITH cte
  2. AS (
  3. SELECT t.transaction_id AS transaction_id
  4. ,t.transaction_date AS transaction_date
  5. ,row_number() OVER (
  6. PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
  7. ) AS rn
  8. ,e.employee_name AS employee_name
  9. ,t.transaction_type AS transaction_type
  10. ,t.remarks AS remarks
  11. FROM employees e
  12. JOIN transactions t ON t.employee_id = e.employee_id
  13. WHERE e.employee_name NOT IN (
  14. SELECT e.employee_name
  15. FROM employees e
  16. JOIN transactions t ON t.employee_id = e.employee_id
  17. WHERE transaction_type = 'Delete'
  18. )
  19. )
  20. SELECT cte.transaction_id
  21. ,cte.transaction_date
  22. ,cte.employee_name
  23. ,cte.transaction_type
  24. ,cte.remarks
  25. FROM cte
  26. 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

  1. WITH cte
  2. AS (
  3. SELECT t.transaction_id AS transaction_id
  4. ,t.transaction_date AS transaction_date
  5. ,row_number() OVER (
  6. PARTITION BY e.employee_name ORDER BY t.transaction_date DESC
  7. ) AS rn
  8. ,e.employee_name AS employee_name
  9. ,t.transaction_type AS transaction_type
  10. ,t.remarks AS remarks
  11. FROM employees e
  12. JOIN transactions t ON t.employee_id = e.employee_id
  13. WHERE e.employee_name NOT IN (
  14. SELECT e.employee_name
  15. FROM employees e
  16. JOIN transactions t ON t.employee_id = e.employee_id
  17. WHERE transaction_type = &#39;Delete&#39;
  18. )
  19. )
  20. SELECT cte.transaction_id
  21. ,cte.transaction_date
  22. ,cte.employee_name
  23. ,cte.transaction_type
  24. ,cte.remarks
  25. FROM cte
  26. WHERE rn = 1;

demo here

huangapple
  • 本文由 发表于 2023年6月5日 22:39:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/76407560.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定