MySQL查询打印双重记录

Question

sales & purchase表中的所有记录器只输入一次。我仔细检查过了。但是，我希望将这两个表组合在一起，这样两个表都将被完全提取。下面是我的查询注意事项，timestamp列具有mysql DATE格式。

Select 
    sales.ID as sid,
    sales.saleHatsh,
    sales.timestamp as sdate,
    sales.gatePass as sGP,
    sales.pname as sPN,
    sales.description as sDES,
    sales.balance as sbal, 
    purchase.ID as pid,
    purchase.purchaseHatsh,
    purchase.timestamp as pdate,
    purchase.gatePass as pGP,
    purchase.pname as pPN,
    purchase.description as pDES,
    purchase.balance as pbal 
from sales,purchase 
where sales.timestamp='2013-11-11' OR purchase.timestamp='2013-11-11'

以下是我的查询结果& sales & purchase表

Sales表只有2个记录器

​

采购表只有4台录音机

​

Answer 1

正在发生的事情是，你没有以任何方式加入这些表格。所以你可以从这些桌子上得到所有可能的匹配。看一下列，我认为您不想加入，但是您可能想要一个友联市：

SELECT 
  sales.ID AS id,
  sales.saleHatsh AS hatsch,
  sales.TIMESTAMP AS date,
  sales.gatePass AS GatePass,
  sales.pname AS pname,
  sales.description AS Description,
  sales.balance AS balance,
  'SALE' AS transanctionType
FROM sales
WHERE sales.TIMESTAMP = '2013-11-11'
UNION 
SELECT 
  purchase.ID,
  purchase.purchaseHatsh,
  purchase.TIMESTAMP,
  purchase.gatePass,
  purchase.pname,
  purchase.description,
  purchase.balance,
  'PURCHASE'
FROM purchase
WHERE purchase.TIMESTAMP = '2013-11-11'

我为您添加了一列"transactionType“，以确定哪些是销售，哪些是购买。