如何捕获前20个单元
(MS SQL)我正在尝试编写一个查询，该查询将捕获来自新办公室的前20笔贷款。我办公室的贷款号码是9位数(100100001)。前4位是办公室标识符，其余5位是发放的贷款总额。因此，如果贷款文件的贷款号为100800015，这意味着它来自office 1008，并且是该office发起的第15个文件。
目前，我有以下问题：
SELECT
l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM
Loan.LoanQA AS l
WHERE
l.FundedDate BETWEEN '6/01/2014' AND '6/30/2014'
AND l.LoanType = 'FHA'我如何调整上面的内容，以便能够在特定的时间范围内从任何办公室捕获前20个单元(即，月)？因此，前4个字符将变得非常重要，因为不同的办公室是不同的。
我不想调整WHERE子句中的前4位数字(分支ID)。我只是想输入一个日期范围，如果有新的贷款(最后5位)，我希望它捕获前20个单位。
回答 3
Stack Overflow用户
发布于 2014-07-17 23:54:40
查询取决于您使用的数据库引擎，但通常您将使用substring函数来确定贷款办公室，然后包含一个where子句来匹配您正在查找的办公室：
SELECT TOP 20 -- only the first 20
l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM
Loan.LoanQA AS l
WHERE
l.FundedDate BETWEEN '6/01/2014' AND '6/30/2014'
AND l.LoanType = 'FHA'
AND SUBSTRING(l.LoanNumber, 1, 4) = '1001' -- only office 1001
ORDER BY l.FundedDate上面的查询是针对MS SQL Server的。
如果您使用的是不同的数据库引擎，则可能必须使用其他方法来限制行。例如，对于MySQL，您可以使用LIMIT 1, 20
Stack Overflow用户
发布于 2014-07-17 23:56:00
因为您没有指定DBMS，所以这是我能提供的最好的帮助。
SQL Server：如果您的数据库管理系统是SQL Server，那么您可以使用TOP这样做
SELECT TOP 20
l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM Loan.LoanQA AS l
WHERE l.FundedDate BETWEEN '6/01/2014' AND '6/30/2014'
AND l.LoanType = 'FHA'
AND SUBSTRING(CONVERT(NVARCHAR(10),l.LoanNumber), 1, 4) = '1234'
ORDER BY l.FundedDateMySQL：如果您的数据库管理系统是MySQL，那么您可以使用LIMIT这样做
SELECT l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM Loan.LoanQA AS l
WHERE l.FundedDate BETWEEN '6/01/2014' AND '6/30/2014'
AND l.LoanType = 'FHA'
AND SUBSTRING(Convert(l.LoanNumber, char(10)), 1, 4) = '1234'
ORDER BY l.FundedDate
LIMIT 20Oracle
如果您的数据库管理系统是Oracle，则可以通过使用ROWNUM来执行此操作
SELECT l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM Loan.LoanQA AS l
WHERE l.FundedDate BETWEEN '6/01/2014' AND '6/30/2014'
AND l.LoanType = 'FHA'
AND ROWNUM <= 20希望这能有所帮助！
Stack Overflow用户
发布于 2014-07-18 21:55:18
我能够回答我自己的问题，下面是MS SQL SMS中的查询。我相信外面有人能够相信WHERE子句。
SELECT
l.FundedDate,
l.LoanNumber,
l.BorrowerLastName,
l.LoanType
FROM
Loan.LoanQA AS l
WHERE
l.FundedDate BETWEEN '1/01/2014' AND '6/30/2014'
AND(l.LoanNumber LIKE '%00001'
OR l.LoanNumber LIKE '%00002'
OR l.LoanNumber LIKE '%00003'
OR l.LoanNumber LIKE '%00004'
OR l.LoanNumber LIKE '%00005'
OR l.LoanNumber LIKE '%00006'
OR l.LoanNumber LIKE '%00007'
OR l.LoanNumber LIKE '%00008'
OR l.LoanNumber LIKE '%00009'
OR l.LoanNumber LIKE '%00010'
OR l.LoanNumber LIKE '%00011'
OR l.LoanNumber LIKE '%00012'
OR l.LoanNumber LIKE '%00013'
OR l.LoanNumber LIKE '%00014'
OR l.LoanNumber LIKE '%00015'
OR l.LoanNumber LIKE '%00016'
OR l.LoanNumber LIKE '%00017'
OR l.LoanNumber LIKE '%00018'
OR l.LoanNumber LIKE '%00019'
OR l.LoanNumber LIKE '%00020')
ORDER BY
l.BranchIDhttps://stackoverflow.com/questions/24808095
复制相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号