在查询中有这么多查询我很困惑 [英] so many queries within queries I'm confused

问题描述

第一个抱歉leangth ...不能再削减了。


我希望输出是

组织320000

基金100004

计划777777

账户1 7234.55

账户2 -347.99

账户3 823.55

计划888888

账户1 8745.99

账户2 -9878.33

..... on and on。


< b>我可以上班的是< / b>

组织320000

基金100004

计划777777

账户1 7234.55


组织320000

基金100004

计划777777

账户2 -347.99

.........开启和关闭。


下面的代码看起来不错但是组织，基金，计划编号

永远不会改变。我一直在看这两天

天......帮助！


< table border =" 0">

<％

sSQL3 =" SELECT Distinct Organization" &安培; _

"来自 &安培; TheYear& _

" WHERE MonthPD =''" &安培; ThePD& "''"

设置rs3 = Connect.Execute（sSQL3）

％>

< tr>

< td> < font color ="＃0000FF"> Organization< / font>< / td>

< td colspan =" 10"><％= rs3（""组织"）％>< / td>

< / tr>

<％直到rs3.eof％>

<％

sSQL2 =" SELECT Distinct Fund" &安培; _

"来自 &安培; TheYear& _

" WHERE MonthPD =''" &安培; ThePD& "''"

设置rs2 = Connect.Execute（sSQL2）

％>

< tr>

< td> < font color ="＃0000FF"> Fund< / font>< / td>

< td colspan =" 10"><％= rs2（""基金"）％>< / td>

< / tr>

<％直到rs2.eof％>

<％

sSQL4 =" SELECT Distinct Program，Fund，Organization" &安培; _

"来自 &安培; TheYear& _

" WHERE MonthPD =''" &安培; ThePD& "" &安培; _

" ORDER BY组织，基金，计划

set rs4 = Connect.Execute（sSQL4）

TheProg = rs4（" Program）

％>

< tr>

< td> < font color ="＃0000FF"> Program< / font> < / td>

< td colspan =" 10"><％= TheProg％>< / td>

< / tr>

<％直到rs4.eof％>

< tr>

< td>帐户< / div>< / td>

< td><％= TheAct％> < / td>

< td><％= TotalAmt％>< / td>

< / tr>

<％

TheFund = rs4（" Fund"）

TheOrg = rs4（" Organization"）

TheProg = rs4（ 程序）

SQL =" SELECT *" &安培; _

" FROM" &安培; TheYear& _

" WHERE MonthPD =''" &安培; ThePD& "" &安培; _

"和组织=''" &安培; TheOrg& "" &安培; _

"和Fund =''" &安培;基金会& "" &安培; _

"和Program =''" &安培; TheProg& "" &安培; _

" ORDER BY Account"

set rs = Connect.Execute（sSQL）

TotalAmt = 0

直到rs.eof

TheAct = rs（" Account"）

TotalAmt = TotalAmt + rs（" PdAmount"）

Response.Flush

rs.MoveNext

循环


Response.Flush

rs4.MoveNext

循环


Response.Flush

rs2.MoveNext

循环


回应。同花顺

rs3.MoveNext

循环％>

< / table>

1st sorry about leangth...couldn''t really cut anymore.

I want the output to be
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55

Program 888888
Account1 8745.99
Account2 -9878.33
.....on and on.

<b>What I can get to work is</b>
Organization 320000
Fund 100004
Program 777777
Account1 7234.55

Organization 320000
Fund 100004
Program 777777
Account2 -347.99
.........on and on.

The code below looks right but the Organization, Fund, Program numbers
never change when they should. I''ve been looking at this for two
days...help!

