create proc sp_cltj_new
@JGGC NVARCHAR(50),
@DATES DATETIME,
@DATEE DATETIME
as
SELECT czr,convert(decimal(18,2),sum(df))'df' into #zdf FROM xxjg group by czr
select a.xh,a.ph,a.jgrq,a.jggc,b.czr,b.df
from xxjg a
left join #zdf b
on a.czr=b.czr
where jggc=@JGGC and jgrq between @DATES and @DATEEgo
假设,明细表记录数为100条,汇总表记录数为3条。
想要将明细表和汇总表拼接,左联合时出现两个表记录数以明细表记录数为准,汇总表随之也出现100条
我想要的结果是明细表显示100条,拼接汇总表3条,3条以下以null值填充
跪求各大虾。联合明细表拼接汇总表
@JGGC NVARCHAR(50),
@DATES DATETIME,
@DATEE DATETIME
as
SELECT czr,convert(decimal(18,2),sum(df))'df' into #zdf FROM xxjg group by czr
select a.xh,a.ph,a.jgrq,a.jggc,b.czr,b.df
from xxjg a
left join #zdf b
on a.czr=b.czr
where jggc=@JGGC and jgrq between @DATES and @DATEEgo
假设,明细表记录数为100条,汇总表记录数为3条。
想要将明细表和汇总表拼接,左联合时出现两个表记录数以明细表记录数为准,汇总表随之也出现100条
我想要的结果是明细表显示100条,拼接汇总表3条,3条以下以null值填充
跪求各大虾。联合明细表拼接汇总表
@JGGC NVARCHAR(50),
@DATES DATETIME,
@DATEE DATETIME
as
SELECT czr,convert(decimal(18,2),sum(df))'df'
into #zdf
FROM xxjg group by czr
select a.xh,a.ph,a.jgrq,a.jggc,b.czr,b.df
from (select *,rn=ROW_NUMBER() Over(PARTITION by czr order by getdate()) from xxjg) a
left join #zdf b on a.czr=b.czr and a.rn=1
where jggc=@JGGC and jgrq between @DATES and @DATEE
go
@JGGC NVARCHAR(50),
@DATES DATETIME,
@DATEE DATETIME
as
SELECT czr,convert(decimal(18,2),sum(df))'df' into #zdf FROM xxjg group by czrselect a.xh,a.ph,a.jgrq,a.jggc,b.czr,b.df
from (select rowid=row_number() over(PARTITION by czr order by getdate()),* from xxjg) a --这儿的ORDER BY可以换成其它的字段排序
left join #zdf b
on a.rowid = 1 and a.czr=b.czr --按czr分组后,只有第一条才关联
where jggc=@JGGC and jgrq between @DATES and @DATEEgo
大师,我看不懂select rowid=row_number() over(PARTITION by czr order by getdate()),* from xxjg
我的是SQL2000的
这句是什么意思,请赐教!
create proc sp_cltj_new
@JGGC NVARCHAR(50),
@DATES DATETIME,
@DATEE DATETIME
as
SELECT czr,convert(decimal(18,2),sum(df))'df' into #zdf FROM xxjg group by czr
select a.xh,a.ph,a.jgrq,a.jggc,b.czr,b.df
from
(
select *,
rowid=(SELECT COUNT(*) FROM xxjg n WHERE n.czr=m.czr AND n.id <= b.id) --注意:这儿的ID字段是xxjg表的自增列,如果此表无自增列,还得想其它方法
from xxjg m
) a
left join #zdf b
on a.rowid = 1 and a.czr=b.czr --按czr分组后,只有第一条才关联
where jggc=@JGGC and jgrq between @DATES and @DATEE
go
执行结果:
汇总表的czr,df两列为null(100条),xxjg表正常显示(100条记录)怎么会出现null呢?
这是一个简单的SQL,先把它看懂。理解了,再继续问。
1 5CSG 862 2013-8-1 bm 朱影 47 0 LCP
2 MGCC2 6494-p4 2013-8-1 bm 朱影 10 0 LCP
3 X040 Z306 2013-8-1 ks 孔祥 23 5.75 YZP
4 Y06 7350-p3 2013-8-2 bm 陈玲 2 0 LCP
6 C2G 8684 2013-8-3 dcfs 陈玲 48 9.6 LCP
7 YT6 7352-k2 2013-8-3 bm 陈玲 3 0.3 LCP
8 QB0 X209 2013-8-3 ks 陈佳佳 8 4.8 GCP
9 H4SG 8628 2013-8-4 dcfs 陈玲 48 0 LCP
10 Y6 7351-k2 2013-8-5 gk 张佳佳 4 0 LCP
11 X40 Z306 2013-8-16 ks 孔祥 23 9.2 YZP
12 C7SG 9009 2013-8-17 gk 周星 48 10.56 LCP
13 C2G 8101-k1 2013-8-17 cs 顾东星 9 3.6 LCP
14 H4SG 8510 2013-8-17 dcfs 陈玲 48 12 LCP
15 C2SG 8578 2013-8-17 zf 许凯丽 48 2.4 LCP
16 R9SG 9007 2013-8-18 ks 唐丽佳 48 0 LCP
17 R8SG 9004 2013-8-19 gk 周星 48 10.56 LCP
18 MGC2 6355 2013-8-20 zf 包承 34 1.7 LCP
19 MGA4 S031 2013-8-21 ks 王俊 50 5 YZP
20 XK40 Z306 2013-8-26 ks 孔祥 23 0 YZP