有一个组别表usergroup,字段为
groupid,groupName
1 A组
2 B组采购表 tblcg,字段为
id ,groupid,orderid
1 1 1
2 2 2
订单表 orders,字段为
orderid,ordertime,orderostate
1 2011-02-23:00:00:00 1
2 2011-02-23:00:00:00 1
3 2011-02-23:00:00:00 0
4 2011-02-23:00:00:00 2
组别表groupdid 对应采购表 groupid,采购表orderid对应订单表orderid
查询订单状态大于1的,A组,B组当天历史最高量,和 A组该天订单处理量,B组该天订单处理量。谢谢!
groupid,groupName
1 A组
2 B组采购表 tblcg,字段为
id ,groupid,orderid
1 1 1
2 2 2
订单表 orders,字段为
orderid,ordertime,orderostate
1 2011-02-23:00:00:00 1
2 2011-02-23:00:00:00 1
3 2011-02-23:00:00:00 0
4 2011-02-23:00:00:00 2
组别表groupdid 对应采购表 groupid,采购表orderid对应订单表orderid
查询订单状态大于1的,A组,B组当天历史最高量,和 A组该天订单处理量,B组该天订单处理量。谢谢!
select groupid, count(groupid)
from usergroup g, tblcg t, orders s
where g.groupid = t.groupid
and t.orderid = s.orderid
and orderostate > 1
group by groupid
declare @usergroup table (groupid int,groupName varchar(3))
insert into @usergroup
select 1,'A组' union all
select 2,'B组'declare @tblcg table (id int,groupid int,orderid int)
insert into @tblcg
select 1,1,1 union all
select 2,2,2declare @orders table (orderid int,ordertime datetime,orderostate int)
insert into @orders
select 1,'2011-02-23 00:00:00',1 union all
select 2,'2011-02-23 00:00:00',1 union all
select 3,'2011-02-23 00:00:00',0 union all
select 4,'2011-02-23 00:00:00',2select c.*,a.* from @orders c
left join @tblcg b on c.orderid=b.orderid
left join @usergroup a on b.groupid=a.groupid/*
orderid ordertime orderostate groupid groupName
----------- ----------------------- ----------- ----------- ---------
1 2011-02-23 00:00:00.000 1 1 A组
2 2011-02-23 00:00:00.000 1 2 B组
3 2011-02-23 00:00:00.000 0 NULL NULL
4 2011-02-23 00:00:00.000 2 NULL NULL
*/
declare @usergroup table (groupid int,groupName varchar(3))
insert into @usergroup
select 1,'A组' union all
select 2,'B组'declare @tblcg table (id int,groupid int,orderid int)
insert into @tblcg
select 1,1,1 union all
select 2,2,2 union all
select 1,1,3 union all
select 1,1,4 union all
select 1,2,5 union all
select 1,2,6 declare @orders table (orderid int,ordertime datetime,orderostate int)
insert into @orders
select 1,'2011-02-23 00:00:00',1 union all
select 2,'2011-02-23 00:00:00',1 union all
select 3,'2011-02-23 00:00:00',0 union all
select 4,'2011-02-23 00:00:00',2 union all
select 5,'2011-02-23 00:00:00',2 union all
select 6,'2011-02-23 00:00:00',2select ordertime,a.groupName,count(1) as cnt from @orders c
left join @tblcg b on c.orderid=b.orderid
left join @usergroup a on b.groupid=a.groupid
where c.orderostate>1
and ordertime='2011-02-23'
group by ordertime,groupName/*
ordertime groupName cnt
----------------------- --------- -----------
2011-02-23 00:00:00.000 A组 1
2011-02-23 00:00:00.000 B组 2
*/