我正在尝试查询3表数据库Orders,客户和销售员。为了找到有关销售人员最近表现的更多信息。所需的类别是id,age,amout> 1000的订单号,数量> 500(y / n)的订单,服务的唯一客户数,自上次订购以来的天数以及最后一次下单的金额。
我的代码如下所示,也可以在链接中查看:
SELECT o.salesperson_id
s.Age,
(CASE WHEN o.Amount > 500 THEN 'Yes' ELSE 'No' END) AS 'Had Order Bigger Than 500?',
(datediff(day,o.order_date,GETDATE())) AS 'Days Since Last Order',
o.Amount as 'Last Order Amount'
FROM Orders o INNER JOIN Salesperson s ON o.salesperson_id = s.ID
WHERE o.Amount IN
(SELECT o.Amount WHERE o.order_date = MAX (o.order_date) FROM Orders)
GROUP BY o.salesperson_id
HAVING (COUNT (DISTINCT o.cust_id)) AS 'Number of Unique Customers Served',
(SUM(CASE WHEN o.Amount > 1000 THEN 1 ELSE 0 END)) AS 'Number of Orders Bigger Than 1000'
ORDER BY o.salesperson_id;
http://sqlfiddle.com/#!18/4fc5c/69
自动调试器说问题出在我的FROM上,但是我很傻
关键字“ FROM”附近的语法不正确
我的预期结果将是这样的:
+----------------+-----------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------+-------------------+
| Salesperson ID | Salesperson Age | Number of Orders Bigger Than 1000 | Had Order Bigger Than 500? (y/n) | Number of Unique Customers Served | Days Since Last Order | Last Order Amount |
+----------------+-----------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------+-------------------+
| 1 | 61 | 0 | N | 1 | | 460 |
| 2 | 34 | 1 | Y | 2 | | 2400 |
| 8 | 57 | 1 | y | 1 | | 1800 |
+----------------+-----------------+-----------------------------------+-----------------------------------+-----------------------------------+-----------------------+-------------------+
非常感谢收到的所有援助。
加入销售人员和订单,按销售人员分组并使用条件汇总:
select
s.ID [Salesperson ID],
s.Age [Salesperson Age],
sum(case when o.Amount > 1000 then 1 else 0 end) [Number of Orders Bigger Than 1000],
case when sum(case when o.Amount > 500 then 1 else 0 end) > 0 then 'Yes' else 'No' end [Had Order Bigger Than 500? (y/n)],
count(distinct cust_id) [Number of Unique Customers Served],
datediff(day, max(o.order_date), GETDATE()) [Days Since Last Order],
(select Amount from Orders where salesperson_id = s.ID and order_date = max(o.order_date))[Last Order Amount]
from Salesperson s inner join Orders o
on o.salesperson_id = s.ID
group by s.ID, s.Age
order by s.ID
参见演示。
结果:
> Salesperson ID | Salesperson Age | Number of Orders Bigger Than 1000 | Had Order Bigger Than 500? (y/n) | Number of Unique Customers Served | Days Since Last Order | Last Order Amount
> -------------: | --------------: | --------------------------------: | :------------------------------- | --------------------------------: | --------------------: | ----------------:
> 1 | 61 | 0 | No | 1 | 8883 | 460
> 2 | 34 | 1 | Yes | 2 | 7953 | 2400
> 8 | 57 | 1 | Yes | 1 | 7587 | 1800
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句