Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL untuk Mengira Ganjaran Pelanggan?

Buat lajur dalam SQL secara dinamik

Lajur dinamik

Tugas termasuk menjana lajur dinamik dalam SQL untuk memaparkan kiraan ganjaran bagi setiap jenis pelanggan.

Struktur jadual dan data

Kami mempunyai borang berikut:

• Pelanggan (ID pelanggan, nama)
• Ganjaran Pelanggan (ID Jenis, Perihalan)
• Ganjaran (ID ganjaran, ID jenis, ID pelanggan)

Permintaan

Matlamatnya adalah untuk membuat lajur bagi setiap jenis ganjaran dan memaparkan kiraan ganjaran bagi setiap pelanggan setiap lajur, bersama-sama dengan jumlah baris.

Penyelesaian

1. Gunakan PIVOT dengan bilangan lajur yang diketahui

Untuk bilangan lajur yang tetap, anda boleh menggunakan fungsi 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. PIVOT menggunakan SQL dinamik

Untuk bilangan lajur yang tidak diketahui, gunakan SQL dinamik:

<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>

Jumlah Baris

Untuk memasukkan jumlah baris, gunakan 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>

Kesimpulan

Penyelesaian di atas membolehkan anda menjana lajur secara dinamik berdasarkan jenis yang tersedia dan memaparkan kiraan ganjaran setiap lajur untuk setiap pelanggan, termasuk jumlah baris.

Respons ini mengekalkan imej dan format asalnya, menyusun semula teks untuk mencapai penggantian hampir sinonim sambil mengekalkan makna asal dan mengelakkan perubahan struktur yang besar.

Atas ialah kandungan terperinci Bagaimana untuk Menjana Lajur secara Dinamik dalam SQL untuk Mengira Ganjaran Pelanggan?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!