<table border="0">
<%
sSQL3 = "SELECT Distinct Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''"
set rs3 = Connect.Execute(sSQL3)
%>
<tr>
<td> <font color="#0000FF">Organization </font></td>
<td colspan="10"><%=rs3("Organization")%></td>
</tr>
<% Do until rs3.eof %>
<%
sSQL2 = "SELECT Distinct Fund" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''"
set rs2 = Connect.Execute(sSQL2)
%>
<tr>
<td> <font color="#0000FF">Fund </font></td>
<td colspan="10"><%=rs2("Fund")%></td>
</tr>
<% Do until rs2.eof %>
<%
sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''" & _
" ORDER BY Organization, Fund, Program"
set rs4 = Connect.Execute(sSQL4)
TheProg = rs4("Program")
%>
<tr>
<td> <font color="#0000FF">Program</font> </td>
<td colspan="10"><%=TheProg%></td>
</tr>
<% Do until rs4.eof %>
<tr>
<td>Account</div></td>
<td><%=TheAct%> </td>
<td><%=TotalAmt%></td>
</tr>
<%
TheFund = rs4("Fund")
TheOrg = rs4("Organization")
TheProg = rs4("Program")
SQL = "SELECT *" & _
"FROM " & TheYear & _
"WHERE MonthPD = ''" & ThePD & "''" & _
" and Organization = ''" & TheOrg & "''" & _
" and Fund = ''" & TheFund & "''" & _
" and Program = ''" & TheProg & "''" & _
" ORDER BY Account"
set rs = Connect.Execute(sSQL)
TotalAmt=0
Do until rs.eof
TheAct = rs("Account")
TotalAmt = TotalAmt + rs("PdAmount")

Response.Flush
rs.MoveNext
Loop

Response.Flush
rs4.MoveNext
Loop

Response.Flush
rs2.MoveNext
Loop

Response.Flush
rs3.MoveNext
Loop %>
</table>

推荐答案

您可以使用单个记录集执行此操作（请参阅 http：// www.aspfaq.com/2241 ）。

我会让HTML非常漂亮。


<％

sql =" SELECT Organization，Fund，Program，Account，PdAmount" &安培; _

" FROM" &安培; TheYear& _

"按组织，基金，计划，账户排序"


cOrg =""：cFund =""：cProg =""：cAcc =""


设置rs = conn.execute（sql）

而不是rs.eof

nOrg = rs（ 0）：nFund = rs（1）：nProg = rs（2）

nAcc = rs（3）：nPd = rs（4）


if nOrg<> cOrg然后''new org

response.write"< p>组织：" &安培; nOrg

cOrg = nOrg

结束如果


如果nFund<> cFund然后''新基金

response.write"< br>基金：" &安培; nFund

cFund = nFund

结束如果


如果nProg<> cProg然后

response.write"< br>程序：" &安培; nProg

cProg = nProg

结束如果


response.write"< br>" &安培; nAcc& "：" &安培; nPd


rs.movenext

循环

％>


你的桌子真的以年命名？这是一个可怕的设计，你真的

应该有一张桌子并且有一年的专栏。你真的搞乱了关系设计和实体建模的整个想法......


-
http://www.aspfaq.com/

（反向回复地址。 ）


" Abby Lee" < AB ******* @ hotmail.com>在消息中写道

news：80 ************************** @ posting.google.c om ...

You can do this with a single recordset (see http://www.aspfaq.com/2241 ).
I''ll leave the HTML pretty-ifying up to you.

<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"

cOrg = "": cFund = "": cProg = "": cAcc = ""

set rs = conn.execute(sql)

do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)

if nOrg <> cOrg then '' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg
end if

if nFund <> cFund then '' new fund
response.write "<br>Fund:" & nFund
cFund = nFund
end if

if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if

response.write "<br>" & nAcc & ":" & nPd

rs.movenext
loop
%>

Are your tables really named by year? This is horrible design, you really
should have ONE table and have a column for year. You''re really messing up
the whole idea of relational design and entity modeling...

--
http://www.aspfaq.com/
(Reverse address to reply.)


"Abby Lee" <ab*******@hotmail.com> wrote in message
news:80**************************@posting.google.c om...

第一个抱歉leangth ......真的不能再削减了。

我希望输出结构是
组织320000
基金100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55

程序888888
Account1 8745.99
Account2 -9878.33 ....等等。

