在循环内构建选择列表

发布于 2024-09-07 15:38:21 字数 1834 浏览 5 评论 0原文

如何不在循环内使用查询的查询来解决必须为每一行构建选择列表的问题？

在此示例中，每个客户都有一个该客户特有的操作列表：

<cfquery name="qryAction" datasource="myDataSource">
SELECT ActionID,CustID,ActionName FROM AvailableActions
</cfquery>
<cfquery name="qryOrderHeader" datasource="myDataSource">
SELECT CustID FROM OrderHeader
</cfquery>
<html>
<body>
<cfform preservedata="yes">
    <cfloop query="qryOrderHeader">
        <cfquery name="qry3" dbtype="query">
        SELECT ActionID,ActionName FROM qryAction
        WHERE CustID = #qryOrderHeader.CustID#
        </cfquery>
        <cfselect name="ActionID" query="qry3" display="ActionName" value="ActionID" />
    </cfloop>
</cfform>
</body>
</html>

以下是 SQL（如果它有助于说明我的示例）。

    use tempdb
    GO
    create table Cust(
    CustID Int Identity Primary Key,
    CustName Varchar(255)
    )
    GO
    INSERT INTO Cust(CustName) values('One')
    INSERT INTO Cust(CustName) values('Two')
    GO
    create table AvailableActions(
    ActionID Int Identity Primary Key,
    CustID Int,
    ActionName Varchar(255)
    )
    GO
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Insert')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Edit')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Delete')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Insert')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Edit')
    GO
    CREATE TABLE OrderHeader(
    OrderHeaderID Int Identity Primary Key,
    CustID Int
    )
    INSERT INTO OrderHeader(CustID) VALUES(1)
    INSERT INTO OrderHeader(CustID) VALUES(2)
    INSERT INTO OrderHeader(CustID) VALUES(2)

我想最好的结果是在 qryOrderHeader 中包含 ActionID 和 ActionName。

原文

How do you NOT use a query of queries inside a loop to solve the problem of having to build a select list for each row?

In this example, every customer has a list of actions unique to that customer:

<cfquery name="qryAction" datasource="myDataSource">
SELECT ActionID,CustID,ActionName FROM AvailableActions
</cfquery>
<cfquery name="qryOrderHeader" datasource="myDataSource">
SELECT CustID FROM OrderHeader
</cfquery>
<html>
<body>
<cfform preservedata="yes">
    <cfloop query="qryOrderHeader">
        <cfquery name="qry3" dbtype="query">
        SELECT ActionID,ActionName FROM qryAction
        WHERE CustID = #qryOrderHeader.CustID#
        </cfquery>
        <cfselect name="ActionID" query="qry3" display="ActionName" value="ActionID" />
    </cfloop>
</cfform>
</body>
</html>

Here's the SQL if it helps illustrate my example.

    use tempdb
    GO
    create table Cust(
    CustID Int Identity Primary Key,
    CustName Varchar(255)
    )
    GO
    INSERT INTO Cust(CustName) values('One')
    INSERT INTO Cust(CustName) values('Two')
    GO
    create table AvailableActions(
    ActionID Int Identity Primary Key,
    CustID Int,
    ActionName Varchar(255)
    )
    GO
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Insert')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Edit')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(1,'Delete')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Insert')
    INSERT INTO AvailableActions(CustID,ActionName) VALUES(2,'Edit')
    GO
    CREATE TABLE OrderHeader(
    OrderHeaderID Int Identity Primary Key,
    CustID Int
    )
    INSERT INTO OrderHeader(CustID) VALUES(1)
    INSERT INTO OrderHeader(CustID) VALUES(2)
    INSERT INTO OrderHeader(CustID) VALUES(2)

I suppose the best result would be to include ActionID and ActionName in qryOrderHeader.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

画中仙 2024-09-14 15:38:21

通过使用 cfoutput 和 group 属性来手动构建选择列表（而不是使用 cfselect）非常简单：

<cfquery name="qryAction" datasource="myDataSource">
    SELECT ActionID,CustID,ActionName 
    FROM AvailableActions
    ORDER BY CustID
</cfquery>

<cfoutput query="qryAction" group="CustID">
    <select name="actionid">
    <cfoutput>
        <option value="#val(ActionID)#">#htmlEditFormat(ActionName)#</option>
    </cfoutput>
    </select>
</cfoutput>

请注意，您需要将 CustID 指定为第一个（或唯一）排序依据，以使嵌套的 cfoutput 正常工作。没有测试过，所以可能有错别字。

It's pretty straightforward to build the select lists manually (instead of using cfselect) by using cfoutput with the group attribute:

<cfquery name="qryAction" datasource="myDataSource">
    SELECT ActionID,CustID,ActionName 
    FROM AvailableActions
    ORDER BY CustID
</cfquery>

<cfoutput query="qryAction" group="CustID">
    <select name="actionid">
    <cfoutput>
        <option value="#val(ActionID)#">#htmlEditFormat(ActionName)#</option>
    </cfoutput>
    </select>
</cfoutput>

Note you need to specify the CustID as the first (or only) order by in order to make the nested cfoutputs work correctly. Not tested, so there may be typos.

回复收藏 0 原文

~没有更多了~