你何时放弃SQL中的set操作并进行程序化？

我曾经在RDBMS中做过这个任务:

给出表客户,订单,订单和产品.通常的字段和关系完成的所有事情,以及订单表上的评论备注字段.

对于一位客户,检索客户订购的所有产品的清单,其中包括产品名称,首次购买年份,最近三次购买的日期,最新订单的评论,该产品 - 客户组合的总收入总和,过去12个月.

几天后,我放弃了将其作为查询进行操作,并选择仅为客户获取每个订单行,并选择每个产品并在程序上运行数据以构建所需的表客户端.

我认为这是以下一种或多种症状:

我是一个懒惰的白痴,应该看看如何在SQL中做到这一点

集合操作不像程序操作那样富有表现力

SQL并不像它应该那样富有表现力

我做对了吗？我有其他选择吗？

你绝对应该能够在不做JOIN与应用程序代码相当的工作的情况下完成这项工作,即从两个订单行和产品中获取所有行并迭代它们.您不必是SQL向导就可以执行该操作. JOIN对于SQL来说,循环是一种过程语言 - 两者都是你应该知道如何使用的基本语言特性.

人们陷入的一个陷阱是认为整个报告必须在单个SQL查询中生成.不对!Tony Andrews指出,大多数报告都不适合矩形.有很多汇总,摘要,特殊情况等,因此在单独的查询中获取报表的部分内容既简单又有效.同样,在过程语言中,您不会尝试在单行代码中进行所有计算,甚至在单个函数中进行计算(希望如此).

某些报告工具坚持要求从单个查询生成报告,并且您没有机会合并多个查询.如果是这样,那么你需要生成多个报告(如果老板想要在一个页面上,那么你需要手动进行一些粘贴).

要获取所有订购产品的清单(带有产品名称),最近三次采购的日期以及对最新订单的评论很简单:

SELECT o.*, l.*, p.*
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
ORDER BY o.order_date;

可以逐行迭代结果以提取最新订单的日期和注释,因为您无论如何都要获取这些行.但是通过要求数据库返回按日期排序的结果,使自己变得容易.

首次购买的年份可从上一个查询中获得,如果您按order_date行排序并逐行获取结果,则您可以访问第一个订单.否则,您可以这样做:

SELECT YEAR(MIN(o.order_date)) FROM Orders o WHERE o.customer_id = ?;

过去12个月的产品购买总和最好通过单独的查询计算:

SELECT SUM(l.quantity * p.price)
FROM Orders o
 JOIN OrderLines l USING (order_id)
 JOIN Products p USING (product_id)
WHERE o.customer_id = ?
 AND o.order_date > CURDATE() - INTERVAL 1 YEAR;

编辑:您在另一条评论中说,您希望了解如何在标准SQL中获取最近三次购买的日期:

SELECT o1.order_date
FROM Orders o1
  LEFT OUTER JOIN Orders o2 
  ON (o1.customer_id = o2.customer_id AND (o1.order_date < o2.order_date 
      OR (o1.order_date = o2.order_date AND o1.order_id < o2.order_id)))
WHERE o1.customer_id = ?
GROUP BY o1.order_id
HAVING COUNT(*) <= 3;

如果您可以使用一些特定于供应商的SQL功能,则可以使用Microsoft/Sybase TOP n或MySQL/PostgreSQL LIMIT:

SELECT TOP 3 order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC;

SELECT order_date
FROM Orders
WHERE customer_id = ?
ORDER BY order_date DESC
LIMIT 3;