SQL查询加入和分组表

时间:2022-01-06 22:30:19

Select table1.id, (table1.total_item-table2.requested) as items_left from table1, table2 where table2.year = 2015 group by table1.id

选择table1.id,(table1.total_item-table2.requested)作为来自table1,table2的items_left,其中table2.year = 2015 group by table1.id

i am using SQL Server 2008.. Whats wrong with my code? it cannot be group in id number,

我正在使用SQL Server 2008 ..我的代码有什么问题?它不能是身份证号码中的群组,

i want to sum all the individual table1.items in table1, and sum all the individual table2.requested in table 2 to subtract the remaining items as I name it items_left, and group it by table1.id

我想总结table1中的所有单个table1.items,并将表2中所有单独的table2.requested相加以减去剩余的项目,因为我将其命名为items_left,并将其分组为table1.id

something like this...

像这样的东西......

0.  id   items_left  year
1.   1    3        2015
2.   5    10       2015
3.   3    4        2015

this is the output of that code above... there is a duplication of id and I cant group it to

这是上面那段代码的输出......有一个重复的id,我不能把它分组

0.  id  items_left
1.   1    1
2.   1    1
3.   1    2
4.   5    5
5.   3    2
6.   5    5
7.   3    2

i want an output like this.. please sir/ma'am help me..

我想要这样的输出..请先生/女士帮助我..

something like this...

像这样的东西......

0. id items_left  year
1.   1    3       2015
2.   5    10      2015
3.   3    4       2015

1 个解决方案

#1


0  

You are missing a "sum()" for the second field; also there is missing joining condition:

你错过了第二个字段的“sum()”;还有缺少加入条件:

SELECT tab1.id, sum(tab1.total_item - ifnull(tab2.requested,0)) AS items_left
FROM tab1
LEFT JOIN tab2 ON tab1.id = tab2.??
WHERE tab2.year = 2015
GROUP BY tab1.id

#1


0  

You are missing a "sum()" for the second field; also there is missing joining condition:

你错过了第二个字段的“sum()”;还有缺少加入条件:

SELECT tab1.id, sum(tab1.total_item - ifnull(tab2.requested,0)) AS items_left
FROM tab1
LEFT JOIN tab2 ON tab1.id = tab2.??
WHERE tab2.year = 2015
GROUP BY tab1.id