如何在SQL Server中加入并获得结果 [英] How to join and get result in SQL server

问题描述

我有两个sql表Fee_Payable_to_Students和Fee_Receipt_Students



Fee_Payable_to_学生的价值为

 S_Adm_No |学生| FheadId | FHead |四月|五月|君| 
 1001 |拉梅什| 1 |入场费| 25000 | 0 | 25000 | 
 1001 |拉梅什| 2 |年费| 6600 | 0 | 6600 | 
 1001 |拉梅什| 3 |学费| 1900 | 1900 | 1900 | 

Fee_Receipt_Students

 S_Adm_No |学生| FheadId | FHead |四月|五月|君| 
 1001 |拉梅什| 1 |入场费| 25000 | 0 | 25000 | 
 1001 |拉梅什| 2 |年费| 6600 | 0 | 6600 | 

现在我要显示为

 S_Adm_No |学生| FheadId | FHead |四月|五月|君| Q1 | R_Amt | Bal | 
 1001 |拉梅什| 1 |入场费| 25000 | 0 | 25000 | 50000 | 63200 | 5700 | 
 1001 |拉梅什| 2 |年费| 6600 | 0 | 6600 | 13200 | 63200 | 5700 | 
 1001 |拉梅什| 3 |学费| 1900 | 1900 | 1900年| 5700 | 63200 | 5700 | 

注意：R_Amt来自Fee_Receipt_Students作为别名



我的代码如下。有些请帮忙...



代码块添加 - OriginalGriff [/ edit]



我尝试了什么：

 选择 
 P.S_Adm_No，P.S_Name，P.fheadid，P.FHead，P.apr，P.may，P.Jun，
（P.apr + P.may + P.Jun） Q1，
 SUM（R.Apr + R.May + R.Jun）R_Amt 
  FROM  
 Fee_Payable_to_Students  AS  P  LEFT   JOIN  Fee_Receipt_Students  AS  R 
  ON  P.S_Adm_no = R.S_Adm_no  AND  P .cls_SecId = R.cls_SecId 
 其中 P.S_Adm_No = '  1001' 
  group   by  
 P. S_NAME，P.fheadid，P.S_Adm_No，P.apr，P.ma y，P.Jun，P.FHead，R.S_Adm_No 
  order   by  P.S_Adm_No 

解决方案

个人？我会抛弃这个地段并重新设计数据库。

这不是一个有效的设计 - 它会复制信息，限制你三个月，并且通常会让你的生活变得困难。

而是设置一个Students表，其中包含StudentID（我假设是S_Adm_no），名称和任何其他相关信息（地址，电子邮件，手机，其他联系方式等）

然后添加一个FeeTypes表，其中包含FeeID（假设为FheadId）和Description（FHead）。

然后设置一个Transactions表，其中包含TransactionID，Date，StudentID，FeeID和Value

现在，当费用到期时，它会被添加到具有正值的交易中。

当学生付款到达时，它会被添加到具有负值的交易中。

这是一个相对简单的事情，提取你需要的信息，而不是你添加的荒谬数量的GROUP BY子句。

OriginalGriff是对的，转储那个你的数据库。 Cos'他们根本不是数据库表，更像是excel表。但数据库表永远不会出类拔萃。尝试操作这些表时会遇到很多问题。事实上，你已经做到了。因此，开始学习关系数据库的正确设计方法=> 数据库设计简介 [ ^ ]和数据库规范化中的1NF，2NF，3NF和BCNF DBMS教程| Studytonight [ ^ ]

I have two sql table Fee_Payable_to_Students and Fee_Receipt_Students

Fee_Payable_to_Students has value as

S_Adm_No | Student | FheadId     |FHead            |    Apr | May | Jun   |
 1001    | Ramesh  |  1          | Admission Fee   |  25000 |   0 | 25000 |
 1001    | Ramesh  |  2          | Annual Fee      |   6600 |   0 |  6600 |
 1001    | Ramesh  |  3          | Tuition Fee     |   1900 |1900 |  1900 |

Fee_Receipt_Students

S_Adm_No | Student | FheadId     |FHead            |    Apr | May | Jun   |
 1001    | Ramesh  |  1          | Admission Fee   |  25000 |   0 | 25000 |
 1001    | Ramesh  |  2          | Annual Fee      |   6600 |   0 |  6600 |

Now I want to display as

S_Adm_No| Student | FheadId |FHead          |    Apr | May | Jun   | Q1  |R_Amt|Bal  |
 1001   | Ramesh  |  1      | Admission Fee |  25000 |   0 | 25000 |50000|63200|5700 |
 1001   | Ramesh  |  2      | Annual Fee    |   6600 |   0 |  6600 |13200|63200|5700 |
 1001   | Ramesh  |  3      | Tuition Fee   |   1900 |1900 |  1900 | 5700|63200|5700 |

Note: R_Amt comes from Fee_Receipt_Students as alias

my code is below. Some please help...

[edit]Code block added - OriginalGriff[/edit]

What I have tried:

select	
		P.S_Adm_No,P.S_Name	,P.fheadid,P.FHead,P.apr,P.may,P.Jun, 
		(P.apr+P.may+P.Jun)Q1, 
		SUM(R.Apr+R.May+R.Jun) R_Amt 
FROM	
		Fee_Payable_to_Students AS P LEFT JOIN Fee_Receipt_Students AS R 
		ON P.S_Adm_no = R.S_Adm_no AND P.cls_SecId = R.cls_SecId 
where	P.S_Adm_No='1001' 
group by 
		P.S_Name,P.fheadid,P.S_Adm_No,P.apr,P.may,P.Jun,P.FHead,R.S_Adm_No    
order by P.S_Adm_No

解决方案

Personally? I'd dump the lot and redesign the database.
That's not an efficient design - it duplicates info, restricts you to three specific months, and generally makes your life difficult.
Instead, set up a Students table, which contains the StudentID (which I assume is the S_Adm_no), name and any other relevant info (address, email, mobile, other contact details, etc.)
Then add a FeeTypes table, containing the FeeID (FheadId I assume) and Description (FHead).
Then set up a Transactions table, containing a TransactionID, Date, StudentID, FeeID, and Value
Now when a fee becomes due, it's added to the Transactions with a positive Value.
When a student payment arrives, it's added to the Transactions with a negative Value.
It's then a relatively simple matter to extract the info you need without the ridiculous number of GROUP BY clauses you are adding.

OriginalGriff is right, dump that database of yours. Cos' they are not database tables at all, more like excel sheets. But database tables are never excel sheets. You will encountered a lot of problems trying to manipulate these kind of tables. In fact, you already did. So start learning the correct way of design a relational database => Introduction to database design[^] and 1NF, 2NF, 3NF and BCNF in Database Normalization | DBMS Tutorial | Studytonight[^]

这篇关于如何在SQL Server中加入并获得结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！