有如下四个表:
select YY,MM,DD from SZ_MONTH
YY(smallint)-->年度, MM(smallint)-->月份
DD(numeric 28,8)-->月天数
表里如有数据:
YY MM DD
2007 1 29.00000000
2007 2 28.00000000
2007 3 31.00000000
2007 4 30.00000000
2007 5 31.00000000
2007 6 30.00000000
2007 7 31.00000000
2007 8 31.00000000
2007 9 30.00000000
2007 10 31.00000000
2007 11 30.00000000
2007 12 31.00000000SELECT YG_NO,SZ_NO,SZ_YM,QTY FROM TF_KQTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
SZ_YM(datetime)-->年月第一天, QTY(numeric)-->数量
表里如有数据:
YG_NO SZ_NO SZ_YM QTY
990901 C03 2007-03-01 00:00:00.000 6.00000000
990901 C05 2007-03-01 00:00:00.000 8.00000000
990901 C05 2007-03-01 00:00:00.000 4.00000000
991230 C01 2007-03-01 00:00:00.000 80000000
991230 C05 2007-03-01 00:00:00.000 50000000SELECT YG_NO,SZ_NO,TRS_DD,SZ_YM,QTY FROM TF_JBTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
TRS_DD(datetime)-->输单日期,SZ_YM(datetime)-->输单年月第一天
QTY(numeric)-->数量
表里如有数据:
YG_NO SZ_NO TRS_DD SZ_YM QTY
990901 A13 2007-03-01 2007-03-01 5.00000000
990901 A13 2007-03-02 2007-03-01 3.00000000
990901 A13 2007-03-03 2007-03-01 9.00000000
991230 A13 2007-03-01 2007-03-01 6.00000000
991230 A13 2007-03-07 2007-03-01 2.00000000
991230 A13 2007-03-09 2007-03-01 10.00000000
991230 A13 2007-03-12 2007-03-01 8.00000000SELECT YG_NO,SZ_NO,TZ_DD,SZ_YM,AMTN FROM TF_SZTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
TRS_DD(datetime)-->当天日期, SZ_YM(datetime)-->年月
AMTN(numeric)-->金额
表里如有数据:
YG_NO SZ_NO TRS_DD SZ_YM AMTN
000207 A13 2007-03-21 2007-03-01 91.00000000
991230 A13 2007-03-21 2007-03-01 97.00000000
上面的四个表想(按年月SZ_YM查询)得到结果值=表里SZ_MONTH
对应的年月(YY+MM)的天数DD*3 -(减去)表TF_KQTZ里对应
员工(YG_NO)查询年月SZ_YM并且SZ_NO='C05'的
行数*3 +(加上)在表TF_JBTZ里对应员工(YG_NO)并且SZ_NO='A13'的按每天
输单日(TRS_DD)的金额(AMTN)大于7的笔数*3;
{ 注明:在表TF_JBTZ里面每个员工(yg_no) 并且SZ_NO='A13'
每天(TRS_DD)的数据只有一笔;而SZ_YM也是对应查询的年月}
哪位大虾快还帮帮忙吧,谢了!
select YY,MM,DD from SZ_MONTH
YY(smallint)-->年度, MM(smallint)-->月份
DD(numeric 28,8)-->月天数
表里如有数据:
YY MM DD
2007 1 29.00000000
2007 2 28.00000000
2007 3 31.00000000
2007 4 30.00000000
2007 5 31.00000000
2007 6 30.00000000
2007 7 31.00000000
2007 8 31.00000000
2007 9 30.00000000
2007 10 31.00000000
2007 11 30.00000000
2007 12 31.00000000SELECT YG_NO,SZ_NO,SZ_YM,QTY FROM TF_KQTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
SZ_YM(datetime)-->年月第一天, QTY(numeric)-->数量
表里如有数据:
YG_NO SZ_NO SZ_YM QTY
990901 C03 2007-03-01 00:00:00.000 6.00000000
990901 C05 2007-03-01 00:00:00.000 8.00000000
990901 C05 2007-03-01 00:00:00.000 4.00000000
991230 C01 2007-03-01 00:00:00.000 80000000
991230 C05 2007-03-01 00:00:00.000 50000000SELECT YG_NO,SZ_NO,TRS_DD,SZ_YM,QTY FROM TF_JBTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
TRS_DD(datetime)-->输单日期,SZ_YM(datetime)-->输单年月第一天
QTY(numeric)-->数量
表里如有数据:
YG_NO SZ_NO TRS_DD SZ_YM QTY
990901 A13 2007-03-01 2007-03-01 5.00000000
990901 A13 2007-03-02 2007-03-01 3.00000000
990901 A13 2007-03-03 2007-03-01 9.00000000
991230 A13 2007-03-01 2007-03-01 6.00000000
991230 A13 2007-03-07 2007-03-01 2.00000000
991230 A13 2007-03-09 2007-03-01 10.00000000
991230 A13 2007-03-12 2007-03-01 8.00000000SELECT YG_NO,SZ_NO,TZ_DD,SZ_YM,AMTN FROM TF_SZTZ
YG_NO(varchar 20)-->工号, SZ_NO(varchar 3)-->项目代号
TRS_DD(datetime)-->当天日期, SZ_YM(datetime)-->年月
AMTN(numeric)-->金额
表里如有数据:
YG_NO SZ_NO TRS_DD SZ_YM AMTN
000207 A13 2007-03-21 2007-03-01 91.00000000
991230 A13 2007-03-21 2007-03-01 97.00000000
上面的四个表想(按年月SZ_YM查询)得到结果值=表里SZ_MONTH
对应的年月(YY+MM)的天数DD*3 -(减去)表TF_KQTZ里对应
员工(YG_NO)查询年月SZ_YM并且SZ_NO='C05'的
行数*3 +(加上)在表TF_JBTZ里对应员工(YG_NO)并且SZ_NO='A13'的按每天
输单日(TRS_DD)的金额(AMTN)大于7的笔数*3;
{ 注明:在表TF_JBTZ里面每个员工(yg_no) 并且SZ_NO='A13'
每天(TRS_DD)的数据只有一笔;而SZ_YM也是对应查询的年月}
哪位大虾快还帮帮忙吧,谢了!
-----------
表TF_JBTZ里哪儿来的金额(AMTN)?
--创建测试环境
create table SZ_MONTH(YY smallint,MM smallint,DD numeric (28,8))
create table TF_KQTZ(YG_NO varchar (20),SZ_NO varchar (3),SZ_YM datetime,QTY numeric(18,2))
create table TF_JBTZ(YG_NO varchar (20),SZ_NO varchar (3),TRS_DD datetime,SZ_YM datetime,QTY numeric(18,2))
create table TF_SZTZ(YG_NO varchar (20),SZ_NO varchar (3),TRS_DD datetime,SZ_YM datetime,AMTN numeric(18,2))--插入测试数据
insert SZ_MONTH(YY,MM,DD)
select '2007','1','29.00000000' union all
select '2007','2','28.00000000' union all
select '2007','3','31.00000000' union all
select '2007','4','30.00000000' union all
select '2007','5','31.00000000' union all
select '2007','6','30.00000000' union all
select '2007','7','31.00000000' union all
select '2007','8','31.00000000' union all
select '2007','9','30.00000000' union all
select '2007','10','31.00000000' union all
select '2007','11','30.00000000' union all
select '2007','12','31.00000000'
insert TF_KQTZ(YG_NO,SZ_NO,SZ_YM,QTY)
select '990901','C03','2007-03-01','6.00000000' union all
select '990901','C05','2007-03-01','8.00000000' union all
select '990901','C05','2007-03-01','4.00000000' union all
select '991230','C01','2007-03-01','80000000' union all
select '991230','C05','2007-03-01','50000000'insert TF_JBTZ(YG_NO,SZ_NO,TRS_DD,SZ_YM,QTY)
select '990901','A13','2007-03-01','2007-03-01','5.00000000' union all
select '990901','A10','2007-03-01','2007-03-01','4.00000000' union all
select '990901','A13','2007-03-02','2007-03-01','3.00000000' union all
select '990901','A13','2007-03-01','2007-03-01','6.00000000' union all
select '990901','A13','2007-03-02','2007-03-01','1.00000000' union all
select '990901','A13','2007-03-03','2007-03-01','9.00000000' union all
select '991230','A13','2007-03-01','2007-03-01','6.00000000' union all
select '991230','A13','2007-03-07','2007-03-01','2.00000000' union all
select '991230','A13','2007-03-01','2007-03-01','3.00000000' union all
select '991230','A13','2007-03-09','2007-03-01','10.00000000' union all
select '991230','A13','2007-03-12','2007-03-01','8.00000000'
insert TF_SZTZ(YG_NO,SZ_NO,TRS_DD,SZ_YM,AMTN)
select '000207','A13','2007-03-21','2007-03-01','91.00000000' union all
select '991230','A13','2007-03-21','2007-03-01','97.00000000'--求解过程select _x.YG_NO,_j.SZ_NO,convert(varchar(10),getdate(),120) as TRS_DD
,convert(varchar(10),_x.SZ_YM,120) as SZ_YM,_x.num+count(*)*3 as AMTN
from(
select _m.*,_k.YG_NO,_k.SZ_YM,DD*3-count(1)*3 as num
from SZ_MONTH _m
join TF_KQTZ _k on year(_k.SZ_YM) = _m.yy and month(_k.SZ_YM) = _m.mm
where _k.SZ_NO='C05'
group by _m.YY,_m.MM,_m.DD,_k.YG_NO,_k.SZ_YM
) _x
join (
select YG_NO,SZ_NO,TRS_DD,count(1) as counts,sum(QTY) as QTY
from TF_JBTZ
group by YG_NO,SZ_NO,TRS_DD having sum(QTY) > 7
)_j
on year(_j.TRS_DD) = _x.yy
and month(_j.TRS_DD) = _x.mm
and _j.YG_NO = _x.YG_NO
where _j.SZ_NO = 'A13'
group by _x.YY,_x.MM,_x.DD,_x.YG_NO,_x.num,_x.SZ_YM,_j.SZ_NO
having sum(_j.QTY) > 7--删除测试环境
drop table SZ_MONTH ,TF_KQTZ, TF_JBTZ, TF_SZTZ/*--测试结果
YG_NO SZ_NO TRS_DD SZ_YM AMTN
-------------------- ----- ---------- ---------- ----------------------------------
990901 A13 2007-03-23 2007-03-01 93.00000000
991230 A13 2007-03-23 2007-03-01 99.00000000(所影响的行数为 2 行)
*/
create table SZ_MONTH(YY smallint,MM smallint,DD numeric (28,8))
create table TF_KQTZ(YG_NO varchar (20),SZ_NO varchar (3),SZ_YM datetime,QTY numeric(18,2))
create table TF_JBTZ(YG_NO varchar (20),SZ_NO varchar (3),TRS_DD datetime,SZ_YM datetime,QTY numeric(18,2))
create table TF_SZTZ(YG_NO varchar (20),SZ_NO varchar (3),TRS_DD datetime,SZ_YM datetime,AMTN numeric(18,2))--插入测试数据
insert SZ_MONTH(YY,MM,DD)
select '2007','1','29.00000000' union all
select '2007','2','28.00000000' union all
select '2007','3','31.00000000' union all
select '2007','4','30.00000000' union all
select '2007','5','31.00000000' union all
select '2007','6','30.00000000' union all
select '2007','7','31.00000000' union all
select '2007','8','31.00000000' union all
select '2007','9','30.00000000' union all
select '2007','10','31.00000000' union all
select '2007','11','30.00000000' union all
select '2007','12','31.00000000'
insert TF_KQTZ(YG_NO,SZ_NO,SZ_YM,QTY)
select '990901','C03','2007-03-01','6.00000000' union all
select '990901','C05','2007-03-01','8.00000000' union all
select '990901','C05','2007-03-01','4.00000000' union all
select '991230','C01','2007-03-01','80000000' union all
select '991230','C05','2007-03-01','50000000'insert TF_JBTZ(YG_NO,SZ_NO,TRS_DD,SZ_YM,QTY)
select '990901','A13','2007-03-01','2007-03-01','5.00000000' union all
select '990901','A10','2007-03-01','2007-03-01','4.00000000' union all
select '990901','A13','2007-03-02','2007-03-01','3.00000000' union all
select '990901','A13','2007-03-01','2007-03-01','6.00000000' union all
select '990901','A13','2007-03-02','2007-03-01','1.00000000' union all
select '990901','A13','2007-03-03','2007-03-01','9.00000000' union all
select '991230','A13','2007-03-01','2007-03-01','6.00000000' union all
select '991230','A13','2007-03-07','2007-03-01','2.00000000' union all
select '991230','A13','2007-03-01','2007-03-01','3.00000000' union all
select '991230','A13','2007-03-09','2007-03-01','10.00000000' union all
select '991230','A13','2007-03-12','2007-03-01','8.00000000'GO
create procedure dbo.usp_test
@date varchar(06)
AS
declare @day int
select @day=isnull(dd,0)
from SZ_MONTH
where rtrim(yy)+right(100+mm,2)=@dateselect YG_NO,count(*) as num1
into #t1
from TF_KQTZ
where SZ_NO='C05' and convert(varchar(06),SZ_YM,112)=@date
group by YG_NOselect YG_NO,count(*) as num2
into #t2
from
(
select YG_NO,TRS_DD,sum(QTY) as qty
from TF_JBTZ
where SZ_NO='A13' and convert(varchar(06),TRS_DD,112)=@date
group by YG_NO,TRS_DD
having sum(QTY)>7
) T
group by YG_NOinsert into TF_SZTZ(YG_NO,SZ_NO,TRS_DD,SZ_YM,AMTN)
select YG_NO,'A13',convert(varchar(10),getdate(),120),convert(varchar(07),getdate(),120)+'-01',qty from
(
select #t1.YG_NO, 3*(@day-isnull(num1,0)+isnull(num2,0)) as qty
from #t1
full join #t2
on #t1.YG_NO=#t2.YG_NO
) Tdrop table #t1,#t2
GOexec usp_test '200703'select * from TF_SZTZ
/*
YG_NO SZ_NO TRS_DD SZ_YM AMTN
---------------------------------------------------------------------------------
990901 A13 2007-03-23 00:00:00.000 2007-03-01 00:00:00.000 93.00
991230 A13 2007-03-23 00:00:00.000 2007-03-01 00:00:00.000 99.00
*/
drop proc usp_test
drop table SZ_MONTH ,TF_KQTZ, TF_JBTZ, TF_SZTZ
表TF_JBTZ里对应员工(YG_NO)并且SZ_NO='A13'的按每天输单日(TRS_DD)的金额(AMTN)大于7的笔数*3
-----------
表TF_JBTZ里哪儿来的金额(AMTN)?不好意思,是数量,昨天太晚了,可能是头晕了
/*
YG_NO SZ_NO TRS_DD SZ_YM AMTN
---------------------------------------------------------------------------------
990901 A13 2007-03-23 00:00:00.000 2007-03-01 00:00:00.000 93.00
991230 A13 2007-03-23 00:00:00.000 2007-03-01 00:00:00.000 99.00
*/
drop proc usp_test
drop table SZ_MONTH ,TF_KQTZ, TF_JBTZ, TF_SZTZ
上面的变量有差异,因为用的控件是日期类型的,显示的应是‘2007-03-01’这样子的格式,但只要判断是在2007年3月份就可以了;
select * from TF_SZTZ
where datepart(yy,SZ_YM)*10000+datepart(mm,SZ_YM)*100=200703
执行exec usp_test '2007-03-01',没有正确的数据结果;alter procedure dbo.usp_test
@date datetime
AS
declare @date_tmp varchar(06)
select @date_tmp=convert(varchar(06),@date,112)
declare @day int
select @day=isnull(dd,0)
from SZ_MONTH
where rtrim(yy)+right(100+mm,2)=@date_tmpselect YG_NO,count(*) as num1
into #t1
from TF_KQTZ
where SZ_NO='C05' and convert(varchar(06),SZ_YM,112)=@date_tmp
group by YG_NOselect YG_NO,count(*) as num2
into #t2
from
(
select YG_NO,TRS_DD,sum(QTY) as qty
from TF_JBTZ
where SZ_NO='A13' and convert(varchar(06),TRS_DD,112)=@date_tmp
group by YG_NO,TRS_DD
having sum(QTY)>7
) T
group by YG_NOinsert into TF_SZTZ(YG_NO,SZ_NO,TRS_DD,SZ_YM,AMTN)
select YG_NO,'A13',convert(varchar(10),getdate(),120),convert(varchar(07),getdate(),120)+'-01',qty from
(
select #t1.YG_NO, 3*(@day-isnull(num1,0)+isnull(num2,0)) as qty
from #t1
full join #t2
on #t1.YG_NO=#t2.YG_NO
) Tdrop table #t1,#t2
GOexec usp_test '2007-03-01'
-----------------------------------------------------------------------------------
990901 A13 2007-03-29 00:00:00.000 2007-03-01 00:00:00.000 93.00
991230 A13 2007-03-29 00:00:00.000 2007-03-01 00:00:00.000 99.00