MYSQLJOINING兩個表顯示重復ID的問題

我正在嘗試了解有關 mysql 資料庫的更多資訊，但遇到了一個問題。我有兩張桌子

消耗品

 ------------ ----------- ----------------------- ------ ------------------- ------ 
| dod        | item_code | item_description      | dept | quantity_received | unit |
 ------------ ----------- ----------------------- ------ ------------------- ------ 
| 2021-12-16 | Jell001   | Petroleum Jelly       | HBT  |                35 | Pcs  |
| 2021-12-16 | ELM001    | Consumer Control Unit | EWM  |                15 | Pcs  |
| 2021-12-17 | ELM002    | D3210 Contactor       | EWM  |                23 | Pcs  |
| 2021-12-17 | ICT001    | Carburator            | ICT  |                23 | Pcs  |
| 2021-12-17 | ICT001    | Carburator            | ICT  |                23 | Pcs  |
| 2021-12-18 | ELM001    | Consumer Control Unit | EWM  |                15 | Pcs  |
 ------------ ----------- ----------------------- ------ ------------------- ------ 

issue_consumables 表

 ---- ---------- ------------ -------------- ----------- ----------------- ------ 
| id | username | doe        | issued_to    | item_code | quantity_issued | unit |
 ---- ---------- ------------ -------------- ----------- ----------------- ------ 
|  1 | STAMP    | 2021-12-18 | John Doe     | ELM001    |               4 | Pcs  |
|  2 | STAMP    | 2021-12-18 | John Doe     | ELM002    |               5 | Pcs  |
|  3 | STAMP    | 2021-12-18 | John Doe     | ICT001    |              35 | Pcs  |
|  4 | STAMP    | 2021-12-15 | Jessy Jesica | Jell001   |              20 | Pcs  |
 ---- ---------- ------------ -------------- ----------- ----------------- ------ 

我想要的結果

 ---- ----------- ------------ -------------- ----------------------- ----------------- 
| id | item_code | date1      | issued_to    | item_description      | quantity_issued |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 
|  4 | Jell001   | 15-12-2021 | Jessy Jesica | Petroleum Jelly       |              20 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  2 | ELM002    | 18-12-2021 | John Doe     | Consumer Control Unit |               5 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Petroleum Jelly       |               4 |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 

我的查詢是

SELECT
    issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued
    FROM consumables
    RIGHT JOIN issue_consumables ON consumables.item_code = issue_consumables.item_code
    ORDER BY issue_consumables.id DESC

結果得到

 ---- ----------- ------------ -------------- ----------------------- ----------------- 
| id | item_code | date1      | issued_to    | item_description      | quantity_issued |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 
|  4 | Jell001   | 15-12-2021 | Jessy Jesica | Petroleum Jelly       |              20 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  3 | ICT001    | 18-12-2021 | John Doe     | Carburator            |              35 |
|  2 | ELM002    | 18-12-2021 | John Doe     | D3210 Contactor       |               5 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Consumer Control Unit |               4 |
|  1 | ELM001    | 18-12-2021 | John Doe     | Consumer Control Unit |               4 |
 ---- ----------- ------------ -------------- ----------------------- ----------------- 

我在哪里做錯了以獲得所需的結果

uj5u.com熱心網友回復：

您需要有一組unique item_code及其描述才能實作結果。

就像是：

select issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued
    FROM issue_consumables
join ( select distinct item_code, item_description from consumables ) consumables on consumables.item_code = issue_consumables.item_code

包括 quantity_received

select issue_consumables.id,
    issue_consumables.item_code,
    DATE_FORMAT(issue_consumables.doe,'%d-%m-%Y')as date1,
    issue_consumables.issued_to,
    consumables.item_description,
    issue_consumables.quantity_issued,
    consumables.quantity_received
    FROM issue_consumables
join ( select item_code, item_description, sum(quantity_received) quantity_received from consumables group by item_code, item_description ) consumables on consumables.item_code = issue_consumables.item_code

標籤：mysql

上一篇：mysql合并相同的資料并在以后保留更新的資料

下一篇：如何從mysql資料庫中永久洗掉記錄