< b>我可以上班的是< / b>
组织320000
基金100004
程序777777
账号1 7234.55

组织320000
基金100004
程序777777
账号2 -347.99
........等等。

下面的代码看似正确，但组织，基金，计划编号从未改变。我一直在看这两天......帮忙！

< table border =" 0">
<％
sSQL3 =" SELECT Distinct Organization" &安培; _
来自 &安培; TheYear& _
WHERE MonthPD =''" &安培; ThePD& "''"
设置rs3 = Connect.Execute（sSQL3）
％>
< tr>
< td> < font color ="＃0000FF"> Organization< / font>< / td>
< td colspan =" 10"><％= rs3（" Organization"）％ >< / td>
< / tr>
<％直到rs3.eof％>
<％
sSQL2 =" SELECT Distinct Fund" &安培; _
来自 &安培; TheYear& _
WHERE MonthPD =''" &安培; ThePD& "''"
设置rs2 = Connect.Execute（sSQL2）
％>
< tr>
< td> < font color ="＃0000FF"> Fund< / font>< / td>
< td colspan =" 10"><％= rs2（" Fund"）％ >< / td>
< / tr>
<％直到rs2.eof％>
<％
sSQL4 =" SELECT Distinct Program，基金，组织 &安培; _
来自 &安培; TheYear& _
WHERE MonthPD =''" &安培; ThePD& "" &安培; _
ORDER BY组织，基金，计划
设置rs4 = Connect.Execute（sSQL4）
TheProg = rs4（程序）
％>
< tr>
< td> < font color ="＃0000FF"> Program< / font> < / td>
< td colspan =" 10"><％= TheProg％>< / td>
< / tr>
<％Do直到rs4.eof％>
< tr>
< td>帐户< / div>< / td>
< td><％= TheAct％> < / td>
< td><％= TotalAmt％>< / td>
< / tr>
<％
TheFund = rs4（ 基金）
TheOrg = rs4（组织）
TheProg = rs4（Program）
SQL =" SELECT *" &安培; _
" FROM" &安培; TheYear& _
WHERE MonthPD =''" &安培; ThePD& "" &安培; _
和组织=''" &安培; TheOrg& "" &安培; _
和Fund =''" &安培;基金会& "" &安培; _
和Program =''" &安培; TheProg& "" &安培; _
ORDER BY Account"
set rs = Connect.Execute（sSQL）
TotalAmt = 0
直到rs.eof
TheAct = rs（账户）
TotalAmt = TotalAmt + rs（PdAmount）

Response.Flush
rs.MoveNext
循环

Response.Flush
rs4 .MoveNext
循环

Response.Flush
rs2.MoveNext
循环

Response.Flush
rs3.MoveNext
循环％>
< / table>

1st sorry about leangth...couldn''t really cut anymore.

I want the output to be
Organization 320000
Fund 100004
Program 777777
Account1 7234.55
Account2 -347.99
Account3 823.55

Program 888888
Account1 8745.99
Account2 -9878.33
....on and on.

<b>What I can get to work is</b>
Organization 320000
Fund 100004
Program 777777
Account1 7234.55

Organization 320000
Fund 100004
Program 777777
Account2 -347.99
........on and on.

The code below looks right but the Organization, Fund, Program numbers
never change when they should. I''ve been looking at this for two
days...help!

