sql - sybase For 循环，包含对从 select 语句获得的每个结果的查询

Question

SELECT distinct(a.acct_num)
FROM customer_acct a,
customer_acct_history b LIKE "%000%"    
WHERE a.acct_num *= b.acct_num AND acct_type='C'

基于这个查询的输出，它返回了很多客户帐号。我正在尝试为每个帐号执行一个选择查询，返回前 1 个。我打算在第二个 sql 中链接第一个查询的 o/p，就像 a.acct_num=( output of first Query ) 一样。我想循环执行此操作并为每个帐号选择前 1 个。任何有关如何进行的帮助将不胜感激

Answer 1

是CURSOR使用的好地方:

declare curs cursor
for 
SELECT distinct(a.acct_num)
FROM customer_acct a,
customer_acct_history b LIKE "%000%"    
WHERE a.acct_num *= b.acct_num AND acct_type='C'

OPEN curs 

DECLARE @acct_num INT

fetch curs into @acct_num

/* now loop, processing all the rows
** @@sqlstatus = 0 means successful fetch
** @@sqlstatus = 1 means error on previous fetch
** @@sqlstatus = 2 means end of result set reached
*/
while (@@sqlstatus != 2)
BEGIN
   SELECT Sometning FROM somwhere where @acct_num = ...
   fetch curs into @acct_num
END

GL!