我想在一个查询中的2个表中的数据？ [英] i want data from 2 tables in one query ?

问题描述

我有2个表都包含几乎相同的字段，第一个表每月与当前支出表类似，第二个表每年包含每月表列的年度分配数量。我想要实现的是我想要所有第一个表列的总和，我也想显示第二个表的内容。



预期产量：

i have 2 tables both contain almost same field the 1st table monthly is like the current expenditure table and the 2nd table yearly contains yearly allocated amount for monthly table columns. what i m trying to achieve is i want the sum of all the first table columns and also i want to show the contents of 2nd table.

expected output:

Heads        || budget allocation during year ||  total expenditure ||  balance amount
salary           20000                            18000               
ta               10000                            1000
contingency      3000                             2000
NRC              1000                              500
institcharges    100000

其他人

others

now in footer || total of all this         ||   total of this    ||  allocation-expenditure

现在我只需要查询工作我已经使布局和页脚工作正常工作我只需要分组第一张表的内容只显示其他内容

now i just need the query to work i have made the layout and footer thing to work i just need to group by the contents of 1st table and just display contents of other

"select m.pcode, m.fyyear,sum(m.salary),sum(m.ta),sum(m.contigency),sum(m.nrc), sum(m.institcharges),sum(m.others),y.yearlyalloc,y.salary as sal1,y.ta as ta1,y.contigency as cont1, y.nrc as nrc1,y.institcharges as inst1,y.others as other1 FROM monthly AS m , yearly as y where m.pcode=y.pcode and m.pcode=(''" + DropDownList1.SelectedItem.Value.ToString() + "'' ) AND y.fyyear like(''" + DropDownList2.SelectedItem.Value.ToString() + "'') and m.pcode, m.fyyear= y.fyyear group by m.pcode, m.fyyear", con

这是我正在使用的查询，但显然是错误的。我需要一种添加第一个表的所有列的方法，只显示第二个表的列

this is the query i am working with but its apparently wrong.i need a way to add all columns of 1st table and just display the columns of 2nd table

推荐答案

Th是使用SQL关键字JOIN完成



请参阅文章 [ ^ ]解释了JOIN的用法。祝你好运，

Edo

This is accomplished using the SQL keyword JOIN

See article[^] explaining the usage of JOIN.

Good luck,
Edo

string str = string.Format(@"select m.pcode,sum( m.salary) as SalaryConsumed, y.salary,sum(m.ta) as taConsumed,y.ta,sum(m.contigency) as contiused, y.contigency,sum(m.nrc) as nrcconsumed,y.nrc,sum(m.institcharges) as institgiven,y.institcharges,sum(m.others) as miscused,y.others, m.fyyear,y.yearlyalloc from monthly m
        inner join yearly y on m.pcode = y.pcode and m.fyyear = y.fyyear where m.pcode=('" + DropDownList1.SelectedItem.ToString() + "' ) AND m.fyyear=('" + DropDownList2.SelectedItem.ToString() + "')group by m.fyyear, m.pcode, y.salary, y.ta,y.contigency,y.nrc,y.institcharges,y.others,y.yearlyalloc", con);

这篇关于我想在一个查询中的2个表中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！