if exists(select * from sysobjects where name='test_users')
drop table test_users
go
create table test_users
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
username varchar (20) not null,
pid int null --推广ID ,0代表无推广人
)
insert into test_users values('n1',0)
insert into test_users values('n2',1)
insert into test_users values('n3',1)
insert into test_users values('n4',1)
insert into test_users values('n5',4)
insert into test_users values('n6',4)if exists(select * from sysobjects where name='test_orders')
drop table test_orders
go
create table test_orders
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
uid int not null,
times datetime not null,
pirce float not null --编号id 主键、自动标识列
)
insert into test_orders values(1,'2011-03-11 11:11:11',100)
insert into test_orders values(3,'2011-03-11 11:11:11',999)
insert into test_orders values(3,'2011-04-11 11:11:11',11)
insert into test_orders values(5,'2011-04-11 11:11:11',51)
insert into test_orders values(4,'2011-04-11 11:11:11',45)
insert into test_orders values(6,'2011-03-11 11:11:11',12)
select * FROM test_ordersselect * from test_users
--怎么查询每个用户推广了多少人。并且推广人每月充值了多少.需要统计出来给推广人发工资。月数后台输入。
--比如三月
--用户 推广人数 当月推广人充值数 当前月份
--n1 3 999 3
--n4 2 12 3
--n2 0 0 3
--n3 0 0 3
--n5 0 0 3
--n6 0 0 3
SELECT username ,
COUNT(*) AS 推广人数 ,
SUM(pirce) AS 当月推广人充值数
FROM ( SELECT a.* ,
b.username
FROM test_orders a
LEFT JOIN test_users b ON a.uid = b.id
) aa
WHERE CONVERT(VARCHAR(7), times, 120) = '2011-03'
GROUP BY username
--pirce 你建表时的拼写有误，没有进行修改
--如果要4月的，就把'2011-03'改成'2011-04'
select username as '用户',pid as '推广人数',isnull(pirce,0) as '当月推广人充值数'
,month(times) as '当前月份
' from test_users tu left join test_orders tos on tu.id=tos.uid and month(times)=3
这样的，当中如果“月数后台输入” 也可以把月数当成参数传给“month(times) as '当前月份
”这个。你自己试试吧。
drop table test_users
go
create table test_users
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
username varchar (20) not null,
pid int null --推广ID ,0代表无推广人
)
insert into test_users values('n1',0)
insert into test_users values('n2',1)
insert into test_users values('n3',1)
insert into test_users values('n4',1)
insert into test_users values('n5',4)
insert into test_users values('n6',4)if exists(select * from sysobjects where name='test_orders')
drop table test_orders
go
create table test_orders
(
id int primary key identity(1,1) not null, --编号id 主键、自动标识列
uid int not null,
times datetime not null,
pirce float not null --编号id 主键、自动标识列
)
insert into test_orders values(1,'2011-03-11 11:11:11',100)
insert into test_orders values(3,'2011-03-11 11:11:11',999)
insert into test_orders values(3,'2011-04-11 11:11:11',11)
insert into test_orders values(5,'2011-04-11 11:11:11',51)
insert into test_orders values(4,'2011-04-11 11:11:11',45)
insert into test_orders values(6,'2011-03-11 11:11:11',12)
go
declare @m nvarchar(7)
set @m='2011-03'
--select * FROM test_orders
--select a.id,a.username,b.id from test_users a left join test_users b on a.id=b.pid
--select * from test_users
select a.username 用户,sum(isnull(b.id/b.id,0))推广人数,sum(isnull(b.pirce,0))当月推广人充值数,1*right(@m,2)当前月份
from test_users a left join (
select c.id,c.pid,sum(isnull(d.pirce,0)) pirce from test_users c left join test_orders d on c.id=d.uid and convert(varchar(7),d.times,120)=@m group by c.id,c.pid
)b on a.id=b.pid
group by a.username
/*
用户 推广人数 当月推广人充值数 当前月份
-------------------- ----------- ---------------------- -----------
n1 3 999 3
n2 0 0 3
n3 0 0 3
n4 2 12 3
n5 0 0 3
n6 0 0 3(6 行受影响)*/
username as 用户,
count(1) as 推广人数 ,
sum(pirce) as 当月推广人充值数
from
(
select
a.*,b.username
from
test_orders a
left join
test_users b on
a.uid = b.id)t
where
convert(varchar(7), times, 120) = '2011-03'
group by
username