ماهي - الجدول المحوري 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;
ماذا؟
يعطي 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 ;
