如何显示下面提到的SQL存储过程--- SQL服务器 [英] How can I display below mentioned SQL store procedure --- SQL server
本文介绍了如何显示下面提到的SQL存储过程--- SQL服务器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我怎样才能显示
studentId Apr May Jun< br />
xyz总和可能是6月的总和< br />
< br />
就像所有月份一样明智< br />
< br />
< br />
有两个表可用FeeAssigned和FeeExemption。
创建 表 FeeAssigned([FEE HEAD] NVARCHAR ( 50 ) ,APR INT ,MAY INT ,JUN INT ,JUL INT ,AUG INT ,SEP INT ,OCT INT ,NOV INT ,DEC INT ,JAN INT ,FEB INT ,MAR INT ,StudentId NVARCHAR ( 50 ))
CREATE 表 FeeExemption([FEE HEAD] NVARCHAR (<跨度cl ass =code-digit> 50 ),APR INT ,MAY INT ,JUN INT ,JUL INT ,AUG INT , SEP INT ,OCT INT ,NOV INT ,DEC INT ,JAN INT ,FEB INT ,MAR INT ,StudentId NVARCHAR ( 50 ))
- INSERT DATA
INSERT INTO FeeAssigned
SELECT ' TUITION FEE', 120 , 120 , 120 , 120 , 120 , 120 , 120 , 120 , 120 , 120 , 120 , 120 ,' STUD01'
UNION ALL
SELECT ' TRAN FEE', 50 , 50 , 50 , 50 ,< span class =code-digit> 50 , 50 , 50 , 50 , 50 , 50 , 50 , 50 ,' STUD01'
INSERT INTO FeeExemption
SELECT ' TUITION FEE', 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 ,< span class =code-string>' STUD01'
UNION < span class =code-keyword> ALL
SELECT ' TRAN FEE', 10 , 10 , 10 , 10 ,< span class =code-digit> 10 , 10 , 10 , 10 , 10 , 10 , 10 , 10 ,' STUD01'
我的尝试:
< br />
CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))< br />
AS< br />
BEGIN< br />
SELECT FA.StudentId,FA。[FEE HEAD],< br />
FA.APR - ISNULL(FET.APR,0)AS APR,< br />
FA.MAY - ISNULL(FET.MAY,0)AS MAY,< br />
FA.JUN - ISNULL(FET.JUN,0)AS JUN,< br / >
FA.JUL - ISNULL(F ET.JUL,0)AS JUL,< br />
FA.AUG - ISNULL(FET.AUG,0)AS AUG,< br />
FA.SEP - ISNULL(FET。 SEP,0)AS SEP,< br />
FA.OCT - ISNULL(FET.OCT,0)AS OCT,< br />
FA.NOV - ISNULL(FET.NOV, 0)AS NOV,< br />
FA.DEC - ISNULL(FET.DEC,0)AS DEC,< br />
FA.JAN - ISNULL(FET.JAN,0) AS JAN,< br />
FA.FEB - ISNULL(FET.FEB,0)AS FEB,< br />
FA.MAR - ISNULL(FET.MAR,0)AS MAR< ; br />
FROM FeeAssigned FA LEFT JOIN< br />
(SELECT FE.StudentId,FE。[FEE HEAD],< br />
SUM(FE.APR) AS APR,< br />
SUM(FE.MAY)AS MAY,< br />
SUM(FE.JUN)AS JUN,< br />
SUM( FE.JUL)AS JUL,< br />
SUM(FE.AUG)AS AUG,< br />
SUM(FE.SEP)AS SEP,< br />
SUM(FE.OCT)AS OCT,< br />
SUM(FE.NOV)AS NOV,< br />
SUM(FE.DEC)AS DEC,< br $>
SUM(FE.JAN)AS JAN,< br />
SUM(FE.FEB)AS FEB,< br />
SUM(FE.MAR)AS MAR< br />
来自FeeExemption FE< br />
GROUP BY FE.StudentId,FE。[FEE HEAD])AS FET ON(FA.StudentId = FET.StudentId)AND(FA。[FEE HEAD] = FET。[FEE HEAD])< br />
WHERE FA.StudentId =@StudentId< br />
结束< br />
< br />
解决方案
尝试此查询。我不会处理null请当你知道它实际上适合你时,处理null。快乐编码:)
选择 FA.StudentId,SUM(FA.APR-FE.APR) As APR,SUM(FA.MAY-FE.MAY)作为 MAY,SUM(FA.JUN-FE.JUN)作为 JUN,SUM(FA.JUL-FE.JUL) As JUL
,SUM(FA.AUG-FE.AUG) As AUG,SUM(FA.SEP-FE) .SEP)作为 SEP,SUM(FA.OCT-FE.OCT)作为 OCT ,SUM(FA.NOV-FE.NOV) As NOV,SUM(FA.DEC-FE.DEC) As DEC
,SUM(FA.JAN-FE.JAN)作为 JAN,SUM(FA.FEB-FE.FEB)作为 FEB,SUM(FA.MAR-FE.MAR)作为 MAR 来自 FeeAssigned FA
inner join FeeExemption FE FA.StudentId = FE.StudentId AND FA。[FEE HEAD] = FE。[FEE HEAD]
group 按 FA.StudentId
你的结果将是
StudentId APR MAY JUN JUL JUG SEP 10月12日星期一2月2月3日
STUD01 150 150 150 150 150 150 150 150 150 150 150 150
How can I display like
studentId Apr May Jun <br /> xyz sum of apr sum of may sum of Jun<br /> <br /> like wise for all month<br /> <br /> <br />
There are two table available FeeAssigned and FeeExemption.
CREATE TABLE FeeAssigned ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
CREATE TABLE FeeExemption ([FEE HEAD] NVARCHAR(50), APR INT,MAY INT,JUN INT,JUL INT,AUG INT,SEP INT,OCT INT,NOV INT,DEC INT,JAN INT,FEB INT,MAR INT,StudentId NVARCHAR(50))
--INSERT DATA
INSERT INTO FeeAssigned
SELECT 'TUITION FEE',120,120,120,120,120,120,120,120,120,120,120,120,'STUD01'
UNION ALL
SELECT 'TRAN FEE',50,50,50,50,50,50,50,50,50,50,50,50,'STUD01'
INSERT INTO FeeExemption
SELECT 'TUITION FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
UNION ALL
SELECT 'TRAN FEE',10,10,10,10,10,10,10,10,10,10,10,10,'STUD01'
What I have tried:
<br /> CREATE PROC GetFeeStructureForStud(@StudentId NVARCHAR(50))<br /> AS<br /> BEGIN<br /> SELECT FA.StudentId,FA.[FEE HEAD],<br /> FA.APR - ISNULL(FET.APR,0) AS APR,<br /> FA.MAY - ISNULL(FET.MAY,0) AS MAY,<br /> FA.JUN - ISNULL(FET.JUN,0) AS JUN,<br /> FA.JUL - ISNULL(FET.JUL,0) AS JUL,<br /> FA.AUG - ISNULL(FET.AUG,0) AS AUG,<br /> FA.SEP - ISNULL(FET.SEP,0) AS SEP,<br /> FA.OCT - ISNULL(FET.OCT,0) AS OCT,<br /> FA.NOV - ISNULL(FET.NOV,0) AS NOV,<br /> FA.DEC - ISNULL(FET.DEC,0) AS DEC,<br /> FA.JAN - ISNULL(FET.JAN,0) AS JAN,<br /> FA.FEB - ISNULL(FET.FEB,0) AS FEB,<br /> FA.MAR - ISNULL(FET.MAR,0) AS MAR<br /> FROM FeeAssigned FA LEFT JOIN <br /> (SELECT FE.StudentId,FE.[FEE HEAD],<br /> SUM(FE.APR) AS APR,<br /> SUM(FE.MAY) AS MAY,<br /> SUM(FE.JUN) AS JUN,<br /> SUM(FE.JUL) AS JUL,<br /> SUM(FE.AUG) AS AUG,<br /> SUM(FE.SEP) AS SEP,<br /> SUM(FE.OCT) AS OCT,<br /> SUM(FE.NOV) AS NOV,<br /> SUM(FE.DEC) AS DEC,<br /> SUM(FE.JAN) AS JAN,<br /> SUM(FE.FEB) AS FEB,<br /> SUM(FE.MAR) AS MAR <br /> FROM FeeExemption FE <br /> GROUP BY FE.StudentId,FE.[FEE HEAD]) AS FET ON (FA.StudentId=FET.StudentId) AND (FA.[FEE HEAD]=FET.[FEE HEAD])<br /> WHERE FA.StudentId=@StudentId<br /> END <br /> <br />
解决方案
Try this query .I am not handle null please handle null when you will come to know that it actually works for you.Happy coding :)
select FA.StudentId,SUM(FA.APR-FE.APR) As APR,SUM(FA.MAY-FE.MAY) As MAY,SUM(FA.JUN-FE.JUN)As JUN,SUM(FA.JUL-FE.JUL) As JUL ,SUM(FA.AUG-FE.AUG) As AUG,SUM(FA.SEP-FE.SEP) As SEP,SUM(FA.OCT-FE.OCT) As OCT,SUM(FA.NOV-FE.NOV) As NOV,SUM(FA.DEC-FE.DEC) As DEC ,SUM(FA.JAN-FE.JAN) As JAN,SUM(FA.FEB-FE.FEB) As FEB,SUM(FA.MAR-FE.MAR) As MAR from FeeAssigned FA inner join FeeExemption FE on FA.StudentId=FE.StudentId AND FA.[FEE HEAD]=FE.[FEE HEAD] group by FA.StudentId
And your result will be
StudentId APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR STUD01 150 150 150 150 150 150 150 150 150 150 150 150
这篇关于如何显示下面提到的SQL存储过程--- SQL服务器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文