如何从sql列的值中找到和

Question

我有两个表，其中包含以下数据:

studenttbl:

seatno  sname   coursename      scoursefee
------------------------------------------
1006    Vivek    MS-CIT           4500
1005    RAJESH   TALLY ERP        5200
1004    Anil     MS-CIT           4500
1003    ANKITA   OFFICE EXPERT    4200
1002    SACHIN   TALLY ERP        5200
1001    VIJAY    MS-CIT           4500

feetbl:

feeid   seatno  receivedamt receiptno   receiveddate
-----------------------------------------------------------
1     1001        2500     1001   2021-10-02 06:27:28.000
2     1002        2200     1002   2021-10-02 06:28:11.000
3     1003        2700     1003   2021-10-02 06:29:03.000
4     1003        500      1004   2021-10-02 06:32:20.000
5     1004        1500     102    2021-10-02 07:02:37.000
6     1004        1000     101    2021-10-02 07:08:03.000
7     1005        5200     103    2021-10-02 07:23:46.000

我需要找到下面的数据，但我没有得到座位1006的平衡值:

jdate      seatno   sname   coursename  Scoursefee  paid    balance
-------------------------------------------------------------------
02 Oct 2021 1005    RAJESH  TALLY ERP       5200    5200    0
02 Oct 2021 1004    Anil    MS-CIT          4500    2500    2000
02 Oct 2021 1003    ANKITA  OFFICE EXPERT   4200    3200    1000
02 Oct 2021 1002    SACHIN  TALLY ERP       5200    2200    3000
02 Oct 2021 1001    VIJAY   MS-CIT          4500    2500    2000

我使用这个SQL查询:

SELECT 
CONVERT(VARCHAR, jdate, 106) AS jdate,
st.seatno,
sname,
coursename,
Scoursefee,
SUM(ft.receivedamt) AS paid,
st.Scoursefee - SUM(ft.receivedamt) AS balance
FROM 
studenttbl st
INNER JOIN 
feetbl ft ON st.seatno = ft.seatno
WHERE 
JDate BETWEEN '1990-01-01 00:00:00'
AND '2021-10-05 00:00:00'
GROUP BY 
st.seatno, st.Scoursefee, sname, 
jdate, coursename, Scoursefee
ORDER BY 
st.seatno DESC

请帮我一下。

Answer 1

这个问题是由于INNER JOIN与LEFT JOIN。当你内连接你只有两个表之间的匹配。当您使用LEFT JOIN时，您将从左侧表中获取所有值，并且只匹配右侧表中的值。

同样，在这个问题上，您的SUM函数将中断。你需要在ISNULL(col,0)中包装左JOIN表的列，这样你的聚合才能正确工作。

我还更改了WHERE子句中的日期比较，使用<=和>=。Aaron Bertrandt多次谈到这个话题，这里有一个很好的链接:

https://www.mssqltips.com/sqlservertutorial/9316/sql-server-between-dates-issue/

SELECT convert(VARCHAR, jdate, 106) AS jdate
,st.seatno
,sname
,coursename
,Scoursefee
,sum(ft.receivedamt) AS paid
,st.Scoursefee - sum(ISNULL(ft.receivedamt,0)) AS balance
FROM studenttbl st
LEFT JOIN feetbl ft ON st.seatno = ft.seatno
WHERE JDate >='1990-01-01 00:00:00'
AND JDate <='2021-10-05 00:00:00'
GROUP BY st.seatno
,st.Scoursefee
,sname
,jdate
,coursename
,Scoursefee
ORDER BY st.seatno DESC

Answer 2

这是因为座位1006没有显示在脚表中。尝试对其进行左连接，以便从Studenttbl中提取所有信息。或者,将适当的数据添加到feetbl。