SQL查询按元数据值分组字段

Question

我有一个网络商店，与WOOCOMMERCE的工作，我需要一些统计数据。

我构建SQL查询，您可以在Code中找到它。

我需要一个"group by function for the pm.meta.value field“buchungsbezeichnung。

目前，每个p.posts条目都有一行。这是很多列，我必须使用excel-pivot来获得正确的统计数据。

对于每个元值"buchungsbezeichnung“，我只需要"Bankzahlung”列或"Mitgliedszahlung“列的和。

我尝试了很多，但我找不到正确的SQL查询。

请帮帮我。感谢齐格弗里德

  p.ID AS 'Order ID',
  CASE p.post_status
      WHEN 'wc-pending'    THEN 'Zahlung Ausstehend'
      WHEN 'wc-processing' THEN 'in Bearbeitung'
      WHEN 'wc-on-hold'    THEN 'Wartestellung'
      WHEN 'wc-completed'  THEN 'Best. ausgeliefert'
      WHEN 'wc-cancelled'  THEN 'Storniert'
      WHEN 'wc-refunded'   THEN 'Rückerstattet'
      WHEN 'wc-failed'     THEN 'Fehlgeschlagen'
      WHEN 'wc-ausgeliefert' THEN 'Ausgeliefert'
      WHEN 'wc-pfand' THEN 'Pfand'
      WHEN 'wc-bestellung-neu' THEN 'Bestellung Neu'
      WHEN 'wc-produzent-vorbest' THEN 'Bei Produzent vorbestellt'
      WHEN 'wc-produzent-best' THEN 'Bestellung b. Produzent'
      WHEN 'wc-ladenausgabe' THEN 'Ladenausgabe'
      WHEN 'wc-bewirtungsbeitrag' THEN 'Bewirtungsbeitrag'
      WHEN 'wc-enw-bank-gh-mi' THEN '11 W_Buchg. Bankueberw. GH'
      WHEN 'wc-enw-umbu-gh-vj' THEN '12 W_Buchg. Übertrag GH VJ'
      WHEN 'wc-enw-barz-gh-mi' THEN '13 W_Buchg. Barzahlung'
      WHEN 'wc-enw-nachl-vj' THEN '14 W_Buchg. Nachlass VJ'
      WHEN 'wc-env-bank-mb-mi' THEN '21 V_Buchg. Bankueberw. MB'
      WHEN 'wc-env-umbu-mb-vj' THEN '22 V_Buchg. Übertrag MB VJ'
      WHEN 'wc-env-bank-gh-so' THEN '23 V_Buchg. Verein Sonstiges'
      WHEN 'wc-agw-zahlung-prod' THEN '31 W_Buchg. Zahlg. RE Prod'
      WHEN 'wc-agw-umb-re-prod' THEN '31 W_Umbu. Zahlg. RE Prod Mitgl'
      WHEN 'wc-agv-austr-mitgl' THEN '32 W-Buchg. Zahlg. RZ Austritt'
      WHEN 'wc-agv-it' THEN '41 V-Buchg. Zahlg. RE IT'
      WHEN 'wc-agv-shop' THEN '42 V-Buchg. Zahlg. RE Shop'
      WHEN 'wc-agv-trans' THEN '43 V-Buchg. Zahlg. RE Trans'
      WHEN 'wc-agv-verw' THEN '44 V-Buchg. Zahlg. RE Verw' 
      WHEN 'wc-xxx-umbu-gh-mb' THEN '51 Umb. Guthaben zu MB'
      WHEN 'wc-ugw-u-zahlung-pro' THEN '52 Umb. Zahlg RE Prod d. Mitgl.'
      WHEN 'wc-gutschrift-mitgli' THEN '53 Gutschrift Mitgl.'
      WHEN 'wc-ugv-ausumb-it' THEN '61 Umb. Zahlg RE IT d. Mitgl.'
      WHEN 'wc-ugv-ausumb-shop' THEN '62 Umb. Zahlg RE Shop d. Mitgl.'
      WHEN 'wc-ugv-ausumb-trans' THEN '63 Umb. Zahlg RE Trans d. Mitgl.' 
      WHEN 'wc-ugv-ausumb-verw' THEN '64 Umb. Zahlg RE Verw d. Mitgl.' 
      WHEN 'wc-mitgliedsbeitrag' THEN '71 Ford. Mitgliedsbeitrag'
      WHEN 'wc-ford-mb-mahn' THEN '72 Ford-Mahn. Mitgliedsbeitrag'
    ELSE 'kein Status'
    END AS 'Status',
       
  MAX( CASE WHEN pm.meta_key = 'monatsverrechnung' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Monatsverrechnung',
  MAX( CASE WHEN pm.meta_key = 'bilanzgruppe' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Bilanzgruppe',
  MAX( CASE WHEN pm.meta_key = 'buchungsbezeichnung' AND p.ID = pm.post_id THEN pm.meta_value END ) AS 'Buchungsbez',
  MAX( CASE WHEN pm.meta_key = 'bilanzwert' AND p.ID = pm.post_id AND p.post_status = 'wc-ugw-u-zahlung-pro' THEN (0-pm.meta_value) END ) AS 'Bankzahlung',
  MAX( CASE WHEN pm.meta_key = 'bilanzwert' AND p.ID = pm.post_id AND p.post_status = 'wc-agw-zahlung-prod' THEN (pm.meta_value) END ) AS 'Mitgliedszahlung'
 
FROM  KtS4szE8u_posts AS p 
JOIN  KtS4szE8u_postmeta AS pm ON p.ID = pm.post_id

group by p.id

WHERE
    p.post_status  IN('wc-agw-zahlung-prod','wc-ugw-u-zahlung-pro')

Answer 1

首先，现在和将来最好都有一个查询表，其中包含wc-pending、wc-processing、wc-on-hold等不同状态，并带有相应的标题。与尝试使用ID键作为状态/标题来回填数据库相比，下面这样的事情要容易得多。

Table PostStatus (lookup table with index on postStatus column)
PostStatusID  Post_Status           Post_Heading
1             wc-pending           Zahlung Ausstehend
2             wc-processing        in Bearbeitung
3             wc-on-hold           Wartestellung
4             wc-completed         Best. ausgeliefert
5             wc-cancelled         Storniert
etc... for all other entries.
xx            wc-mitgliedsbeitrag  71 Ford. Mitgliedsbeitrag

然后，您可以加入并简化您的案例以

select
      p.ID 'Order ID',
      coalesce( ps.post_heading, 'kein Status' ) Status,
      MAX( CASE WHEN pm.meta_key = 'monatsverrechnung' 
               THEN pm.meta_value END ) AS 'Monatsverrechnung',
      MAX( CASE WHEN pm.meta_key = 'bilanzgruppe' 
               THEN pm.meta_value END ) AS 'Bilanzgruppe',
      MAX( CASE WHEN pm.meta_key = 'buchungsbezeichnung' 
                THEN pm.meta_value END ) AS 'Buchungsbez',
      MAX( CASE WHEN pm.meta_key = 'bilanzwert' 
                    AND p.post_status = 'wc-ugw-u-zahlung-pro' 
                THEN (0-pm.meta_value) END ) AS 'Bankzahlung',
      MAX( CASE WHEN pm.meta_key = 'bilanzwert' 
                    AND p.post_status = 'wc-agw-zahlung-prod' 
                THEN (pm.meta_value) END ) AS 'Mitgliedszahlung'
    from 
      KtS4szE8u_posts AS p 
         -- using the LEFT-JOIN so you can get status readable values
         LEFT JOIN PostStatus ps 
            on p.post_status = ps.post_status
         -- now join for your meta data too
         JOIN  KtS4szE8u_postmeta pm 
            ON p.ID = pm.post_id
   group by 
      p.id
   WHERE
      p.post_status IN ('wc-agw-zahlung-prod', 'wc-ugw-u-zahlung-pro' )

因为WHERE子句只提取两个可能的状态代码，所以CASE语句的其余部分将只需要这两个可能的值，其余的甚至都不会出现在输出中。通过使用查找表，您仍然可以只使用2来获得您想要的任何可读标题，或者如果您有100个不同的状态代码，则可以获得100。

至于MAX( case/when )条件，您不需要显式添加和p.id = pm.post_id，因为这已经是连接条件的一部分。

现在，回到你原来的问题。你需要计算一些东西。听起来您需要将上面的内容包装到一个外部查询中，但您需要对其进行调整。但是，如果您没有查找表，并且一开始只处理两个可能的post状态值，那么也可以简化，因为我将在下面介绍，但您应该考虑实际的查找表。

select
      p.ID 'Order ID',
      -- again, simplified CASE/WHEN since your where clause is only ever
      -- returning records for these two status conditions
      case when p.post_status = 'wc-agw-zahlung-prod'
           then '31 W_Buchg. Zahlg. RE Prod'
           else '52 Umb. Zahlg RE Prod d. Mitgl.' end Status
      MAX( CASE WHEN pm.meta_key = 'monatsverrechnung' 
               THEN pm.meta_value END ) AS 'Monatsverrechnung',
      MAX( CASE WHEN pm.meta_key = 'bilanzgruppe' 
               THEN pm.meta_value END ) AS 'Bilanzgruppe',
      MAX( CASE WHEN pm.meta_key = 'buchungsbezeichnung' 
                THEN pm.meta_value END ) AS 'Buchungsbez',
      MAX( CASE WHEN pm.meta_key = 'bilanzwert' 
                    AND p.post_status = 'wc-ugw-u-zahlung-pro' 
                THEN (0-pm.meta_value) END ) AS 'Bankzahlung',
      MAX( CASE WHEN pm.meta_key = 'bilanzwert' 
                    AND p.post_status = 'wc-agw-zahlung-prod' 
                THEN (pm.meta_value) END ) AS 'Mitgliedszahlung'
    from 
      KtS4szE8u_posts AS p 
         JOIN  KtS4szE8u_postmeta pm 
            ON p.ID = pm.post_id
   group by 
      p.id
   WHERE
      p.post_status IN ('wc-agw-zahlung-prod', 'wc-ugw-u-zahlung-pro' )

现在，为了包装，使用我在上面发布的任何一种方法

select
      sum( case when PreSumResult.Status = '31 W_Buchg. Zahlg. RE Prod'
           then 1 else 0 end ) as CountOf31Posts,
      sum( case when PreSumResult.Status = '31 W_Buchg. Zahlg. RE Prod'
           then Mitgliedszahlung else 0 end ) as SumOfMitgliedszahlung,
      sum( case when PreSumResult.Status = '52 Umb. Zahlg RE Prod d. Mitgl.'
           then 1 else 0 end ) as CountOf52Posts,
      sum( case when PreSumResult.Status = '52 Umb. Zahlg RE Prod d. Mitgl.'
           then Bankzahlung else 0 end ) as SumOfBankzahlung 
   from
      ( either of the above queries ) as PreSumResult

因此，预查询已经使用元数据为您执行了聚合/分离限定符。然后，它成为外部查询的别名"PreSumResult“，分别获取相应的计数和求和值。