如何从数据库表中获取逐个记录,用于基于排名的存储过程 [英] How get One by one record from Data Base Table ,for stored procedure based on ranking
问题描述
我在MySQL中有Table包含以下列...
1 .Id int ( 11 )AI true
2 .service Id varchar ( 10 )
3 .FedTaxId varchar ( 10 )
4 .Authority varchar ( 20 )
5 .Account Int ( 11 )
喜欢这些我的桌子存在,这里我没有在创建时设置任何等级,但我有这样的要求..
1 101 12345 12-123 Nevada 589758 .9-35
2 102 12346 12-124 California 1247895555
3 103 Idaho 55555555
就像我在MySQL中有数据一样,我想设置######。# - ##的格式,就像这些内华达帐户一样,所以我只需要一个为我的存储过程一次记录,要验证状态名称是否是内华达或不基于那,我使用Concat()方法来实现目标但是在这里,我很震惊......基于排名如何获取记录,不幸的是,所有行都用尽了...如何获得只有一个记录...在存储过程中使用任何光标。
< blockquote>
根据细节我的想法y你想做的可以在sql server中完成如下。
create table test
(
Id int,
serviceId varchar(10),
FedTaxId varchar(10),
权限varchar(20),
账户Int
)
insert测试值(101,'12345','12-123','nevada',555555555),
插入测试值(102,'12346','12-124','California',1247895555) ,
插入测试(Id,Authority,Account)值(103,'Idaho',55555555)
select ID,serviceId,FedTaxId,Authority,
当authority ='Nevada'然后
(
LEFT(Convert(varchar,Account),6)+'。'+ substring(Convert(varchar,Account),7,1)+' - ' + substring(Convert(varchar,Account),8,2)
)
else转换(varchar,Account)结束为来自测试的账户
*我假设您想要结果为6digits.1digit-2digits(######。# - ##)格式
结果:
ID serviceId FedTaxId Authority Account
101 12345 12-123 Nevada 555555.5-55
102 12346 12-124 California 1247895555
103 NULL NULL Idaho 55555555
SELECT ID,serviceId,FedTaxId,Authority,
CASE WHEN authority = ' Nevada' THEN
(concat(substring(Account, 1 , 6 ), 。 ,substring(Account, 7 , 2 ), - ,子串(帐户, 9 , 1 ))
) ELSE convert (帐户, char ( 10 )) END AS 帐户
FROM test;
适用于Mysql数据库。,使用 http://sqlfiddle.com/ [ ^ ]
I have Table in MySQL consist of following columns ..
1.Id int(11) AI true
2.service Id varchar(10)
3.FedTaxId varchar(10)
4.Authority varchar(20)
5.Account Int(11)
like these my table exist,here I am not set any rank at the time of creation ,But I have requirement like these ..
1 101 12345 12-123 Nevada 589758.9-35
2 102 12346 12-124 California 1247895555
3 103 Idaho 55555555
like that I have data in MySQL,I want to set format of "######.#-##" like these for "Nevada" account Only,so I need Only One record for at a time for My stored procedure,To verify the "State Name" is "Nevada" or not Based On That ,I am using "Concat()" Method to achieve the goal but here ,I am struck..Based on Ranking How to get the record until ,all rows are Exhausted unfortunately...How get Only One By One record ...with out using any cursor inside stored procedure.
Hi,
as per the details what i think you want to do can be done as follows in sql server.
create table test ( Id int, serviceId varchar(10), FedTaxId varchar(10), Authority varchar(20), Account Int ) insert into test values (101,'12345','12-123','nevada',555555555), insert into test values (102,'12346','12-124','California',1247895555), insert into test(Id,Authority,Account) values (103,'Idaho',55555555) select ID,serviceId,FedTaxId,Authority, case when authority='Nevada' then ( LEFT(Convert(varchar,Account),6)+'.'+substring (Convert(varchar,Account),7,1)+'-'+substring (Convert(varchar,Account),8,2) ) else Convert(varchar,Account) end as Account from test
* I am assuming that you want result as 6digits.1digit-2digits(######.#-##) format
result:
ID serviceId FedTaxId Authority Account 101 12345 12-123 Nevada 555555.5-55 102 12346 12-124 California 1247895555 103 NULL NULL Idaho 55555555
SELECT ID,serviceId,FedTaxId,Authority, CASE WHEN authority='Nevada' THEN (concat(substring(Account,1,6),".",substring(Account,7,2),"-" ,substring(Account,9,1) ) )ELSE convert(Account,char(10)) END AS Account FROM test;
It's for Mysql data base.,used The http://sqlfiddle.com/[^]
这篇关于如何从数据库表中获取逐个记录,用于基于排名的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!