时间戳

首页 > 解决方案 > 元数据值字段的 SQL 查询组

sql - 元数据值字段的 SQL 查询组

问题描述

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

我构建了您可以作为代码找到的 SQL 查询。

我需要一个“pm.meta.value 字段“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')

标签： sqlmetadata

解决方案

首先，现在和将来最好有一个查找表，其中包含 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 子句仅提取 2 个可能的状态代码，因此 CASE 语句的其余部分无论如何都只需要这 2 个可能的值，其余的甚至永远不会出现在输出中。通过使用查找表，您仍然可以使用 2 或 100（如果您有 100 个不同的状态代码）获得您想要的任何可读标题。

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

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

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”，分别获取相应的计数和总和值。

推荐阅读