有两个表
orders(orderid——订单号 主键
orderstatus——订单状态:有订票和出票两种状态
customerid——会员号
ticketnum——票数)
orderdetail(orderdetailid——订单明细编号
orderid——订单号
price——票面金额)
要统计出票总金额大于1000的会员的订票订单数,出票订单数,订票票数和订票总金额的语句怎么写?
orders(orderid——订单号 主键
orderstatus——订单状态:有订票和出票两种状态
customerid——会员号
ticketnum——票数)
orderdetail(orderdetailid——订单明细编号
orderid——订单号
price——票面金额)
要统计出票总金额大于1000的会员的订票订单数,出票订单数,订票票数和订票总金额的语句怎么写?
count(1) as 订票订单数,
sum(case when a.orderstatus='出票' then 1 else 0 end) as 出票票数,
sum(case when a.orderstatus='订票' then 1 else 0 end) as 订票票数,
b.price
from
orders a
join
(select orderid,sum(price) as price from orderdetail group by orderid) b
on a.orderid=b.orderid
where b.price>1000
group by b.price
select
count(1) as 订票订单数,
sum(case when a.orderstatus='出票' then 1 else 0 end) as 出票票数,
sum(case when a.orderstatus='订票' then 1 else 0 end) as 订票票数,
b.price
from
orders a
join
(select orderid,sum(price) as price from orders a,orderdetail b where a.orderid=b.orderid and a.orderstatus='订票' group by orderid) b
on a.orderid=b.orderid
where b.price>1000
group by b.price
count(1) as 订票订单数,
sum(case when a.orderstatus='出票' then 1 else 0 end) as 出票票数,
sum(case when a.orderstatus='订票' then 1 else 0 end) as 订票票数,
b.price
from
orders a
join
(select a.orderid,sum(b.price) as price
from orders a,orderdetail b
where a.orderid=b.orderid and a.orderstatus='订票'
group by a.orderid
) b
on a.orderid=b.orderid
where b.price>1000
group by b.price
select
sum(case when a.orderstatus='订票' then 1 else 0 end ) as 订票订单数,
sum(case when a.orderstatus='订票' then a.ticketnum else 0 end) as 订票票数,
sum(case when a.orderstatus='订票' then b.price else 0 end) as 订票总金额,
sum(case when a.orderstatus='出票' then 1 else 0 end ) as 出票订单数,
sum(case when a.orderstatus='出票' then a.ticketnum else 0 end) as 出票票数,
sum(case when a.orderstatus='出票' then b.price else 0 end) as 出票总金额
from orders a
join
(select a.orderid,sum(b.price) as price
from orders a,orderdetail b
where a.orderid=b.orderid and a.orderstatus='订票'
group by a.orderid
) b
on a.orderid=b.orderid
where b.price>1000
group by b.price没有数据不知道对不对。测测吧!
(select a.orderid,sum(b.price) as price
from orders a,orderdetail b
where a.orderid=b.orderid and a.orderstatus='订票'
group by a.orderid
) b
on a.orderid=b.orderid
和join
(select orderid,sum(price) as price from orderdetail group by orderid) b
on a.orderid=b.orderid
这两种连接有什么区别,为什么上面那个b表先要和a连接一次