顧客の報酬をカウントするために SQL で列を動的に生成するにはどうすればよいですか?

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>

結論

上記のソリューションを使用すると、利用可能なタイプに基づいて列を動的に生成し、合計行を含む各顧客の列ごとの報酬数を表示できます。

この応答は、画像とその元の形式を維持し、元の意味を維持しながらほぼ同義語の置換を実現するためにテキストを言い換え、大きな構造の変更を回避します。

以上が顧客の報酬をカウントするために SQL で列を動的に生成するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。