sql - sql中的分类

Question

我有两张桌子：

兴趣表：

Date    VisitorID   Interest
5/3    A             $30
5/4    C             $15
5/7    B             $21

访客表：

VisitorID          InterestSegment
A              Simple
B              Compound
C              Simple

我需要查询 2 个表以每天显示“简单”和“复合”段。解决方案应如下所示：

Date    Total spend from Simple   Total spend from Compound
5/3          $30                        NULL
5/4          $15                        NULL
5/7          NULL                       $21

我尝试使用以下代码，但它没有按照我想要的方式进行分类。另外，我需要将 $ 替换为 '' 因为 SUM 函数在 varchar 字段上不起作用。

SELECT p.Date, m.InterestSegment, SUM(REPLACE(p.Interest,'$',''))
FROM visitor as m
LEFT JOIN interest as p ON m.VisitorID = p.VisitorID
GROUP BY Date, InterestSegment, Interest

标签： sql

Answer 1

使用条件聚合

     SELECT p.Date, 
     SUM(case when InterestSegment='Simple' then Interest else 0 end) 
     Total_spend_from_Simple,
     SUM(case when InterestSegment='Compound' then Interest else 0 end) 
     Total_spend_from_Compound   
     FROM visitor as m
     JOIN interest as p ON m.VisitorID = p.VisitorID
     GROUP BY p.Date