27,579
社区成员
发帖
与我相关
我的任务
分享sql查询，请大家帮帮忙，谢谢了！
rate_control预算控制表结构：
id 年 月 员工id 预算金额
1 2011 06 1 4000
2 2011 07 2 5000
3 2011 07 1 3000
emp员工表结构：
id 员工名字
1 张三
2 李四
要查询出这样的效果：
员工名字 预算金额
张三 3000
李四 5000
... ...
员工表和预算控制表是一对多的关系，一个员工可能有N条预算控制的记录，现在要求把所有员工名字作为第一列，按年月对应员工最近的一条预算控制中的预算金额做为第二列查询出来，请大家帮帮忙。可能我有说得不够清楚的地方，大家可以问。
数据库是postgreSQL，因为是用了hibernate，所以不希望用到存储过程，存储过程我也不会用，所以最好是能写sql语句。
分不多，但马上解决，马上结贴。
...全文
请发表友善的回复…
发表回复
iq2367 2011-08-02
- 打赏
- 举报
//用来保存sql查询结果集
query = session.
createSQLQuery("SELECT DISTINCT CONFIG_COM_DEPT.dept_id AS deptId, a.tag AS rate_tag "
+ "FROM CONFIG_COM_DEPT CONFIG_COM_DEPT JOIN SYS_BUDGET_CONTROL a "
+ "ON CONFIG_COM_DEPT.dept_id = a.fk_dept_id AND a.bud_year <= " + selectYear + " "
+ "WHERE a.bud_id = (select d.bud_id from CONFIG_COM_DEPT c JOIN SYS_BUDGET_CONTROL d "
+ "ON c.dept_id = d.fk_dept_id AND d.bud_year <= " + selectYear + " "
+ "WHERE c.dept_name = CONFIG_COM_DEPT.dept_name "
+ "ORDER BY d.bud_year DESC LIMIT 1) "
+ "ORDER BY deptId");
query.addScalar("deptId", Hibernate.INTEGER);
query.addScalar("rate_tag", Hibernate.STRING);
iq2367 2011-08-02
- 打赏
- 举报
谢谢AcHerat，虽然不能很完美的解决问题，把所有不管有无预算的部门遍历出来，但这可以在程序中控制。
谢谢所有帮助过我的热心朋友，前几天忘记结贴了。抱歉~
谢谢所有帮助过我的热心朋友，前几天忘记结贴了。抱歉~
AcHerat 2011-07-28
- 打赏
- 举报
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
where a.id = (select top 1 d.id from ea.dept c JOIN ea.rate_control d
ON c.id = d.dept_id AND d.year <= 2012
where c.name = dept.name
order by d.[year] desc,d.[month] desc)
ORDER BY id
iq2367 2011-07-28
- 打赏
- 举报
先谢谢21楼!
比如选择2012年，那么要查出来的数据是 ：
根据输入的年份去查这两张表，不论部门是否有无预算信息，都要作为列显示出来，而且不重复，每一个部门对应的年份和预算金额应该是输入的年份最近年份的一条记录。
rate_control预算控制表结构：
id year dept_id amount
1 2011 1 4000
2 2011 2 5000
3 2012 1 3000
dept部门表结构：
id name
1 软件部
2 销售部
比如选择2012年，那么要查出来的数据是 ：
dept.id(或者dept.name) rate_control.amount rate_control.year
1(或者软件部) 3000 2012
2(或者销售部) 5000 2011
根据输入的年份去查这两张表，不论部门是否有无预算信息，都要作为列显示出来，而且不重复，每一个部门对应的年份和预算金额应该是输入的年份最近年份的一条记录。
cd731107 2011-07-28
- 打赏
- 举报
最好重新把表结构和数据，以及你想要的结果发上来，因为你后面再发的数据和起初你发贴的内容对不上，看不懂
hanger1212 2011-07-28
- 打赏
- 举报
[Quote=引用 9 楼 liangyong1107 的回复:]
SQL code
/*
rate_control预算控制表结构：
id 年 月 员工id 预算金额
1 2011 06 1 4000
2 2011 07 2 5000
3 2011 07 1 3000
emp员工表结构：
id 员工名字
1 张三
2 李四
要查询出这样的效果：
……
[/Quote]
+1
SQL code
/*
rate_control预算控制表结构：
id 年 月 员工id 预算金额
1 2011 06 1 4000
2 2011 07 2 5000
3 2011 07 1 3000
emp员工表结构：
id 员工名字
1 张三
2 李四
要查询出这样的效果：
……
[/Quote]
+1
iq2367 2011-07-28
- 打赏
- 举报
飘过~弱弱的问：有人在吗?
qingxiangy 2011-07-28
- 打赏
- 举报
[Quote=引用 3 楼 cd731107 的回复:]
SQL code
select 员工名字,预算金额
from emp a,rate_control b
where a.id=b.员工id
and not exists (select 1 from rate_control c where b.员工id=c.员工id
and c.年+c.月>b.年+b.月)
[/Quote] 就这个了
SQL code
select 员工名字,预算金额
from emp a,rate_control b
where a.id=b.员工id
and not exists (select 1 from rate_control c where b.员工id=c.员工id
and c.年+c.月>b.年+b.月)
[/Quote] 就这个了
iq2367 2011-07-27
- 打赏
- 举报
[Quote=引用 17 楼 acherat 的回复:]
SQL code
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
where a.id =……
[/Quote]
真的很感谢AcHerat，但还是有问题...
查询的结果是过滤了重复的，但是取的不是最近1年的，如下：
其中人事部、声乐部等部门2012年都是有预算的，可是现在取的却是2011年的。
SQL code
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
where a.id =……
[/Quote]
真的很感谢AcHerat，但还是有问题...
查询的结果是过滤了重复的，但是取的不是最近1年的，如下：
1;"人事部";4300.01;2011
2;"声乐部";1500.00;2011
758;"财务部";-1.00;2011
759;"行政部";-1.00;2011
760;"活动部";-1.00;2011
761;"销售部";500.01;2012
763;"体育部";1200.00;2012
其中人事部、声乐部等部门2012年都是有预算的，可是现在取的却是2011年的。
AcHerat 2011-07-27
- 打赏
- 举报
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
where a.id = (select top 1 d.id from ea.dept c JOIN ea.rate_control d
ON c.id = d.dept_id AND d.year <= 2012
where c.name = dept.name
order by a.[year] desc,a.[month] desc)
ORDER BY id
iq2367 2011-07-27
- 打赏
- 举报
很谢谢AcHerat的帮助，可14楼和15楼写的都用不了...
AcHerat 2011-07-27
- 打赏
- 举报
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept LEFT JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
AND NOT EXISTS (SELECT 1 FROM ea.rate_control b WHERE a.dept_id = b.dept_id
and /*或者where*/ a.id = (select top 1 d.id from ea.dept c LEFT JOIN ea.rate_control d
ON c.id = d.dept_id AND d.year <= 2012
AND NOT EXISTS (SELECT 1 FROM ea.rate_control e WHERE d.dept_id = e.dept_id
where c.name = dept.name
order by [year] desc,[month] desc)
ORDER BY id
AcHerat 2011-07-27
- 打赏
- 举报
--借用9楼的：
select id,name,amount,[year]
from (
select *,px=row_number() over (partition by dept.name order by [year] desc,[month] desc)
from ea.dept dept LEFT JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
AND NOT EXISTS (SELECT 1 FROM ea.rate_control b WHERE a.dept_id = b.dept_id
)t
where px = 1
iq2367 2011-07-27
- 打赏
- 举报
继续求救~
iq2367 2011-07-27
- 打赏
- 举报
9楼的我看不大懂，谢谢7楼的AcHerat，能继续帮我看下吗?
iq2367 2011-07-27
- 打赏
- 举报
SELECT DISTINCT a.id AS id, dept.name AS name, a.amount AS amount, a.year AS year
FROM ea.dept dept LEFT JOIN ea.rate_control a
ON dept.id = a.dept_id AND a.year <= 2012
AND NOT EXISTS (SELECT 1 FROM ea.rate_control b WHERE a.dept_id = b.dept_id
AND ((b.year+b.month) > (a.year+a.month) or ((b.year+b.month) = (a.year+a.month) AND b.amount > a.amount)))
ORDER BY id
我这样写后，还是不能过滤以前年份的数据
9;"行政部";1000.00;2011
13;"销售部";500.01;2011
14;"人事部";4300.01;2011
16;"体育部";1200.00;2011
17;"声乐部";1500.00;2011
18;"活动部";1300.00;2012
59;"财务部";-1.00;2011
60;"活动部";-1.00;2011
120;"行政部";1000.00;2012
121;"销售部";500.01;2012
122;"人事部";4300.01;2012
123;"体育部";1200.00;2012
124;"声乐部";1500.00;2012
125;"财务部";-1.00;2012
126;"活动部";-1.00;2012
141;"行政部";1000.00;2012
142;"销售部";500.01;2012
143;"人事部";4300.01;2012
144;"体育部";1200.00;2012
145;"声乐部";1500.00;2012
146;"财务部";-1.00;2012
147;"活动部";-1.00;2012
THG8888 2011-07-26
- 打赏
- 举报
9楼正解，顶下
liangyong1107 2011-07-26
- 打赏
- 举报
/*
rate_control预算控制表结构：
id 年 月 员工id 预算金额
1 2011 06 1 4000
2 2011 07 2 5000
3 2011 07 1 3000
emp员工表结构：
id 员工名字
1 张三
2 李四
要查询出这样的效果：
员工名字 预算金额
张三 3000
李四 5000
... ...
员工表和预算控制表是一对多的关系，一个员工可能有N条预算控制的记录，现在要求把所有员工名字作为第一列，按年月对应员工最近的一条预算控制中的预算金额做为第二列查询出来，请大家帮帮忙。可能我有说得不够清楚的地方，大家可以问。
数据库是postgreSQL，因为是用了hibernate，所以不希望用到存储过程，存储过程我也不会用，所以最好是能写sql语句。
分不多，但马上解决，马上结贴。
*/
declare @table1 table (id int not null,年 int not null,月 int not null,员工Id int null, 预算金额 float null)
insert into @table1
select 1,2011,6,1,4000 union all
select 2,2011,7,2,5000 union all
select 3,2011,7,1,3000
declare @table2 table (id int not null,员工名字 varchar(50) null)
insert into @table2
select 1,'张三' union all
select 2,'李四'
;with cte as
(
select *,ROW_NUMBER() OVER(PARTITION BY 员工Id ORDER BY 年 desc,月 desc) as RN
from @table1
)
select b.员工名字,cte.预算金额 from cte left join @table2 b
on cte.员工Id=b.Id where cte.RN=1
AcHerat 2011-07-26
- 打赏
- 举报
select distinct a.name,b.amount
from ea.dept a, ea.rate_control b
where a.id=b.dept_id
and not exists (select 1 from ea.rate_control c where b.dept_id=c.dept_id
and ((c.year+c.month)>(b.year+b.month) or((c.year+c.month)=(b.year+b.month) and c.cmount > b.amount)))AcHerat 2011-07-26
- 打赏
- 举报
[code=SQL]
select distinct a.name,b.amount
from ea.dept a, ea.rate_control b
where a.id=b.dept_id
and not exists (select 1 from ea.rate_control c where b.dept_id=c.dept_id
and ((c.year+c.month)>(b.year+b.month) or((c.year+c.month)=(b.year+b.month) and c.cmount > b.amount)))
[/code]
加载更多回复（6）
