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