如何编写Query以从数据库中获取单个记录？ [英] How to write a Query to get single record from Database?

问题描述

我有一个表有多个记录，具有相同的字段，相同的值，但只有一个字段不同。 VoucherNoVoucherNo在两条记录中有不同的值，如9007和9852我想查询以获得具有最大值VoucherNo的记录。



我的查询是喜欢这个

I have a table which has multiple records with the same fields, same values, but only one field is different. "VoucherNo" VoucherNo has different values in two records like "9007" and "9852" I want to query to get record which has max value VoucherNo.

My Query is Like This

select a.userid as [UserId],c.fullname as [Name],CONVERT(VARCHAR(11),a.voucherdate,106) as [VoucherDate],CONVERT(VARCHAR(11),
b.regdate,106) as [JoiningDate],b.rankno as [Rank],d.rankname as [RankName],b.branchuserid as [Branch],c.address as [Address],
a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],a.sc as [AdminCharges],a.levelamount 
as [LevelAmount] from vouchers a join users b on a.userid=b.userid join profiles c on b.accountid=c.accountid join ranks d 
on b.rankno=d.rankno where a.voucherdate >= '11/01/2013' and a.voucherdate <= '12/01/2013' and 
a.userid in ('V2W645346','V2W545647','V2W853227','V2W394436','V2W255329','V2W965239')

这里如果我使用MAX（）然后它将只返回单个值，我想要为不同的用户记录，但条件是它应该向每个用户返回一条记录，其中VoucherNo中没有最大值，而不是多个。所以请帮我解决这个问题。





提前感谢。

Here if I use MAX() then it will return only single value, I want record for different users, but condition is it should return one record to each user which has max no in VoucherNo, not multiple. So please help me to solve this problem.


Thank in advance.

推荐答案

最通用的方法有多种：

There are multiple ways the most generic is :

select top 1 * from ( select ... ) ; your query in the parenthesis 

您还可以使用 LIMIT 如果您的数据库支持它。 http://www.w3schools.com/sql/sql_top.asp [ ^ ]

You can also use LIMIT if your database supports it. http://www.w3schools.com/sql/sql_top.asp[^]

如果你想要用户ID智能（最大） （VoucherNo））数据，在VoucherNo和userid中使用group by

If you want the userid wise(max(VoucherNo)) data ,use group by in "VoucherNo" and "userid "

试试这个

Try this

select  a.userid as [UserId],c.fullname as [Name],a.[VoucherDate],CONVERT(VARCHAR(11),
b.regdate,106) as [JoiningDate],b.rankno as [Rank],d.rankname as [RankName],b.branchuserid as [Branch],c.address as [Address],
a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],a.sc as [AdminCharges],a.levelamount 
as [LevelAmount] 
from users b 
	join (select  a.userid as [UserId],CONVERT(VARCHAR(11),a.voucherdate,106) as [VoucherDate],
				a.voucherno,a.tds as [TDS],a.othercharges as [OtherCharges],a.netamount as [NetAmount],
				a.sc as [AdminCharges],a.levelamount as [LevelAmount], 
				ROW_NUMBER() over (partition by userid order by VoucherNo desc) as Row_no
				from vouchers as a) as a on a.userid=b.userid and Row_no=1 
	Join profiles c on b.accountid=c.accountid join ranks d 
on b.rankno=d.rankno 
where a.voucherdate >= '11/01/2013' and a.voucherdate <= '12/01/2013' and 
a.userid in ('V2W645346','V2W545647','V2W853227','V2W394436','V2W255329','V2W965239')

这篇关于如何编写Query以从数据库中获取单个记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！