sql - 根据城市计算订单数量

Question

我有一个正在创建的临时表，我们会说第 1 列是 order_id，第 2 列是 user_id，第 3 列是 start_date，第 4 列是 end_date，第 5 列是城市。

order_id user_id Start_date end_date city
101 1 200001 200101 X
101 2 200101 200110 是
101 3 200110 200112
101 3 200112 200210

我想按城市计算从它移出到另一个城市的 order_ids 的数量，并在另一列中计算从另一个城市移入它的 order_ids 的数量。

我希望它以表格形式出现，如下所示：

城市 move_out_orders move_into_orders
× 1 0
是的 1 1
z 0 1

标签： sqlsql-server

Answer 1

你可以做：

with
x as (
  select a.city as from_city, b.city as to_city
  from t a
  join t b on a.order_id = b.order_id 
          and a.city <> b.city
          and a.end_date = b.start_date
),
o (city, cnt) as (
  select from_city, count(*) from x group by from_city
),
i (city, cnt) as (
  select to_city, count(*) from x group by to_city
)
select
  coalesce(i.city, o.city) as city,
  o.cnt as moved_out_orders,
  i.cnt as moved_in_orders
from i
full join o on o.city = i.city