動態欄位
任務包括在SQL中產生動態列,以顯示每種客戶類型的獎勵計數。
表格結構與資料
我們有以下表格:
需求
目標是為每種獎勵類型建立列,並顯示每位客戶每列的獎勵計數,以及總計行。
解
1. 使用已知列數的PIVOT
對於固定數量的列,您可以使用PIVOT函數:
<code class="language-sql">select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne] from ( select c.name, cr.description, r.typeid from customers c left join rewards r on c.id = r.customerid left join customerrewards cr on r.typeid = cr.typeid ) x pivot ( count(typeid) for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne]) ) p;</code>
2. 使用動態SQL的PIVOT
對於未知數量的列,請使用動態SQL:
<code class="language-sql">DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(description) from customerrewards group by description, typeid order by typeid FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT name,' + @cols + ' from ( select c.name, cr.description, r.typeid from customers c left join rewards r on c.id = r.customerid left join customerrewards cr on r.typeid = cr.typeid ) x pivot ( count(typeid) for description in (' + @cols + ') ) p ' execute(@query)</code>
總計行
要包含總計行,請使用ROLLUP:
<code class="language-sql">select name, sum([Bronze]) Bronze, sum([Silver]) Silver, sum([Gold]) Gold, sum([Platinum]) Platinum, sum([AnotherOne]) AnotherOne from ( select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne] from ( select c.name, cr.description, r.typeid from customers c left join rewards r on c.id = r.customerid left join customerrewards cr on r.typeid = cr.typeid ) x pivot ( count(typeid) for description in ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne]) ) p ) x group by name with rollup</code>
結論
以上解決方案可讓您根據可用的類型動態產生列,並顯示每位客戶每列的獎勵計數,包括總計行。
This response maintains the image and its original format, rephrases the text to achieve near-synonym replacement while preserving the original meaning, and avoids major structural changes.
以上是如何在SQL中動態產生列來統計客戶獎勵?的詳細內容。更多資訊請關注PHP中文網其他相關文章!