比较两个表 [英] Compare between two tables

问题描述

你好，

我正在开发Windows应用程序，它将文本文件转换为sql紧凑数据库.从文本文件转换的数据将存储在表1中.
表1有2个栏，项目代码和数量:
物品代码数量
111111 3
111112 2
111113 4

我有2个表，其中有4个列，位置，项目代码，snnumber，数量；
位置项目代码snnumber数量
LG，111111 566655 1
LG，111111 565656 1
LG，111111 565656 1

表2中的意思是，我的数量全部固定为1.，说得到3个相同的物品，我将存储3个不同的物品.但是在表1中，它将显示数量为3.

因此，现在我要比较这两个表，以使表1中的表项代码存在于表2中，反之亦然..我想在csv或excel文件中生成一份报告，说明方差和数量.
这可能吗?如果是，如何?有人可以引导我..

Hello,

i''m developing a windows application where it will convert textfile to sql compact database.this converted data from textfile will be stored in table 1.
table 1 have 2 colums,itemcode and qty:
itemcode qty
111111 3
111112 2
111113 4

and i have table 2 with 4 colums ,location,itemcode,snnumber,qty;
location itemcode snnumber qty
LG , 111111 566655 1
LG , 111111 565656 1
LG , 111111 565656 1

means in table 2,i have qty with all fixed to 1.lets say got 3 same items,i will store as 3 different items.but in table 1,it will show the quantity as 3.

so,now i want to compare these two tables,so that itemcode in table 1 exists in table2,and vice versa..and i want a produce a report in csv or excel file, the variance,and the quantity.
is this possible?if yes,how?can anyone guide me..

推荐答案

不太了解您的问题.看起来您可以使用SQL组按以下结构使用表2中的信息填充表1:

选择项目代码，总和(数量)
来自table2
按项目代码分组

什么是您的问题?

Not really understand your question. It looks like you can fill table 1 with info from table 2 using a SQL group by construct like:

select itemcode, sum(quantity)
from table2
group by itemcode

what is exaclty your question?

如果我理解您的问题，您将需要一个利用子查询和内部联接的查询生成适当的结果表.我认为这将类似于以下内容:

If I understand your question, you will need a query that utilizes sub-queries and inner join to generate the appropriate resulting table. I think it would be something similar to this:

SELECT 
    tbl1.itemcode, 
    tbl1.T1Quantity, 
    tbl2.T2Quantity, 
    (tbl1.T1Quantity - tbl2.T2Quantity) AS Variance 
FROM 
    ( SELECT 
          itemcode, 
          qty AS T1Quantity 
      FROM 
          table1
    ) AS tbl1 INNER JOIN 
    ( SELECT 
          itemcode, 
          SUM(qty) AS T2Quantity 
      FROM 
          table2 
      GROUP BY 
          itemcode
    ) AS tbl2 ON tbl1.itemcode = tbl2.itemcode

SELECT
    Table1.itemcode, 
    Table1.qty AS T1Quantity, 
    SUM(Table2.qty) AS T2Quantity, 
    Table1.qty - SUM(Table2.qty) AS Variance
FROM
    Table1 INNER JOIN
    Table2 ON Table1.itemcode = Table2.itemcode
GROUP BY 
    Table1.itemcode, 
    Table1.qty

这篇关于比较两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！