<table border="0">
<%
sSQL3 = "SELECT Distinct Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''"
set rs3 = Connect.Execute(sSQL3)
%>
<tr>
<td> <font color="#0000FF">Organization </font></td>
<td colspan="10"><%=rs3("Organization")%></td>
</tr>
<% Do until rs3.eof %>
<%
sSQL2 = "SELECT Distinct Fund" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''"
set rs2 = Connect.Execute(sSQL2)
%>
<tr>
<td> <font color="#0000FF">Fund </font></td>
<td colspan="10"><%=rs2("Fund")%></td>
</tr>
<% Do until rs2.eof %>
<%
sSQL4 = "SELECT Distinct Program, Fund, Organization" & _
" FROM " & TheYear & _
" WHERE MonthPD = ''" & ThePD & "''" & _
" ORDER BY Organization, Fund, Program"
set rs4 = Connect.Execute(sSQL4)
TheProg = rs4("Program")
%>
<tr>
<td> <font color="#0000FF">Program</font> </td>
<td colspan="10"><%=TheProg%></td>
</tr>
<% Do until rs4.eof %>
<tr>
<td>Account</div></td>
<td><%=TheAct%> </td>
<td><%=TotalAmt%></td>
</tr>
<%
TheFund = rs4("Fund")
TheOrg = rs4("Organization")
TheProg = rs4("Program")
SQL = "SELECT *" & _
"FROM " & TheYear & _
"WHERE MonthPD = ''" & ThePD & "''" & _
" and Organization = ''" & TheOrg & "''" & _
" and Fund = ''" & TheFund & "''" & _
" and Program = ''" & TheProg & "''" & _
" ORDER BY Account"
set rs = Connect.Execute(sSQL)
TotalAmt=0
Do until rs.eof
TheAct = rs("Account")
TotalAmt = TotalAmt + rs("PdAmount")

Response.Flush
rs.MoveNext
Loop

Response.Flush
rs4.MoveNext
Loop

Response.Flush
rs2.MoveNext
Loop

Response.Flush
rs3.MoveNext
Loop %>
</table>

嗯，这里有一点点逻辑，例如如果您有两个组织

使用相同的程序或两个程序使用相同的帐户等。您可以通过将子项设置为"来解决这个问题。无论何时你进入新的等级，例如


<％

sql =" SELECT Organization，Fund，Program，Account，PdAmount" &安培; _

" FROM" &安培; TheYear& _

"按组织，基金，计划，账户排序"


cOrg =""：cFund =""：cProg =""：cAcc =""


设置rs = conn.execute（sql）

而不是rs.eof

nOrg = rs（ 0）：nFund = rs（1）：nProg = rs（2）

nAcc = rs（3）：nPd = rs（4）


if nOrg<> cOrg然后''new org

response.write"< p>组织：" &安培; nOrg

cOrg = nOrg：cFund =""：cProg =""

end if


if nFund <> cFund然后''新基金

response.write"< br>基金：" &安培; nFund

cFund = nFund：cProg =""

end if


if nProg<> cProg然后

response.write"< br>程序：" &安培; nProg

cProg = nProg

结束如果


response.write"< br>" &安培; nAcc& "：" &安培; nPd


rs.movenext

loop

％>


-
http://www.aspfaq.com/

（反向地址回复。）

Well, there''s a little bit of logic to fit here, e.g. if you have two orgs
with the same program or two programs with the same account, etc. You can
solve that by setting children to "" whenever you hit a new tier, e.g.

<%
sql = "SELECT Organization, Fund, Program, Account, PdAmount" & _
"FROM " & TheYear & _
" ORDER BY Organization, Fund, Program, Account"

cOrg = "": cFund = "": cProg = "": cAcc = ""

set rs = conn.execute(sql)

do while not rs.eof
nOrg = rs(0): nFund = rs(1): nProg = rs(2)
nAcc = rs(3): nPd = rs(4)

if nOrg <> cOrg then '' new org
response.write "<p>Organization:" & nOrg
cOrg = nOrg: cFund="": cProg=""
end if

if nFund <> cFund then '' new fund
response.write "<br>Fund:" & nFund
cFund = nFund: cProg=""
end if

if nProg <> cProg then
response.write "<br>Program:" & nProg
cProg = nProg
end if

response.write "<br>" & nAcc & ":" & nPd

rs.movenext
loop
%>

--
http://www.aspfaq.com/
(Reverse address to reply.)

Aaron，


你是最甜蜜的事......我可以吻你。


Abby

Aaron,

You are the sweetest thing...I could just kiss you.

Abby

这篇关于在查询中有这么多查询我很困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！