时间戳

首页 > 解决方案 > MySQL-如何查询从两个不同的表中选择打开记录发出的收盘余额率，一个是购买，另一个是发出

mysql - MySQL-如何查询从两个不同的表中选择打开记录发出的收盘余额率，一个是购买，另一个是发出

问题描述

我想进行一个查询，从两个表中检索数据，即一个是购买，另一个是问题。两个表都有相同的字段，即 icode、qty、rate、purdate 和 issueate。购买查询是：-SELECT Dry_Purchase.Icode, Sum(Dry_Purchase.Qty) AS SumOfQty, Dry_Purchase.Rate FROM Dry_Purchase WHERE (((Dry_Purchase.PurDate) Between DateSerial(Year(Date()),(Month(Date())) -1),21) 和 DateSerial(Year(Date()),Month(Date()),20))) GROUP BY Dry_Purchase.Icode, Dry_Purchase.Rate; 购买查询的输出为：Icode SumOfQty Rate 11 10 13.5 11 39.5 14 19 75 79.75 19 22 80 21 54 87.45 23 15 218 24 10.5 650 8 79 33.25 8 13 34

问题查询是：- SELECT Dry_Expense.Icode, Sum(Dry_Expense.Qty) AS SumOfQty, Dry_Expense.Rate FROM Dry_Expense WHERE (((Dry_Expense.ExpDate) Between DateSerial(Year(Date()),(Month(Date()) -1),21) 和 DateSerial(Year(Date()),Month(Date()),20))) GROUP BY Dry_Expense.Icode, Dry_Expense.Rate; 这个查询的输出是

Icode SumOfQty 比率 11 11.55 13 11 8.55 13.5 11 10.8 14 19 2.35 80 21 54 87.45 8 15.9 33.25

在结合以上两个查询后，输出应该像这个 rptdate icode opening recd 发出 closingbal rate 19/09/18 11 0 10 8.550 1.450 13.50 19/09/18 11 0 39.5 10.800 28.700 14.00 19/09/18 19 0 75 0.000 75 79。 19/09/18 19 0 22 2.350 72.650 80.00 19/09/18 21 0 54 54 0 87.45 19/09/18 23 0 15 0 15 218.00 19/09/18 24 0 10.5 0 10.500 / 650 80/19/09 0 79.0 15.900 63.100 33.25 19/09/18 8 0 13.0 0 13.000 34.00 19/09/18 8 11.550 0 11.550 0 13.00

请帮助我如何查询此输出我正在尝试此查询 SELECT A.icode,A.qty,A.rate,A.recd as recd,B.Issued as Issue FROM (SELECT icode,rate,purdate,SUM( Abs(qty)) AS recd FROM Dry_Purchase GROUP BY icde,rate ) A, (SELECT icode,rate,expdate,(SUM(Abs(qty)) AS Issue FROM Dry_Expense GROUP BY icode,rate) B WHERE A.icode=B .icode AND A.rate=B.rate AND (A.purdate between DateSerial((Year(Date()),(Month(Date())-1),21)) 和 DateSerial(Year(Date()), Month(Date()),20)) AND B.expdate DateSerial((Year(Date()),(Month(Date())-1),21) 和 DateSerial(Year(Date()),Month(日期（）），20））；请帮助我

标签： mysql

解决方案

使用子选择来定位最短日期，然后加入 以获取与该日期匹配的行。

SELECT
    a.`Purdate` AS a.`Date1`, a.`Qty`, a.`Rate`
FROM `TableName` a
JOIN (SELECT MIN(`PurDate`) as `minDate`
    FROM `TableName`
    WHERE `Icode` = '1') b
  ON b.`minDate` = a.`PurDate`
WHERE a.`Icode` = '1'

推荐阅读