时间戳

首页 > 解决方案 > 如何在缺少逗号和数据的情况下进行拆分

sql - 如何在缺少逗号和数据的情况下进行拆分

问题描述

我的数据图像

set rsGetAcc = server.createobject("adodb.recordset")
sSQL = "select accno, referral, "
sSQL = sSQL & "refer1, refer2, refer3, refer4, refer5, refer6 from member "
sSQL = sSQL & "where refer3 = '" & session("username") & "' "
sSQL = sSQL & "and refer4 = '' "
sSQL = sSQL & "and type = 'CSH' "
rsGetAcc.open sSQL, conn
if not rsGetAcc.eof then
     set rstChkBalance = server.CreateObject("ADODB.RecordSet")
     sSQL = "SELECT ifnull(sum(if(sign='+',amount,0)),0) as TotMBCashLmtDB FROM trns "
     **sSQL = sSQL & "WHERE accno IN ('" & rsGetAcc("accno") & "') "**
     sSQL = sSQL & "AND type = 'ASD' "
     sSQL = sSQL & "AND api = '' "
     rstChkBalance.Open sSQL, conn, 3, 3
     if not rstChkBalance.eof then
         dTotMBCashLmtDB = rstChkBalance("TotMBCashLmtDB")
     end if
end if
pCloseTables(rsGetAcc)

我的数据库显示了两个 accno 但我的 rsGetaAcc("accno") 在sSQL = sSQL & "WHERE accno IN ('" & rsGetAcc("accno") & "')中只有 1 个 accno

请教我如何获得 2 个 accno 以及如何拆分为 ("TKSS1S1MB2", "TKSS1S1M4")

谢谢你。

标签： sqlasp.net

解决方案

 set rstChkBalance = server.CreateObject("ADODB.RecordSet")
 sSQL = "SELECT ifnull(sum(if(sign='+',amount,0)),0) as TotMBCashLmtDB FROM trns "
 sSQL = sSQL & "WHERE accno IN (select accno from member "
 sSQL = sSQL & "where refer3 = '" & session("username") & "' "
 sSQL = sSQL & "and refer4 = '' "
 sSQL = sSQL & "and type = 'CSH') "
 sSQL = sSQL & "AND type = 'ASD' "
 sSQL = sSQL & "AND api = '' "

请注意，我已将第一个记录集的查询包含在IN第二个记录集的子句中。

推荐阅读