create table t_department --部门表
(
dept_id varchar(10),--部门编号
dept_name varchar(20)--部门名称
)insert into t_department select '01','营业部1'
insert into t_department select '02','营业部2'
insert into t_department select '0101','营业厅1'
insert into t_department select '0102','营业厅2'
insert into t_department select '0103','营业厅3'
insert into t_department select '0201','营业厅4'
insert into t_department select '0202','营业厅5'
insert into t_department select '0203','营业厅6'create table t_sk --收款明细表
(
dept_name varchar(20), --营业厅名称
date datetime, --日期
je decimal(9,2) --收款金额
)
insert into t_sk select '营业厅1','2006-1-1',100
insert into t_sk select '营业厅2','2006-1-1',200---------------------------------------------------insert into t_sk select '营业厅6','2006-1-1',10
--------------------------------------------------
insert into t_sk select '营业厅1','2006-1-2',300
insert into t_sk select '营业厅2','2006-1-2',400
insert into t_sk select '营业厅3','2006-1-2',500
---------------------------------------------------
insert into t_sk select '营业厅4','2006-1-2',null--------------------------------------------------
/*要求能够以日期和营业部为条件进行查询*/
/*例如如果日期为2006-1-1,且部门名称为营业部1,则查询结果为*/
dept_name rq je
营业厅1 2006-1-1 100
营业厅2 2006-1-1 200
营业厅3 2006-1-1 0
/*如果日期为2006-1-1,且部门名称为营业部2,则查询结果为*/
dept_name rq je
营业厅4 2006-1-1 0
营业厅5 2006-1-1 0
营业厅6 2006-1-1 10
/*日期为2006-1-2的查询结果与上面的类似*/
drop table t_department
drop table t_sk/*注:编号为0101和0102的营业厅属于01营业部,编号为0201和0202的营业厅属于02营业部*/
(
dept_id varchar(10),--部门编号
dept_name varchar(20)--部门名称
)insert into t_department select '01','营业部1'
insert into t_department select '02','营业部2'
insert into t_department select '0101','营业厅1'
insert into t_department select '0102','营业厅2'
insert into t_department select '0103','营业厅3'
insert into t_department select '0201','营业厅4'
insert into t_department select '0202','营业厅5'
insert into t_department select '0203','营业厅6'create table t_sk --收款明细表
(
dept_name varchar(20), --营业厅名称
date datetime, --日期
je decimal(9,2) --收款金额
)
insert into t_sk select '营业厅1','2006-1-1',100
insert into t_sk select '营业厅2','2006-1-1',200---------------------------------------------------insert into t_sk select '营业厅6','2006-1-1',10
--------------------------------------------------
insert into t_sk select '营业厅1','2006-1-2',300
insert into t_sk select '营业厅2','2006-1-2',400
insert into t_sk select '营业厅3','2006-1-2',500
---------------------------------------------------
insert into t_sk select '营业厅4','2006-1-2',null--------------------------------------------------
/*要求能够以日期和营业部为条件进行查询*/
/*例如如果日期为2006-1-1,且部门名称为营业部1,则查询结果为*/
dept_name rq je
营业厅1 2006-1-1 100
营业厅2 2006-1-1 200
营业厅3 2006-1-1 0
/*如果日期为2006-1-1,且部门名称为营业部2,则查询结果为*/
dept_name rq je
营业厅4 2006-1-1 0
营业厅5 2006-1-1 0
营业厅6 2006-1-1 10
/*日期为2006-1-2的查询结果与上面的类似*/
drop table t_department
drop table t_sk/*注:编号为0101和0102的营业厅属于01营业部,编号为0201和0202的营业厅属于02营业部*/
--------------
你能告诉我怎么做吗?
日期比较的时候最好转换到天,否则容易产生分秒不等的情况
select b.*
from t_department a join t_sk b
where a.dept_name = b.dept_name
and left(a.dept_code,2) = '01'
and convert(varchar(10),b.rq,111) = convert(varchar(10),cast('2006-1-1' as datetime),111)
select @date='2006-01-01'select t1.dept_name,isnull(t2.rq,@date) rq,isnull(t2.je,0) from
(select dept_name from t_department where dept_id like '01%' and dept_id<>'01') t1
left join (
select dept_name,date rq,isnull(je,0) je from t_sk
where dept_name in (select dept_name from t_department where dept_id like '01%' and dept_id<>'01')
and date = @date) t2 on t1.dept_name=t2.dept_name
from t_department a left join t_sk b
on a.dept_name = b.dept_name
and convert(varchar(8),b.rq,112) = '20060101'
where a.dept_code like (select dept_code from t_department where dept_name='营业部1')+'%'
group by a.dept_name
declare @date datetime,@id varchar(10)
set @date='2006-01-01' --修改這裡就OK了吧
set @id='01' -- 01 or 02select A.dept_name,isnull(B.[date],@date) as [date], isnull(B.je,0) as [je]
from
(select dept_name from t_department where dept_id like @id+'_%') A
left join t_sk B
on A.dept_name=B.dept_name and datediff(dd,@date,B.[date])=0