有三张表。部门表,销售员表,销售表。销售表:
id 自增长
pid 销售员的id
product 售出产品名
tmoney 售出价销售员表
id 销售员id
pid 所属部门id
uname 姓名部门表
id 部门id
bname 部门名
现在想用一个GridView来表现一些数据。如图所示:
部门列中:按部门的销售业绩高低进行排序;
个人列中:按每个销售员的业绩高低进行排序。
怎么写sql可以让GridView绑定这些数据啊
id 自增长
pid 销售员的id
product 售出产品名
tmoney 售出价销售员表
id 销售员id
pid 所属部门id
uname 姓名部门表
id 部门id
bname 部门名
现在想用一个GridView来表现一些数据。如图所示:
部门列中:按部门的销售业绩高低进行排序;
个人列中:按每个销售员的业绩高低进行排序。
怎么写sql可以让GridView绑定这些数据啊
from 销售表 join 销售员表 on 销售表.pid=销售员表.id
join 部门表 on order by .... group by ...
最好是给出点数据和想要的结果,不让不知道是不是对的
先通过表联接查部门的在join查个人的
(select a.bname,num(c.tmoney) as cm from 部门表 a join 销售员表 b
on a.id = b.pid join 销售表 c on b.id = c.pid group by a.bname order by cm)
rightjoin
(select d.uname, num(e.tmoney) as em from 销售员表 d join 销售表 e on d.id = e.pid
group by d.uname,d.id order by em)
然后在后台把前2者拼合成一个DataTable;
最后绑定到DataView。
(select a.id,a.bname,sum(c.tmoney) btmoney,max(c.tmoney) tmoney
from 部门表 A,销售员表 b
,(SELECT PID,sum(tmoney) tmoney from 销售表 group by pid) c
where a.id=b.pid and b.id=c.pid
group by a.bname) a
,(select a.pid,a.uname,b.tmoney from 销售员表 a
,(SELECT PID,sum(tmoney) tmoney from 销售表 group by pid) b where a.id=b.pid) b
where a.id=b.pid and a.tmoney=b.tmoney
select o.ord 名次,T2.bname 部门名,T2.cnt 部门销售额,T1.uname 销售员,T1.cnt 销售额 from
(
select (select count(1) from sysobjects where id<t.id) ord from sysobjects t
)O
left join
(select row_number() over( order by sum(a.tmoney)) ord ,b.uname,sum(a.tmoney) cnt
from T_销售表 a,T_销售员 b
where a.pid=b.id
group by b.uname
)T1
on O.ord=T1.ordleft join
(select row_number() over( order by sum(a.tmoney)) ord ,c.bname,sum(a.tmoney) cnt
from T_销售表 a,T_销售员 b,T_部门 c
where a.pid=b.id and b.pid=c.id
group by c.bname
)T2
on O.ord=T2.ord
where o.ord between 1 and 10
(select a.id,a.bname,sum(c.tmoney) btmoney,max(c.tmoney) tmoney
from 部门表 A,销售员表 b
,(SELECT PID,sum(tmoney) tmoney from 销售表 group by pid) c
where a.id=b.pid and b.id=c.pid
group by a.id,a.bname) a
,(select a.pid,a.uname,b.tmoney from 销售员表 a
,(SELECT PID,sum(tmoney) tmoney from 销售表 group by pid) b where a.id=b.pid) b
where a.id=b.pid and a.tmoney=b.tmoney
select a.id 部门,a.bname 部门名,d.金额
from 部门表 a left join
(select b.pid,sum(c.tmoney) 金额 from 销售员表 b,销售表 c where b.id=c.pid group by b.pid) d
on a.id=d.pid
order by d.金额 desc个人销售情况:
select a.id 销售员,a.uname 姓名,b.金额
from 销售员表 a,(select pid,sum(tmoney) 金额 from 销售表 group by pid) b
where a.id=b.pid
group by a.id,a.uname
order by b.金额 desc