cfoutput组价格(cfoutput group prices)

高性能WEB开发 IT屋
问 题

i want to group prices according to products id, like here: http://s44.radikal.ru/i106/1108/57/33380d0557f4.jpg but i can't group them properly, there are must be one product and few prices, rather than same products and different prices for each. so far with the help of Scott Stroz, i have wrote the code:

the query:

<cfquery name="get_products" datasource="#dsn3#">
    SELECT DISTINCT P.PRODUCT_ID,P.PRODUCT_NAME,PS.MONEY,PR.PRICE
    FROM PRODUCT P
        JOIN PRICE_STANDART PS ON P.PRODUCT_ID = PS.PRODUCT_ID
        JOIN PRICE PR ON P.PRODUCT_ID = PR.PRODUCT_ID
    WHERE P.IS_SALES=1 AND P.IS_PURCHASE=1 AND P.IS_INTERNET=1 AND PS.PURCHASESALES=1 AND PS.PRICESTANDART_STATUS=1 
    <cfif len(trim(attributes.product_cat)) and len(attributes.product_code)>AND P.PRODUCT_CODE LIKE '#attributes.product_code#%'</cfif>         
    <cfif isdefined('attributes.product_id') and len(attributes.product_id)>AND P.PRODUCT_ID=#attributes.product_id#</cfif>
    GROUP BY P.PRODUCT_ID,PR.PRICE,P.PRODUCT_NAME,PS.MONEY
    ORDER BY PR.PRICE DESC
</cfquery>

and the table:

<table cellpadding="3" cellspacing="1" class="color-border" width="100%">
    <tr class="color-header">
        <td width="30" class="header_bold">No</td>
        <td><b>Ürün</b></td>
        <td class="header_bold" width="80">Liste fiyatı</td>
        <td class="header_bold" width="80">Bayı 1</td>
        <td class="header_bold" width="80">Bayı 2</td>
        <td class="header_bold" width="80">Bayı 3</td>
        <td class="header_bold" width="80">Bayı 4</td>
        <td class="header_bold" width="25">Para</td>
    </tr>
    <cfoutput query="get_products" startrow="#attributes.startrow#" maxrows="#attributes.maxrows#" group="product_id">
        <tr height="20" onMouseOver="this.className='color-light';" onMouseOut="this.className='color-row';" class="color-row">
            <td>#currentrow#</td>   
            <td>#product_name#</td>
            <cfoutput group="price"><td>#tlformat(price,2)#</td></cfoutput>
            <td align="center">#MONEY#</td>
        </tr>
    </cfoutput>
</table>

thank you all guys for help!

解决方案

Assuming you have a query that looks like this:

product_id, product_name, price
1, 'test', 100
1, 'test', 200
1, 'test', 300
2, 'test2', 100
2, 'test2', 200
2, 'test2', 300

The following code would group by product id and output each individual price

<cfoutput query="get_products" group="product_id">
    #product_name#
    Prices:
    <cfoutput>
        #price#<br>
    </cfoutput>
</cfoutput>

Now, the group by in sql has nothing to do with coldfusions group in cfoutput. It's a convinience method to loop out unique rows. So when coldfusion sees two identical "product_id" it will run the nested cfoutput loop.

You can do this for as many levels you want, but in your case there only seems to be two.

本文地址:IT屋 » cfoutput group prices

问 题

我要根据产品ID对价格进行分组,如下所示: http:/ /s44.radikal.ru/i106/1108/57/33380d0557f4.jpg 但我不能正确分组,有必须有一个产品和几个价格,而不是相同的产品和不同的价格为每个。到目前为止,在Scott Stroz的帮助下,我写了代码:



查询:



 < cfquery name =“get_products”datasource =“#dsn3#”> 
SELECT DISTINCT P.PRODUCT_ID,P.PRODUCT_NAME,PS.MONEY,PR.PRICE
FROM PRODUCT P
JOIN PRICE_STANDART PS ON P.PRODUCT_ID = PS.PRODUCT_ID
加入价格ON P.PRODUCT_ID = PR.PRODUCT_ID
WHERE P.IS_SALES = 1 AND P.IS_PURCHASE = 1 AND P.IS_INTERNET = 1 AND PS.PURCHASESALES = 1 AND PS.PRICESTANDART_STATUS = 1
< cfif len (trim(attributes.product_cat))和​​len(attributes.product_code)> AND P.PRODUCT_CODE LIKE'#attributes.product_code#%'< / cfif>
< cfif isdefined('attributes.product_id')and len(attributes.product_id)> AND P.PRODUCT_ID =#attributes.product_id#< / cfif>
GROUP BY P.PRODUCT_ID,PR.PRICE,P.PRODUCT_NAME,PS.MONEY
ORDER BY PR.PRICE DESC
< / cfquery>


和表格:



 < table cellpadding =“3”cellspacing =“1”class =“color-border”width =“100%”> 
< tr class =“color-header”>
< td width =“30”class =“header_bold”>否< / td>
< td>< b>Ürün< / b>< / td>
< td class =“header_bold”width =“80”> Listefiyatı< / td>
< td class =“header_bold”width =“80”>Bayı1< / td>
< td class =“header_bold”width =“80”>Bayı2< / td>
< td class =“header_bold”width =“80”>Bayı3< / td>
< td class =“header_bold”width =“80”>Bayı4< / td>
< td class =“header_bold”width =“25”> Para< / td>
< / tr>
< cfoutput query =“get_products”startrow =“#attributes.startrow#”maxrows =“#attributes.maxrows#”group =“product_id”>
< tr height =“20”onMouseOver =“this.className ='color-light';” onMouseOut =“this.className ='color-row';” class =“color-row”>
< td>#currentrow#< / td>
< td>#product_name#< / td>
< cfoutput group =“price”>< td> #tlformat(price,2)#< / td>< / cfoutput&
< td align =“center”>#MONEY#< / td>
< / tr>
< / cfoutput>
< / table>


感谢大家的帮助!


解决方案

假设您有一个如下所示的查询:



  product_id,product_name,price 
1,'test',100
1,'test',200
1,'test',300
2,'test2',100
2, 'test2',200
2,'test2',300


按产品ID输出每个单独的价格



 < cfoutput query =“get_products”group =“product_id”> 
#product_name#
价格:
< cfoutput>
#price#< br>
< / cfoutput>
< / cfoutput>


现在,sql中的group by与cfoutput中的coldfusions组无关。这是一个convinience方法循环出独特的行。所以当coldfusion看到两个相同的“product_id”它将运行嵌套的cfoutput循环。



你可以为你想要的任何级别,但在你的情况下,为两个。


本文地址:IT屋 » cfoutput组价格