select master.name,count(distinct(detail.x_id)),sum(salary.pay) from master,detail,salary where master.id=detail.id and detail.x_id=salary.x_id group by master.name;
select a.name, count(b.x_id), sum(c.pay) from MASTER a, DETAIL b, SALARY c where (a.id = b.id) and (c.x_id in select x_id from SALARY d, DETAIL e where d.x_id=e.x_id)
select master.name,count(distinct(detail.x_id)),sum(salary.pay) from master,detail,salary where master.id=detail.id and detail.x_id=salary.x_id group by master.name;
to: ly_88 我的SALARY表还有其他年份的工资,我要找2001年的怎么办?
select a.name, count(b.x_id), sum(c.pay) from MASTER a, DETAIL b, SALARY c where (a.id = b.id) and (b.x_id=c.x_id)
如果month字段是字符,在where子句加上 and salary.month like '2001%'
and c.MONTH like '2001%'
请调试,应该可以了select a.name,count(b.x_id),sum(c.salary) from master as a inner join (detail as b inner join salary as c on b.x_id=c.x_id ) on a.id=b.id group by a.bxh, a.xm
请调试,应该可以了select a.name,count(b.x_id),sum(c.salary) from master as a inner join (detail as b inner join salary as c on b.x_id=c.x_id ) on a.id=b.id group by a.id, a.name
如果name 唯一可以去掉 最后一行的a.id,
Whitehare(小白免): 执行结果怎么人数全是1?
to: cszhz(丑小鸭): 你的执行结果怎么人数也全是1?
select id,count(x_id),sum(money) (select b.id,a.x_id,a.money from (select x_id,sum(pay) as money from salary group by x_id) a,master b where a.x_id=b.x_id) group by id
select id,count(x_id),sum(money) (select b.id,a.x_id,a.money from (select x_id,sum(pay) as money from salary group by x_id) a,master b where a.x_id=b.x_id) group by id
select id,count(x_id),sum(money) (select b.id,a.x_id,a.money from (select x_id,sum(pay) as money from salary group by x_id) a,master b where a.x_id=b.x_id) group by id
to whitehare(小白免): 执行后,怎么结果全是1(人数)?ly_88(飞龙)、cszhz(丑小鸭)的也一样! 怎么回事?
to zhmmx(咪咪虾): 你的结果也不对呀! 同志们,有空就帮我想一想吧!踢一脚也行。
win2000server+sqlserver2000: SELECT dbo.MASTER.NAME, COUNT(DETAILX.X_ID) AS EMPCOUNT, SUM(DETAILX.PPAYCOUNT) AS PAYCOUNT FROM (SELECT dbo.DETAIL.X_ID, dbo.DETAIL.ID, dbo.DETAIL.NAME, SUM(dbo.SALARY.PAY) AS PPAYCOUNT FROM dbo.SALARY RIGHT OUTER JOIN dbo.DETAIL ON dbo.SALARY.X_ID = dbo.DETAIL.X_ID GROUP BY dbo.DETAIL.X_ID, dbo.DETAIL.ID, dbo.DETAIL.NAME) AS DETAILX RIGHT OUTER JOIN dbo.MASTER ON DETAILX.ID = dbo.MASTER.ID GROUP BY dbo.MASTER.NAME, dbo.MASTER.ID
select sum(pay) from master,detail,salary where master.id=detail.dep and detail.x_id=salary.x_id and detail.id='部门ID' and salary.month='年月'
to BONBON_LI(待业青年):调试中……
我调试过了,我的表名和你不一样,一起考给你create table m(pname varchar(10) null,pid int not null primary key) create table d(pid int null,xid varchar(10) not null primary key, xname varchar(10) null) create table s(xid varchar(10) not null,month datetime null, pay money null)insert into m values('computer',1000) insert into m values('developer',1001) insert into m values('financial',1002) insert into m values('hand',1003)insert into d values(1000,'0001','0a') insert into d values(1000,'0011','0b') insert into d values(1002,'0003','2a') insert into d values(1000,'0111','0c') insert into d values(1001,'0002','1w') insert into d values(1003,'0444','3c') insert into d values(1001,'0022','1r') insert into d values(1003,'0004','3a') insert into d values(1001,'0222','1e') insert into d values(1002,'0033','2b') insert into d values(1002,'0333','2c') insert into d values(1002,'3333','2d') insert into d values(1000,'1111','0d') insert into d values(1003,'0044','3b')insert into s values('0001',dateadd(month,-1,getdate()),1000) insert into s values('0011',dateadd(month,-1,getdate()),1001) insert into s values('0001',dateadd(month,-2,getdate()),1000) insert into s values('0011',dateadd(month,-2,getdate()),1001) insert into s values('0001',dateadd(month,-3,getdate()),1001) insert into s values('0011',dateadd(month,-3,getdate()),1002) insert into s values('0003',dateadd(month,-1,getdate()),1002) insert into s values('0003',dateadd(month,-3,getdate()),1002) insert into s values('0003',dateadd(month,-2,getdate()),1003) insert into s values('0111',dateadd(month,-1,getdate()),1004) insert into s values('0111',dateadd(month,-2,getdate()),1004) insert into s values('0111',dateadd(month,-3,getdate()),1005)insert into s values('0111',dateadd(month,-1,getdate()),2000) insert into s values('0111',dateadd(month,-2,getdate()),2001) insert into s values('0111',dateadd(month,-3,getdate()),2000) insert into s values('0002',dateadd(month,-1,getdate()),2001) insert into s values('0002',dateadd(month,-2,getdate()),2001) insert into s values('0002',dateadd(month,-3,getdate()),2002) insert into s values('0444',dateadd(month,-1,getdate()),2002) insert into s values('0444',dateadd(month,-3,getdate()),2002) insert into s values('0444',dateadd(month,-2,getdate()),2003) insert into s values('0022',dateadd(month,-1,getdate()),2004) insert into s values('0022',dateadd(month,-2,getdate()),2004) insert into s values('0022',dateadd(month,-3,getdate()),2005)insert into s values('0004',dateadd(month,-1,getdate()),3000) insert into s values('0004',dateadd(month,-1,getdate()),3001) insert into s values('0004',dateadd(month,-2,getdate()),3000) insert into s values('0222',dateadd(month,-2,getdate()),3001) insert into s values('0222',dateadd(month,-3,getdate()),3001) insert into s values('0222',dateadd(month,-3,getdate()),3002) insert into s values('0033',dateadd(month,-1,getdate()),3002) insert into s values('0033',dateadd(month,-3,getdate()),3002) insert into s values('0033',dateadd(month,-2,getdate()),3003) insert into s values('3333',dateadd(month,-1,getdate()),3004) insert into s values('3333',dateadd(month,-2,getdate()),3004) insert into s values('3333',dateadd(month,-3,getdate()),3005)insert into s values('1111',dateadd(month,-1,getdate()),4000) insert into s values('1111',dateadd(month,-2,getdate()),4001) insert into s values('1111',dateadd(month,-3,getdate()),4000) insert into s values('0044',dateadd(month,-1,getdate()),4001) insert into s values('0044',dateadd(month,-2,getdate()),4001) insert into s values('0044',dateadd(month,-3,getdate()),4002)select pname,count(jj.xid),sum(jj.money) from (select m.pname,kk.pid,kk.xid,kk.money from (select d.pid,tt.xid,tt.money from (select xid,sum(pay) as money from s group by xid) tt,d where tt.xid=d.xid) kk,m where m.pid = kk.pid) jj group by pname
其实zhmmx(咪咪虾)的就差不多了 但他只得到id
to BONBON_LI(待业青年):你只要吧我的代码改一下表名就可以了,你再想想
to hlilna(): 你和我做的查询结果差别在于: 你统计的是部门里拿到工资的人数, 而我统计的是部门里所有的人数,只要把RIGHT OUTER JOIN改成INNER JOIN就和你的一样了。 不知道cdkogh要的是哪种结果
To BONBON_LI: 我要的是部门里所有的人数。 执行的结果:部门是对了,但人数不是0就是1,总工资居然为NULL(当人数为0时), 我就搞不明白? 还有,RIGHT OUTER JOIN与INNER JOIN有什么区别?
来了, 看我的select a.name,count(b.x_id),sum(c.salary) from master as a inner join (detail as b left outer join salary as c on b.x_id=c.x_id ) on a.id=b.id group by a.name 应为是left集合大于right 用left outer join有必要可以两个都改
我是要得到如下数据:部门名称 人数 总工资
部门1 124 215433.23
部门2 255 501233.12
部门3 68 124542.22
...
where master.id=detail.id and detail.x_id=salary.x_id
group by master.name;
where (a.id = b.id) and (c.x_id in select x_id from SALARY d, DETAIL e where d.x_id=e.x_id)
where master.id=detail.id and detail.x_id=salary.x_id
group by master.name;
我的SALARY表还有其他年份的工资,我要找2001年的怎么办?
where (a.id = b.id) and (b.x_id=c.x_id)
and salary.month like '2001%'
from
master as a inner join
(detail as b inner join
salary as c
on b.x_id=c.x_id ) on a.id=b.id
group by a.bxh, a.xm
from
master as a inner join
(detail as b inner join
salary as c
on b.x_id=c.x_id ) on a.id=b.id
group by a.id, a.name
Whitehare(小白免):
执行结果怎么人数全是1?
to: cszhz(丑小鸭):
你的执行结果怎么人数也全是1?
(select b.id,a.x_id,a.money from
(select x_id,sum(pay) as money
from salary
group by x_id) a,master b
where a.x_id=b.x_id)
group by id
(select b.id,a.x_id,a.money from
(select x_id,sum(pay) as money
from salary
group by x_id) a,master b
where a.x_id=b.x_id)
group by id
(select b.id,a.x_id,a.money from
(select x_id,sum(pay) as money
from salary
group by x_id) a,master b
where a.x_id=b.x_id)
group by id
to whitehare(小白免):
执行后,怎么结果全是1(人数)?ly_88(飞龙)、cszhz(丑小鸭)的也一样!
怎么回事?
to zhmmx(咪咪虾):
你的结果也不对呀!
同志们,有空就帮我想一想吧!踢一脚也行。
SELECT dbo.MASTER.NAME, COUNT(DETAILX.X_ID) AS EMPCOUNT,
SUM(DETAILX.PPAYCOUNT) AS PAYCOUNT
FROM
(SELECT dbo.DETAIL.X_ID, dbo.DETAIL.ID, dbo.DETAIL.NAME, SUM(dbo.SALARY.PAY) AS PPAYCOUNT
FROM dbo.SALARY
RIGHT OUTER JOIN dbo.DETAIL
ON dbo.SALARY.X_ID = dbo.DETAIL.X_ID
GROUP BY dbo.DETAIL.X_ID, dbo.DETAIL.ID, dbo.DETAIL.NAME)
AS DETAILX
RIGHT OUTER JOIN dbo.MASTER
ON DETAILX.ID = dbo.MASTER.ID
GROUP BY dbo.MASTER.NAME, dbo.MASTER.ID
detail.x_id=salary.x_id and detail.id='部门ID' and salary.month='年月'
to BONBON_LI(待业青年):调试中……
create table d(pid int null,xid varchar(10) not null primary key, xname varchar(10) null)
create table s(xid varchar(10) not null,month datetime null, pay money null)insert into m values('computer',1000)
insert into m values('developer',1001)
insert into m values('financial',1002)
insert into m values('hand',1003)insert into d values(1000,'0001','0a')
insert into d values(1000,'0011','0b')
insert into d values(1002,'0003','2a')
insert into d values(1000,'0111','0c')
insert into d values(1001,'0002','1w')
insert into d values(1003,'0444','3c')
insert into d values(1001,'0022','1r')
insert into d values(1003,'0004','3a')
insert into d values(1001,'0222','1e')
insert into d values(1002,'0033','2b')
insert into d values(1002,'0333','2c')
insert into d values(1002,'3333','2d')
insert into d values(1000,'1111','0d')
insert into d values(1003,'0044','3b')insert into s values('0001',dateadd(month,-1,getdate()),1000)
insert into s values('0011',dateadd(month,-1,getdate()),1001)
insert into s values('0001',dateadd(month,-2,getdate()),1000)
insert into s values('0011',dateadd(month,-2,getdate()),1001)
insert into s values('0001',dateadd(month,-3,getdate()),1001)
insert into s values('0011',dateadd(month,-3,getdate()),1002)
insert into s values('0003',dateadd(month,-1,getdate()),1002)
insert into s values('0003',dateadd(month,-3,getdate()),1002)
insert into s values('0003',dateadd(month,-2,getdate()),1003)
insert into s values('0111',dateadd(month,-1,getdate()),1004)
insert into s values('0111',dateadd(month,-2,getdate()),1004)
insert into s values('0111',dateadd(month,-3,getdate()),1005)insert into s values('0111',dateadd(month,-1,getdate()),2000)
insert into s values('0111',dateadd(month,-2,getdate()),2001)
insert into s values('0111',dateadd(month,-3,getdate()),2000)
insert into s values('0002',dateadd(month,-1,getdate()),2001)
insert into s values('0002',dateadd(month,-2,getdate()),2001)
insert into s values('0002',dateadd(month,-3,getdate()),2002)
insert into s values('0444',dateadd(month,-1,getdate()),2002)
insert into s values('0444',dateadd(month,-3,getdate()),2002)
insert into s values('0444',dateadd(month,-2,getdate()),2003)
insert into s values('0022',dateadd(month,-1,getdate()),2004)
insert into s values('0022',dateadd(month,-2,getdate()),2004)
insert into s values('0022',dateadd(month,-3,getdate()),2005)insert into s values('0004',dateadd(month,-1,getdate()),3000)
insert into s values('0004',dateadd(month,-1,getdate()),3001)
insert into s values('0004',dateadd(month,-2,getdate()),3000)
insert into s values('0222',dateadd(month,-2,getdate()),3001)
insert into s values('0222',dateadd(month,-3,getdate()),3001)
insert into s values('0222',dateadd(month,-3,getdate()),3002)
insert into s values('0033',dateadd(month,-1,getdate()),3002)
insert into s values('0033',dateadd(month,-3,getdate()),3002)
insert into s values('0033',dateadd(month,-2,getdate()),3003)
insert into s values('3333',dateadd(month,-1,getdate()),3004)
insert into s values('3333',dateadd(month,-2,getdate()),3004)
insert into s values('3333',dateadd(month,-3,getdate()),3005)insert into s values('1111',dateadd(month,-1,getdate()),4000)
insert into s values('1111',dateadd(month,-2,getdate()),4001)
insert into s values('1111',dateadd(month,-3,getdate()),4000)
insert into s values('0044',dateadd(month,-1,getdate()),4001)
insert into s values('0044',dateadd(month,-2,getdate()),4001)
insert into s values('0044',dateadd(month,-3,getdate()),4002)select pname,count(jj.xid),sum(jj.money)
from (select m.pname,kk.pid,kk.xid,kk.money from
(select d.pid,tt.xid,tt.money from
(select xid,sum(pay) as money from s group by xid) tt,d
where tt.xid=d.xid) kk,m
where m.pid = kk.pid) jj
group by pname
你和我做的查询结果差别在于:
你统计的是部门里拿到工资的人数,
而我统计的是部门里所有的人数,只要把RIGHT OUTER JOIN改成INNER JOIN就和你的一样了。
不知道cdkogh要的是哪种结果
To BONBON_LI:
我要的是部门里所有的人数。
执行的结果:部门是对了,但人数不是0就是1,总工资居然为NULL(当人数为0时),
我就搞不明白?
还有,RIGHT OUTER JOIN与INNER JOIN有什么区别?
我的语句不对吗,我在自己的database上调试过的把你的几个表导成Excel发个我[email protected]
看我的select a.name,count(b.x_id),sum(c.salary)
from
master as a inner join
(detail as b left outer join
salary as c
on b.x_id=c.x_id ) on a.id=b.id
group by a.name 应为是left集合大于right 用left outer join有必要可以两个都改
结果出来了,完全正确!
再次感谢大家的参与,我会为我的帖子加分作为报答!
谢谢!
顺便给各位共享一个免费发送短消息软件(不会放很久):
http://211.100.17.155/software/Aico_sms.zip
(很有用,就是显示的号码太长了:+8869177427*********共19位)