mysql数据库表中的混乱

Question

我对将结果放入两个表有点困惑，我知道这很简单，但没有任何线索。。这是我的查询，它给了我结果，但当我使用group by时，它只给了我一行的结果，即使数据库中的实际结果是（300500），因为我是通过lead_id获取结果的，这在两个表中都很常见。。

SELECT inv.amount, crmd.computers, crmd.product_id, crmd.pc_opti
FROM invoice AS inv, lead_crm_data AS crmd
WHERE inv.lead_id = '310'
AND crmd.lead_id = '310'
GROUP BY inv.lead_id

这是给定查询的结果。。

amount  computers   product_id  pc_opti
300     2           7, 6            2
300     3           7, 6            3
540     2           7, 6            2
540     3           7, 6            3

GROUP BY的结果

300     2           7, 6            2

所需结果

300     2           7, 6            2
540     3           7, 6            3

Answer 1

试试这个：

SELECT inv.amount, crmd.computers, crmd.product_id, crmd.pc_opti
FROM invoice AS inv, lead_crm_data AS crmd
WHERE inv.lead_id = '310'
  AND crmd.lead_id = '310'
GROUP BY inv.amount

您可以使用更好的（IMHO）语法：

SELECT inv.amount, crmd.computers, crmd.product_id, crmd.pc_opti
FROM invoice AS inv INNER JOIN lead_crm_data AS crmd 
    ON inv.lead_id = crmd.lead_id 
   AND inv.lead_id = '310'
GROUP BY inv.amount