mysql - 考虑到与其关联的项目数量，如何使列跨越一行

Question

我在显示包含不同列的表格时遇到问题。

最后一列应跨越与其关联的每个项目(或行)。我的代码在 ColdFusion 11 上运行，但当我在 ColdFusion 8 上尝试时，它会抛出错误。

错误从“cfscript”(queryAddColumn)开始。这是下面的代码。我修改了它。原始代码在这里( How Can I Output a Remark Column with rowspan Without Duplicates for same group in Coldfusion (Re formated) )..

<cfquery datasource="ysr" name="report">
    SELECT p.*, p.quantity as quantity, pt.paintcode as paintCode, pt.producttypeid, pt.grouptypeid, pt.paintcolor, pt.painttypeid, pt.mastercode, pp.producttypename as productTypeName, pts.paintype as paintType, pt.*, l.litrename as litreName, l.litreid, pt.none
    FROM purchase p, paint pt, producttype pp, painttype pts, litre l
    WHERE p.transactionid = #transactionid#
    AND p.paintid = pt.paintid
    AND pt.producttypeid = pp.producttypeid
    AND pt.painttypeid = pts.painttypeid
    AND pt.litreid = l.litreid 
    ORDER BY pp.producttypename, pts.paintype ASC

</cfquery>


<!--- add groupRowspan and groupTotalQuantity columns --->
<cfscript>
  queryAddColumn(report, "groupRowspan", "integer", []);
  queryAddColumn(report, "groupTotalQuantity", "integer", []);
  if(report.RecordCount) {
      lastQueryRowToUpdate = 0;
      lastProductType = lastPaintType = lastLitrename = lastgrouptypeid = "";
      groupRowspan = 0;
      groupTotalQuantity = 0;
      for(rowNum=1; rowNum<=report.RecordCount; rowNum++) {
          if((report.productTypeName[rowNum] is not lastProductType) or (report.paintType[rowNum] is not lastPaintType)  or (report.litrename[rowNum] is not lastlitrename) or (report.grouptypeid[rowNum] is not lastgrouptypeid) ) {
              if(lastQueryRowToUpdate) {
                  querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
                  querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
              }
              lastQueryRowToUpdate = rowNum;
              lastProductType = report.productTypeName[rowNum];
              lastPaintType = report.paintType[rowNum];
              lastLitrename = report.litrename[rowNum];
              lastGrouptypeid = report.grouptypeid[rowNum];
              groupRowspan = 0;
              groupTotalQuantity = 0;
          }
          groupRowspan++;
          if(isValid("integer", report.quantity[rowNum])) {
              groupTotalQuantity += report.quantity[rowNum];
          }
          if((rowNum is report.RecordCount) and lastQueryRowToUpdate) {
              querySetCell(report, "groupRowspan", groupRowspan, lastQueryRowToUpdate);
              querySetCell(report, "groupTotalQuantity", groupTotalQuantity, lastQueryRowToUpdate);
          }
      }
  }
</cfscript>

这是输出的html代码

<table id="items" bgcolor="">

         <a name="afteradding"> 
          <tr bgcolor="#ccccee">
              <th>ITEM</th>
              <th>QUANTITY</th>
              <th  class="blank" colspan="3">DESCRIPTION</th>
              <th>LITRES</th>
              <th>REMARKS</th>
          </tr>
          </a>



             <cfloop  query="report">
            <tr class="item-row">


                    <cfoutput>

            <th>#report.CurrentRow#</th>


            <th>#report.quantity#</th>

             <th colspan="3" class="description"><span><Cfif #grouptypeid# eq "">#report.productTypeName#</Cfif> <cfif #grouptypeid# neq ""><cfelse>#report.paintType# </cfif>#report.paintColor# <Cfif #grouptypeid# eq "">#report.paintCode#</Cfif> <cfif #grouptypeid# neq ""><cfelse>#report.litrename#</cfif></span></th>
             <cfif isValid("integer", report.groupRowspan)>

            <th rowspan="#report.groupRowspan#"><cfif #grouptypeid# neq ""><cfelse>#report.litrename#</cfif></th>

            <cfquery datasource="ysr" name="ysroo">
                SELECT grouptypename
                FROM grouptype
                WHERE grouptypeid = '#grouptypeid#'
            </cfquery>

        <th rowspan="#report.groupRowspan#">#report.groupTotalQuantity#<cfif #grouptypeid# eq ""> <cfif #report.litreid# eq 1>Drums<cfelse>Gallons</cfif> of #report.productTypeName# #report.paintType#</cfif>  <cfif #grouptypeid# neq ""> #ysroo.grouptypename# of #paintcolor#</cfif></th>
            </cfif>
                </cfoutput>
          </tr>
            </cfloop>

    <!---     <tr id="hiderow">
            <td colspan="5"><a id="addrow" href="javascript:;" title="Add a row">Add a row</a></td>
          </tr>

          <tr>
              <td colspan="2" class="blank"> </td>
              <td colspan="2" class="total-line">Subtotal</td>
              <td class="total-value"><div id="subtotal">$875.00</div></td>
          </tr>
          <tr>

              <td colspan="2" class="blank"> </td>
              <td colspan="2" class="total-line">Total</td>
              <td class="total-value"><div id="total">$875.00</div></td>
          </tr>
          <tr>
              <td colspan="2" class="blank"> </td>
              <td colspan="2" class="total-line">Amount Paid</td>

              <td class="total-value"><textarea id="paid">$0.00</textarea></td>
          </tr>
    --->

        <cfquery datasource="ysr" name="chktranbs">
            SELECT COUNT(purchaseid) purchase
            FROM purchase
            WHERE transactionid = #transactionid#
        </cfquery>



        </table>

Answer 1

乍一看，有两件事与 CF 9 不兼容(你很幸运 - 我今天早上碰巧在拖延，所以用这个来解决很方便)。

首先是 super 天才大师@Leigh 已经向您指出的数组构造函数。所以这个:

  queryAddColumn(report, "groupRowspan", "integer", []);

需要替换为:

  queryAddColumn(report, "groupRowspan", "integer", ArrayNew(1));

第二个跳转的是聚合集合语句。我相信这个语法:

 lastProductType = lastPaintType = lastLitrename = lastgrouptypeid = "";

...从 CF9 开始可能是新的。试试这个:

 lastProductType = "";
lastPaintType = "";
lastLitrename = "";
lastgrouptypeid = "";

我相信操作数 (<=) 和增量器 (++, +=) 是在 CF8 中引入的。如果是的话，他们可能没问题——但我可能是错的。