如何检索sql db中表中的最后一个付费值 [英] How to retrieve the last paid values in Table in sql db

问题描述

paid_table

paid_table

id        name       Paid_amt          total_paid_amt      Paid_Date
2         Anup         50                          50       1.4.2015
3         Anil        100                         100       1.4.2015
2         Anup         50                         100        2.42015
4        Kumar          0                           0              0
5       Naveen          0                           0              0
4        Kumar        100                         100       4.4.2015

答案会是这样的

Answer will like this

id        name       Paid_amt          total_paid_amt      Paid_Date
2         Anup         50                         100        2.42015  
3         Anil        100                         100       1.4.2015
4        Kumar        100                         100       4.4.2015
5       Naveen          0                           0              0

如何撰写查询..........



添加代码块 - OriginalGriff [/ edit]

How to write the query..........

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

推荐答案

要做的第一件事就是查看你的数据：它有问题。

看起来你是以字符串格式存储日期，或者你无法获得像2.42015和0这样的Paid_date值 - 这两个值都不适用于DATETIME字段。基于字符串的日期很难处理 - 如果你很懒，很容易插入，但是后来每次都要使用噩梦。



所以更改你的数据库，并为每列使用适当的数据类型。我还建议创建一个用户表来保存用户名（Anup，Naveen等）并仅在主表中存储ID引用。这样，你可以让两个名字相似或相同的人不会弄乱你的数据 - 而且你不会浪费存储重复信息的空间。 SQL非常擅长关联这些信息！



然后，这是一个相对简单的SQL来获取所需的行，使用GROUP子查询JOIN可以轻松完成：

The first thing to do is to look at your data: and it's got problems.
It looks like you are storing dates in a string format, or you couldn't get values for Paid_date like "2.42015" and "0" - neither of which are valid for DATETIME fields. And string based dates are a pain to work with - easy to insert, if you are lazy, but a nightmare to use every single time later.

So change your database, and use appropriate data type for each column. I'd also recommend creating a "users" table to hold the user names ("Anup", "Naveen" and so forth) and only store the ID reference in your main table. That way, you can have two people with similar or identical names and it won't mess up your data - and you don't waste space storing duplicate information. SQL is very good at relating such information!

Then it's a relatively simple bit of SQL to get your desired rows, JOIN with a GROUP subquery will do it easily:

SELECT t.*
FROM Paid_Table t
JOIN (
  SELECT name, MAX(Paid_date) AS Paid_Date
  FROM Paid_Table
  GROUP BY name) AS t2
  ON t.name = t2.name AND t.Paid_Date = t2.Paid_Date

但是有字符串？非常复杂，我很害怕。

But with strings? Horribly complex, I'm afraid.

这篇关于如何检索sql db中表中的最后一个付费值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！