从sql中的列中减去列的和

Question

我插入前四列多次，新的paidVal。现在我想要得到sid,snameVal, balance

我在studenttable和felt上使用了内连接。

我试过了。但是我得到了3行，而我应该得到2行。表中有一个字段cNameVal，我输入的不同的值等于我得到的行数

sql
SELECT studenttbl.sid,
studenttbl.snameVal,
studenttbl.sPhoneVal,
coursefeeval - SUM(paidval) AS balance,
CONVERT(varchar, studenttbl.sdateVal, 106) AS sdateVal
FROM feeTbl
INNER JOIN studenttbl ON feeTbl.sid = studenttbl.sid
GROUP BY studenttbl.sid,
studenttbl.snameVal,
coursefeeval,
sPhoneVal,
sdateVal;

feetbl

sid       snameVal cNameVal    courseFeeVal    paidVal 
1001    Vivek     7            4000          1000   
1001    Vivek     7            4000          1000   
1002    Rahul     8            5000          1000   
1002    Rahul     5            6000          5000   

Answer 1

没有预期的结果，这是基于以下注释:

我想在这里得到sid,snameVal, balance平衡意味着courseFeeVal - SUM(paidVal)'

似乎你想要一个有窗口的SUM:

SELECT st.sid,
st.snameVal,
st.sPhoneVal,
coursefeeval - SUM(paidval) OVER (PARTITION BY st.sid) AS balance, --2 of these columns need qualifying
st.sdateVal AS sdateVal --leave dates as a date and time data time, don't convert to a string
FROM feeTbl fT
INNER JOIN studenttbl st ON fT.sid = st.sid;