我不知道如何进行查询..... [英] i don't know how to make query.....

问题描述

我有两个表.
一个是
截面表
____________

id名称类部分
1 sankar VI A
2盘点VI A
3 raja III C

第二个是
paid_table
_____________

id sectionid paid_amount余额
100 1 500 200
101 1 200 0
102 2 1000 500
103 2 200 300

我需要的是


名称平衡

101 0
103300


-------------------------------------------
我应该怎么做才能生成查询....

(我需要为VI类A部分学生提供最后一笔余额交易.)

i have two table.
one is
section table
____________

id name class section
1 sankar VI A
2 pandian VI A
3 raja III C

second one is
paid_table
_____________

id sectionid paid_amount Balance
100 1 500 200
101 1 200 0
102 2 1000 500
103 2 200 300

i need is


name balance

101 0
103 300


-------------------------------------------
what should i do for generate query....

( i need last transaction of balance for VI class A section student. )

推荐答案

就在我头顶，没有经过测试

Just off the top of my head, not tested

SELECT Name, Balance
FROM section
JOIN paid_table ON paid_table.sectionid = section.id
WHERE paid_table.id = (SELECT MAX(id) FROM paid_table WHERE sectionid = section.id)

您也可以这样编写:

You could also write it in this way:

SELECT Name, Balance FROM section, paid_table
WHERE paid_table.sectionid = section.id AND 
paid_table.id = (SELECT MAX(id) FROM paid_table WHERE sectionid = section.id)

我喜欢使用RANK()函数.
我已经成功地使用它提高了性能，尤其是当您必须处理非常大的表时.

I love to use the RANK() function.
I have been successful in improving performance using it, especially if you have to deal with very large tables.

SELECT t.Name, t.Balance
FROM
	(
	SELECT	s.Name AS Name
		, p.Balance As Balance
		, RANK() OVER (PARTITION BY s.name ORDER BY p.id DESC) as rowNum
	FROM section s
	INNER JOIN paid_table p
	ON p.sectionid = s.id
	) t
Where t.rowNum=1

这篇关于我不知道如何进行查询.....的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！