需要有关SQL查询的帮助

debugcn 发表于 Dev

强农

我目前正在使用OracleSQL处理两个不同的查询问题，并且需要一些帮助。

Q1。在这里，两个查询可以完美地分开工作。但是，我需要将它们放到一个表/图中，而不是将两个单独的表/图中放在一起。我缺少什么/合并两者需要做什么？主要区别是“ WHERE Email_List =“

---显示客户数和平均总支出，按不在我们电子邮件列表中的客户细分。

SELECT COUNT(DISTINCT Customer_Number) Cust_No_w_Email
      ,AVG(Total_Spending) Avg_Spend_w_Email  
FROM CUST_FILE
WHERE Email_List=1;

SELECT COUNT(DISTINCT Customer_Number) Cust_No_wo_Email
      ,AVG(Total_Spending) Avg_Spend_wo_Email
FROM CUST_FILE
WHERE Email_List=0;

Q2。与上面的模板类似，下面的模板是我能得到的最接近的模板。

---显示按工作以及客户是否在我们的电子邮件列表中细分的食品支出总和（提示：SUM函数）。仅显示食品支出总和大于5000的那些组。按食品支出总和从高到低对组进行排序。

SELECT Job, Email_List AS W_Email, SUM(Food) AS Food_Exp
FROM CUST_FILE
WHERE Email_List=1
GROUP BY Job, Email_List, Food
HAVING SUM(Food)>5000
ORDER BY Job, Food DESC;

SELECT Job, Email_List AS WO_Email, SUM(Food) AS Food_Exp
FROM CUST_FILE
WHERE Email_List=0
GROUP BY Job, Email_List, Food
HAVING SUM(Food)>5000
ORDER BY Food DESC;

如果可以帮助我，您正在拯救灵魂。先感谢您！

Ankit bajpai

对于第一个问题，您可以使用以下查询-

SELECT 'customers on list', COUNT(DISTINCT Customer_Number) Cust_No_w_Email
      ,AVG(Total_Spending) Avg_Spend_w_Email  
FROM CUST_FILE
WHERE Email_List = 1
UNION ALL
SELECT 'customers not on list', COUNT(DISTINCT Customer_Number) Cust_No_w_Email
      ,AVG(Total_Spending) Avg_Spend_w_Email  
FROM CUST_FILE
WHERE Email_List = 0

对于第二个问题，您可以在下面的查询中尝试-

SELECT 'Customer in list', Job, Email_List AS W_Email, SUM(Food) AS Food_Exp
FROM CUST_FILE
WHERE Email_List=1
GROUP BY Job, Email_List
HAVING SUM(Food)>5000
UNION ALL
SELECT 'Customer not in list', Job, Email_List, SUM(Food)
FROM CUST_FILE
WHERE Email_List=0
GROUP BY Job, Email_List
HAVING SUM(Food)>5000
ORDER BY Food_Exp DESC;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。