如果值相同则对它们进行 sql 分组

Question

我编写了一个sql-server代码和coldfusion表，其中包含产品和价格，每种产品有5个不同的价格，所有价格都正确显示，虽然它们彼此不同，但如果产品5个价格相同，则将它们分组作为一个。无论如何，希望从代码和屏幕截图中一切都会清楚。

这是sql：

SELECT P.PRODUCT_ID,
       P.PRODUCT_NAME,
       PS.MONEY,
       PR.PRICE,
       P.BRAND_ID,
       PS.PRICE,
       GSL.PRODUCT_STOCK,
       GSL.PURCHASE_ORDER_STOCK,
       GSL.SALEABLE_STOCK,
       P.PRODUCT_DETAIL2,
       P.BARCOD
FROM PRODUCT P
JOIN PRICE_STANDART PS ON P.PRODUCT_ID = PS.PRODUCT_ID
JOIN PRICE PR ON P.PRODUCT_ID = PR.PRODUCT_ID
JOIN #DSN2_ALIAS#.GET_STOCK_LAST GSL ON P.PRODUCT_ID = GSL.PRODUCT_ID
WHERE PS.PURCHASESALES=1 AND 
      PS.PRICESTANDART_STATUS=1 AND
      P.IS_SALES=1 AND
      P.IS_INTERNET=1 AND
      P.IS_EXTRANET=1
GROUP BY 
      P.PRODUCT_ID,
      PR.PRICE,
      P.PRODUCT_NAME,
      PS.MONEY,
      P.BRAND_ID,
      PS.PRICE,
      GSL.PRODUCT_STOCK,
      GSL.PURCHASE_ORDER_STOCK,
      GSL.SALEABLE_STOCK,
      P.PRODUCT_DETAIL2,
      P.BARCOD
ORDER BY
    P.PRODUCT_ID, PR.PRICE DESC

和表：

<tr class="color-header">
        <td width="170"><b>Ürün</b></td>
        <td class="header_bold"><b>Açıklama 2</b></td>
        <td class="header_bold" width="100"><b>Marka</b></td>
        <cfif isdefined('attributes.department_id') and len(attributes.department_id)>
        <td class="header_bold" width="35"><b>Depo Stok</b></td>
        <cfelse>
            <td class="header_bold" width="35"><b>Stok</b></td>
            <td class="header_bold" width="50"><b>Satabilen Stok</b></td>
            <td class="header_bold" width="35"><b>Yoldaki Stok</b></td>
        </cfif>
        <cfif (session.ep.admin is 1) or (session.ep.userid is 19)>
            <td class="header_bold" width="70">Dön. Sonu B.Maliyet $</td>
        </cfif>
        <td class="header_bold" width="90">Liste Fiatı</td>
        <td class="header_bold" <cfif isdefined('get_company.companycat_id') and len(get_company.companycat_id) and (get_company.companycat_id eq 6)>style="color:yellow;font-size:9pt;background:#000;"</cfif> width="80">Bayi 1</td>
        <td class="header_bold" <cfif isdefined('get_company.companycat_id') and len(get_company.companycat_id) and (get_company.companycat_id eq 7)>style="color:yellow;font-size:9pt;background:#000;"</cfif> width="80">Bayi 2 <br /> 12 Ay 0 faiz</td>
        <td class="header_bold" <cfif isdefined('get_company.companycat_id') and len(get_company.companycat_id) and (get_company.companycat_id eq 8)>style="color:yellow;font-size:9pt;background:#000;"</cfif> width="80">Bayi 3 <br /> 30-60-90 çek</td>
        <td class="header_bold" <cfif isdefined('get_company.companycat_id') and len(get_company.companycat_id) and (get_company.companycat_id eq 9)>style="color:yellow;font-size:9pt;background:#000;"</cfif> width="80">Bayi 4 Tek Çekim Peşin</td>
        <cfif (session.ep.admin is 1) or (session.ep.userid is 19)>
        <td class="header_bold" width="60">
            <cfif isdefined('attributes.yoldaki_stock') and attributes.yoldaki_stock is 1>
                Yoldaki
            <cfelseif isdefined('attributes.department_id') and len(attributes.department_id)>
                Depodaki
            <cfelse>
                Satabilen
            </cfif>Hesapla
        </td>
        </cfif>
        <td class="header_bold" width="25">Para</td>
    </tr>

    <cfoutput query="get_products" startrow="#attributes.startrow#" maxrows="#attributes.maxrows#" group="product_id">
            <cfquery name="get_maliyet" datasource="#dsn3#">SELECT PURCHASE_NET_SYSTEM_MONEY,(PURCHASE_NET_SYSTEM+PURCHASE_EXTRA_COST_SYSTEM) AS DS_MALIYET FROM PRODUCT_COST WHERE PRODUCT_ID=#PRODUCT_ID#</cfquery><cfif len(get_maliyet.ds_maliyet)><cfset toplam_maliyet=get_maliyet.ds_maliyet+toplam_maliyet></cfif>
            <tr height="20" onMouseOver="this.className='color-light';" onMouseOut="this.className='color-row';" class="color-row"> 
                <td>
                    <a href="/index.cfm?fuseaction=product.form_upd_product&pid=#product_id#" style="color:blue;" target="_blank">#left(product_name,25)#</a>
                </td>
                <td>#left(PRODUCT_DETAIL2,45)#</td>
                <td align="center"><cfif len(brand_list)>#get_brands.brand_name\[listfind(brand_list,brand_id,',')\]#</cfif></td>
                <cfif isdefined('attributes.department_id') and len(attributes.department_id)>
                    <td align="center">#product_stock2#</td>
                <cfelse>
                    <td align="center">#PRODUCT_STOCK#</td>
                    <td align="center">#saleable_stock#</td>
                    <td align="center">#purchase_order_stock#</td>
                </cfif>
                <cfif (session.ep.admin is 1) or (session.ep.userid is 19)>
                    <td align="center"><cfif len(GET_MALIYET.DS_MALIYET)>#tlformat(GET_MALIYET.DS_MALIYET/get_money.rate2,2)#<cfelse>0</cfif></td>
                </cfif>
                <cfoutput>
                    <td align="center">#tlformat(price,2)#</td>
                </cfoutput>
            <cfif (session.ep.admin is 1) or (session.ep.userid is 19)>
                <td align="center"><a href="javascript://" onclick="gizle_goster(abr#currentrow#);" style="font-weight:bold;"><cfif isdefined('attributes.yoldaki_stock') and attributes.yoldaki_stock is 1>x #purchase_order_stock#<cfelseif isdefined('attributes.department_id') and len(attributes.department_id)>x #product_stock2#<cfelse>x #saleable_stock#</cfif></a></td>
            </cfif>
            <td align="center">#MONEY#</td>
        </tr>
</cfoutput>

在屏幕截图中，您可以快速学习和理解问题。当然，我编辑了一些代码并删除了不必要的部分。大家，谢谢大家的帮助！ ^^

Answer 1

在 SQL 查询中，您对价格字段进行分组，以便在将结果返回到 ColdFusion 之前将这些记录折叠成一条记录。从查询的 GROUP BY 子句中删除价格列，这应该得到纠正。