面试时的一道sql题

 面试时遇到的一条sql题

 

//订单表

CREATE TABLE [dbo].[ord] (
    [oid] [bigint] IDENTITY (1, 1) NOT NULL ,
    [cid] [bigint] NULL ,
    [ts] [money] NULL
) ON [PRIMARY]
GO

 

//用户表

CREATE TABLE [dbo].[comer] (
    [cid] [bigint] IDENTITY (1, 1) NOT NULL ,
    [cname] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

 

 

要求查询结果,每个用户一条记录,不能重复.显示结果如下

用户姓名| 一共消费笔数|一共消费金额|单笔大于100元的消费笔数|单比大于100元的消费总额|

 

 

 

select e.cname,f.* from comer e,

(select b.cid,sum(b.ts) as bts,count(b.ts) as bconts,max(c.sub) as sub,max(c.up) as up 
from ord b 
left join
(
select count(a.ts) as up,sum(a.ts) as sub,a.cid from ord a where a.ts>100 group by a.cid 
)c
on b.cid=c.cid group by b.cid) f where e.cid=f.cid