ماهي - الجدول المحوري MySQL




ماهي فائدة pivot table (6)

إذا كان لدي جدول MySQL ابحث عن شيء من هذا القبيل:

company_name    action  pagecount
-------------------------------
Company A       PRINT   3
Company A       PRINT   2
Company A       PRINT   3
Company B       EMAIL   
Company B       PRINT   2
Company B       PRINT   2
Company B       PRINT   1
Company A       PRINT   3

هل من الممكن تشغيل استعلام MySQL للحصول على مخرجات كهذه:

company_name    EMAIL   PRINT 1 pages   PRINT 2 pages   PRINT 3 pages
-------------------------------------------------------------
CompanyA        0       0               1               3
CompanyB        1       1               2               0

والفكرة هي أن عدد pagecount يمكن أن يختلف ، لذا يجب أن يعكس مقدار عمود الخرج ذلك ، عمود واحد لكل زوج action / عدد pagecount وعدد الزيارات بعد ذلك لكل company_name . لست متأكدًا مما إذا كان هذا يسمى جدول محوري ولكن أحدهم اقترح ذلك؟


إصدار stardard-SQL باستخدام منطق منطقي :

SELECT company_name
     , COUNT(action = 'EMAIL' OR NULL) AS "Email"
     , COUNT(action = 'PRINT' AND pagecount = 1 OR NULL) AS "Print 1 pages"
     , COUNT(action = 'PRINT' AND pagecount = 2 OR NULL) AS "Print 2 pages"
     , COUNT(action = 'PRINT' AND pagecount = 3 OR NULL) AS "Print 3 pages"
FROM   tbl
GROUP  BY company_name;

SQL كمان.

ماذا؟

يعطي TRUE TRUE OR NULL TRUE .
FALSE OR NULL تعطي القيمة NULL .
يعطي NULL OR NULL NULL .
وتعتمد COUNT فقط القيم غير الفارغة. فويلا.


الجواب الصحيح هو:

select table_record_id,
group_concat(if(value_name='note', value_text, NULL)) as note
,group_concat(if(value_name='hire_date', value_text, NULL)) as hire_date
,group_concat(if(value_name='termination_date', value_text, NULL)) as termination_date
,group_concat(if(value_name='department', value_text, NULL)) as department
,group_concat(if(value_name='reporting_to', value_text, NULL)) as reporting_to
,group_concat(if(value_name='shift_start_time', value_text, NULL)) as shift_start_time
,group_concat(if(value_name='shift_end_time', value_text, NULL)) as shift_end_time
from other_value
where table_name = 'employee'
and is_active = 'y'
and is_deleted = 'n'
GROUP BY table_record_id

بالنسبة إلى MySQL ، يمكنك وضع الشروط مباشرة في الدالة SUM() وسيتم تقييمها على أنها منطقية 0 أو 1 وبالتالي يمكن أن يكون لديك حساب استنادًا إلى المعايير الخاصة بك دون استخدام عبارات IF/CASE

SELECT
    company_name,  
    SUM(action = 'EMAIL')AS Email,
    SUM(action = 'PRINT' AND pagecount = 1)AS Print1Pages,
    SUM(action = 'PRINT' AND pagecount = 2)AS Print2Pages,
    SUM(action = 'PRINT' AND pagecount = 3)AS Print3Pages
FROM t
GROUP BY company_name

DEMO


بالنسبة إلى المحور الديناميكي ، استخدم GROUP_CONCAT مع CONCAT . تقوم دالة GROUP_CONCAT السلاسل من مجموعة في سلسلة واحدة بخيارات متنوعة.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN action = "',
      action,'"  AND ', 
           (CASE WHEN pagecount IS NOT NULL 
           THEN CONCAT("pagecount = ",pagecount) 
           ELSE pagecount IS NULL END),
      ' THEN 1 ELSE 0 end) AS ',
      action, IFNULL(pagecount,'')

    )
  )
INTO @sql
FROM
  t;

SET @sql = CONCAT('SELECT company_name, ', @sql, ' 
                  FROM t 
                   GROUP BY company_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

عرض هنا


هناك أداة تسمى MySQL Pivot table generator ، يمكنها مساعدتك على إنشاء جدول محوري يستند إلى الويب يمكنك تصديره لاحقًا إلى excel (إذا أردت). يمكن أن تعمل إذا كانت البيانات الخاصة بك في جدول واحد أو في العديد من الجداول.

كل ما عليك القيام به هو تحديد مصدر البيانات للأعمدة (يدعم الأعمدة الديناميكية) ، والصفوف ، والقيم في نص الجدول وعلاقة الجدول (إذا كان هناك أي منها)

الصفحة الرئيسية لهذه الأداة هي http://mysqlpivottable.net


select t3.name, sum(t3.prod_A) as Prod_A, sum(t3.prod_B) as Prod_B, sum(t3.prod_C) as    Prod_C, sum(t3.prod_D) as Prod_D, sum(t3.prod_E) as Prod_E  
from
(select t2.name as name, 
case when t2.prodid = 1 then t2.counts
else 0 end  prod_A, 

case when t2.prodid = 2 then t2.counts
else 0 end prod_B,

case when t2.prodid = 3 then t2.counts
else 0 end prod_C,

case when t2.prodid = 4 then t2.counts
else 0 end prod_D, 

case when t2.prodid = "5" then t2.counts
else 0 end prod_E

from 
(SELECT partners.name as name, sales.products_id as prodid, count(products.name) as counts
FROM test.sales left outer join test.partners on sales.partners_id = partners.id
left outer join test.products on sales.products_id = products.id 
where sales.partners_id = partners.id and sales.products_id = products.id group by partners.name, prodid) t2) t3

group by t3.name ;




pivot