有2个表,User 有 UserId,UserName 列;Order 有OrderId,UserId,OrderStatus,OrderPrice列。
需求是统计出总共有多少用户,下订单的有多少用户数,下订单并成交的用户数(这个成交的条件就是OrderStatus=100)
需求是统计出总共有多少用户,下订单的有多少用户数,下订单并成交的用户数(这个成交的条件就是OrderStatus=100)
调试欢乐多
用户数=count(*),
下订单用户数=count(*),
下订单并成交用户数=sum(case when OrderStatus=100 then 1 else 0 end)
from [user] u
left join [order] o
on u.userid=o.userid
try
(select count(1) from User) as 用户数量 ,
(select count(distinct UserId) from Order)as 下订单用户数,
count(distinct UserId) as 订单并成交的用户数
from Order
where OrderStatus=100
declare @User table([UserId] varchar(3),[UserName] varchar(4))
insert @User
select '001','王一' union all
select '002','黄军' union all
select '003','吴芳' union all
select '004','胡玉'
--> 测试数据:@Order
declare @Order table([OrderId] int,[UserId] varchar(3),[OrderStatus] int,[OrderPrice] int)
insert @Order
select 1,'001',100,4000 union all
select 2,'002',100,3000 union all
select 3,'003',100,1000 union all
select 4,'001',50,8000select
(select count(1) from @User) as 用户数量 ,
(select count(distinct UserId) from @Order)as 下订单用户数,
count(distinct UserId) as 订单并成交的用户数
from @Order
where OrderStatus=100
-----------------------
4 3 3