注册:显示学生ID的最后余额,完成后将不会显示在列表中 [英] Reg: Show the Last balance of the student id, after completing it won't show in the list
本文介绍了注册:显示学生ID的最后余额,完成后将不会显示在列表中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
您好,
SID课程付费BAL TOT BILLNO日期
1 MATHS 6000 4000 10000 001 01/06 / 2013
2 MATHS 5000 5000 10000 002 01/06/2013
3 MATHS 2000 8000 10000 003 01/06/2013
2 MATHS 3000 2000 10000 004 02/06/2013
4 MATHS 7000 3000 10000 005 03/06/2013
1 MATHS 4000 0.0 10000 006 04/06/2013
2 MATHS 2000 0.0 10000 007 05/06/2013
3 MATHS 5000 3000 10000 003 06/06/2013
我想显示学生的最后余额记录,如果他完成总余额,则不会显示在列表中。即,我期待以下o / p。
SID COURSE支付BAL TOT BILLNO日期
3 MATHS 5000 3000 10000 003 01/06/2013
4 MATHS 7000 3000 10000 005 03/06/2013
如果可能,我需要代码......
解决方案
请阅读我对你问题的评论。< br $>
看看这里:
SET DATEFORMAT dmy;
DECLARE @ tbl TABLE ([SID] INT ,[COURSE] VARCHAR ( 30 ),[PAID] DECIMAL ( 8 , 2 ),[BAL] DECIMAL ( 8 , 2 ),[TOT] DECIMAL ( 8 ,< span class =code-digit> 2 ),[BILLNO] VARCHAR ( 3 ),[ DATE ] DATETIME )
INSERT INTO @ tbl ([SID],[COURSE],[PAID ],[BAL],[TOT],[BILLNO],[ DATE ])
SELECT 1 AS [SID],' MATHS' AS [COURSE], 6000 AS [PAID], 4000 AS [BAL], 10000 AS [TOT],' 001' AS BILLNO,' 01/06/2013' AS [ DATE ]
UNION < span class =code-keyword> ALL SELECT 2 ,' MATHS', 5000 , 5000 , 10000 ,' 002', ' 01/06/2013'
UNION ALL SELECT 3 , ' MATHS', 2000 , 8000 , 10000 ,' 003',' 01/06/2013'
UNION ALL SELECT 2 ,' MATHS', 3000 , 2000 , 10000 ,' 004', ' 02/06/2013'
UNION ALL SELECT 4 , ' MATHS', 7000 , 3000 , 10000 ,' 005',' 03/06/2013'
UNION ALL SELECT 1 ,' MATHS', 4000 , 0 。 0 , 10000 ,' 006 ',' 04/06/2013'
UNION ALL SELECT 2 ,' MATHS', 2000 , 0 。 0 , 10000 ,' 007',' < span class =code-string> 05/06/2013'
UNION ALL SELECT 3 ,' MATHS', 5000 , 3000 , 10000 ,' 003',' 06/06/2013 '
SELECT t1。[SID],t1。[COURSE],t1。[PAID], t1。[BAL],t1。[TOT],t1。[BILLNO],t1。[ DATE ]
FROM @ tbl AS t1 INNER JOIN (
SELECT [SID],MAX([ DATE ]) AS [ DATE ]
FROM @ tbl
GROUP BY [SID]
) AS t2 ON t1。[SID] = t2。[SID] AND t1。[ DATE ] = t2。[ DATE ]
WHERE [BAL]> 0 - 你是什么意思:TOT完成????
ORDER BY t1。[SID]
结果:
<前lang =文字> [SID] [课程] [付费] [BAL] [TOT] [BILLNO] [日期] ]
3 MATHS 5000.00 3000.00 10000.00 003 2013-06-06 00:00:00.000
4 MATHS 7000.00 3000.00 10000.00 005 2013-06-03 00:00:00.000
Hi,
SID COURSE PAID BAL TOT BILLNO DATE
1 MATHS 6000 4000 10000 001 01/06/2013
2 MATHS 5000 5000 10000 002 01/06/2013
3 MATHS 2000 8000 10000 003 01/06/2013
2 MATHS 3000 2000 10000 004 02/06/2013
4 MATHS 7000 3000 10000 005 03/06/2013
1 MATHS 4000 0.0 10000 006 04/06/2013
2 MATHS 2000 0.0 10000 007 05/06/2013
3 MATHS 5000 3000 10000 003 06/06/2013
I want to show the record last balance of the studentid, and if he completing his total balance, it wont't show in the list. ie., I'm expecting the following o/p.
SID COURSE PAID BAL TOT BILLNO DATE
3 MATHS 5000 3000 10000 003 01/06/2013
4 MATHS 7000 3000 10000 005 03/06/2013
If possible i need the code...
解决方案
Please, read my comment to your question.
Have a look here:
SET DATEFORMAT dmy; DECLARE @tbl TABLE([SID] INT, [COURSE] VARCHAR(30), [PAID] DECIMAL(8,2), [BAL] DECIMAL(8,2), [TOT] DECIMAL(8,2), [BILLNO] VARCHAR(3), [DATE] DATETIME) INSERT INTO @tbl ([SID], [COURSE], [PAID], [BAL], [TOT], [BILLNO], [DATE]) SELECT 1 AS [SID], 'MATHS' AS [COURSE], 6000 AS [PAID], 4000 AS [BAL], 10000 AS [TOT], '001' AS BILLNO, '01/06/2013' AS [DATE] UNION ALL SELECT 2, 'MATHS', 5000, 5000, 10000, '002', '01/06/2013' UNION ALL SELECT 3, 'MATHS', 2000, 8000, 10000, '003', '01/06/2013' UNION ALL SELECT 2, 'MATHS', 3000, 2000, 10000, '004', '02/06/2013' UNION ALL SELECT 4, 'MATHS', 7000, 3000, 10000, '005', '03/06/2013' UNION ALL SELECT 1, 'MATHS', 4000, 0.0, 10000, '006', '04/06/2013' UNION ALL SELECT 2, 'MATHS', 2000, 0.0, 10000, '007', '05/06/2013' UNION ALL SELECT 3, 'MATHS', 5000, 3000, 10000, '003', '06/06/2013' SELECT t1.[SID], t1.[COURSE], t1.[PAID], t1.[BAL], t1.[TOT], t1.[BILLNO], t1.[DATE] FROM @tbl AS t1 INNER JOIN ( SELECT [SID], MAX([DATE]) AS [DATE] FROM @tbl GROUP BY [SID] ) AS t2 ON t1.[SID] = t2.[SID] AND t1.[DATE]=t2.[DATE] WHERE [BAL]>0 --what do you mean: TOT completed???? ORDER BY t1.[SID]
Result:
[SID] [COURSE] [PAID] [BAL] [TOT] [BILLNO] [DATE] 3 MATHS 5000.00 3000.00 10000.00 003 2013-06-06 00:00:00.000 4 MATHS 7000.00 3000.00 10000.00 005 2013-06-03 00:00:00.000
这篇关于注册:显示学生ID的最后余额,完成后将不会显示在列表中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文