首頁 >資料庫 >mysql教程 >如何在SQL中動態產生列來統計客戶獎勵?

如何在SQL中動態產生列來統計客戶獎勵?

Susan Sarandon
Susan Sarandon原創
2025-01-09 15:21:40635瀏覽

How to Dynamically Generate Columns in SQL to Count Customer Rewards?

SQL中動態建立列

動態欄位

任務包括在SQL中產生動態列,以顯示每種客戶類型的獎勵計數。

表格結構與資料

我們有以下表格:

  • 客戶 (客戶ID,姓名)
  • 客戶獎勵 (類型ID,描述)
  • 獎勵 (獎勵ID,類型ID,客戶ID)

需求

目標是為每種獎勵類型建立列,並顯示每位客戶每列的獎勵計數,以及總計行。

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中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn