110,548
社区成员
发帖
与我相关
我的任务
分享
private void button1_Click( object sender, EventArgs e )
{
//构造A表
DataTable dtA = new DataTable();
DataColumn c1 = new DataColumn( "ID", typeof( string ) );
DataColumn c2 = new DataColumn( "QTYA", typeof( int ) );
c2.DefaultValue = 0;
DataColumn c3 = new DataColumn( "MONEYA", typeof( int ) );
c3.DefaultValue = 0;
dtA.Columns.Add( c1 );
dtA.Columns.Add( c2 );
dtA.Columns.Add( c3 );
//dtA.PrimaryKey = new DataColumn[] { c1 };
//A表数据
dtA.Rows.Add( "001", 10, 1000 );
dtA.Rows.Add( "002", 5, 300 );
dtA.Rows.Add( "003", 3, 700 );
//构造B表
DataTable dtB = new DataTable();
DataColumn cc1 = new DataColumn( "ID", typeof( string ) );
DataColumn cc2 = new DataColumn( "QTYB", typeof( int ) );
cc2.DefaultValue = 0;
DataColumn cc3 = new DataColumn( "MONEYB", typeof( int ) );
cc3.DefaultValue = 0;
dtB.Columns.Add( cc1 );
dtB.Columns.Add( cc2 );
dtB.Columns.Add( cc3 );
//dtB.PrimaryKey = new DataColumn[] { cc1 };
//A表数据
dtB.Rows.Add( "001", 3, 300 );
dtB.Rows.Add( "002", 1, 60 );
dtB.Rows.Add( "004", 2, 10000 );
dtB.Rows.Add( "005", 3, 700 );
//合并
dtA.Merge( dtB, true, MissingSchemaAction.Add );
dataGridView1.DataSource = dtA;
}
select id,SUM(QTYA) AS QTYA,SUM(MONEYA) AS MONEYA,SUM(QTYB) AS QTYB,SUM(MONEYB) AS MONEYB
FROM
(
select id,QTYA,MONEYA,0 as QTYB,0 as MONEYB
from tabA
union all
select id,0,0,QTYB, MONEYB
from tabB
) as Tab
GROUP BY ID
DataTable dtResult=new DataTable();
dtResult.Coloumns.Add("ID");
dtResult.Coloumns.Add("QTYA");
dtResult.Coloumns.Add("MONEYA");
dtResult.Coloumns.Add("QTYB");
dtResult.Coloumns.Add("MONEYB");
int k = 0;
int i = 0;
int j = 0;
while(i < dtA.Rows.Count && j < dtB.Rows.Count) {
if (int.parse(dtA.Rows[i][0]) < int.parse(dtB.Rows[i][0]))
{ dtResult.Rows.Add(dtA.Rows[i][0].toString() ,dtA.Rows[i][1].toString() ,dtA.Rows[i] [2].toString(),"0","0" );
i++;
}
else if (int.parse(dtA.Rows[i][0]) == int.parse(dtB.Rows[i][0]))
{
dtResult.Rows.Add(dtA.Rows[i][0].toString() ,dtA.Rows[i][1].toString() ,dtA.Rows[i] [2].toString(),dtB.Rows[i][1].toString(),dtB.Rows[i][2].toString());
i++;
j++;
}
else
{
dtResult.Rows.Add(dtB.Rows[i][0].toString() ,"0","0",dtB.Rows[i][1].toString(),dtB.Rows[i][2].toString() );
j++;
}
}
while (i < dtA.Rows.Count)
{
dtResult.Rows.Add(dtA.Rows[i][0].toString() ,dtA.Rows[i][1].toString() ,dtA.Rows[i] [2].toString(),"0","0" );
i++;
}
while (j < dtB.Rows.Count)
{
dtResult.Rows.Add(dtB.Rows[i][0].toString() ,"0","0",dtB.Rows[i][1].toString(),dtB.Rows[i][2].toString() );
j++;
}