sql - 创建在 SQL 中返回表的函数

Question

我想用一些逻辑来创建视图，比如使用 (for loop , if .. else) 但由于 SQL 不支持这种方式，所以我想创建不带参数并返回表的表函数。

我有一张orders如下表

OrderId  Destination  Category  Customer
----------------------------------------
6001     UK           5         Adam
6002     GER          3         Jack

下表tracking orders如下

ID  OrderID  TrackingID
-----------------------
1   6001     1
2   6001     2
3   6002     2

这里是types of tracking

ID  Name
--------------
1   Processing
2   Shipped
3   Delivered

正如您在 中看到的tracking order，订单号可能有多个记录，具体取决于发生了多少跟踪事件。

我们有超过 25 种跟踪类型，我在这里没有包括在内。这意味着一个订单可以在tracking order表中存在 25 次。

话虽如此，我的要求是创建如下视图，条件是订单必须属于 5 或​​ 3 个类别（我们有超过 15 个类别）。

每当我运行该函数时，它必须返回更新的信息。

因此，例如，当发生新的跟踪并将其插入时tracking order，我想运行我的函数并在相应的标志列（例如isDelivered）中查看更新。

我真的很困惑什么是实现这一目标的最佳方法。我不需要确切的脚本，我只需要了解实现它的方法，因为我对 SQL 不是很熟悉

标签： sqlsql-serverstored-proceduresviewtable-functions

Answer 1

它可以通过使用条件聚合的交叉表查询来完成。像这样的东西

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;

[编辑] 为了打破第 3 类订单，在交叉表中添加了 3 个附加列。

select o.OrderID, 
       max(case when tt.[Name]='Processing' then 1 else 0 end) isPrepared,
       max(case when tt.[Name]='Shipped' then 1 else 0 end) isShipped,
       max(case when tt.[Name]='Delivered' then 1 else 0 end) isDelivered,
       max(case when tt.[Name]='Processing' and o.category=3 then 1 else 0 end) isC3Prepared,
       max(case when tt.[Name]='Shipped' and o.category=3 then 1 else 0 end) isC3Shipped,
       max(case when tt.[Name]='Delivered'  and o.category=3 then 1 else 0 end) isC3Delivered
from orders o
     join tracking_orders tro on o.OrderID=tro.OrderID
     join tracking_types tt on tro.TrackingID=tt.TrackingID
where o.category in(3, 5)
group by o.OrderID;