这个存储过程应该要怎么写，求助大神！

qq_15126639 2019-06-17 11:23:14

查询有两个主表JLLS和JLLSTMP 两个表是一样的结构一个是临时表一个是正式表需要union all这两个表，列有YHID,RQ,jymid,fsje,bmid
说明：这两个表jymid为30,31,38,40,每个YHID也就是用户id对应会有n行对应这4个jymid的记录，bmid 也就是部门id。
数据例如：
条件我要查RQ为6月1号到6月16号早上8点到10点的所有数据，YHID为1，jymid 为30,31,38,40。
YHID, RQ, jymid,fsje,bmid
1, 2019-06-17 08:01:01, 30, -10, 3
1, 2019-06-17 08:01:01, 31, 11, 3
1, 2019-06-17 08:01:01, 38, 100, 3
1, 2019-06-15 08:01:01, 38, 30, 3
1, 2019-06-17 08:01:01, 40, 150, 3
1, 2019-06-12 08:01:01, 38, 50, 3

1, 2019-06-17 08:01:01, 30, -10, 4
1, 2019-06-17 08:01:01, 31, -3, 4
1, 2019-06-17 08:01:01, 38, 100, 4
1, 2019-06-17 08:01:01, 40, 150, 4
上面数据是一个用户然后部门更换了，查询最后的结果是：
YHID,fsje1（jymid38的金额总和）,bs1（jymid38的笔数）,fsje2（jymid40的金额总和）,bs2（jymid40的笔数）,fsje3（jymid30的金额总和）,bs3（jymid30的笔数）,fsje4（jymid31的金额总和）,bs4（jymid31的笔数），Zje（总金额），Zbs（总笔数），bmid
1, 180, 3, 150, 1, -10, 1, 11, 1, 331, 6, 3
1, 100, 1, 150, 1, -10, 1, -3, 1, 237, 4, 4
这样的数据然后需要分页查询，还有这两个表的数据量比较大，能最优性能的查询最好。感谢大神们。

...全文

228 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

qq_15126639 2019-06-19

打赏
举报

引用 2 楼唐诗三百首的回复:

希望结果的字段数与数据字段数不匹配喔, 而且不太明白第2行的最后2个0是怎么算出来的.

就是看JLLS表中RQ的时间是否在Classtime表的时间范围内的数据统计

唐诗三百首 2019-06-19

打赏
举报

希望结果的字段数与数据字段数不匹配喔, 而且不太明白第2行的最后2个0是怎么算出来的.

qq_15126639 2019-06-19

打赏
举报

引用 3 楼唐诗三百首的回复:



create table JLLS(YHID int,RQ datetime,jymid int,fsje int,bmid int)

create table JLLSTMP(YHID int,RQ datetime,jymid int,fsje int,bmid int)



insert into JLLS

 select 1, '2019-06-17 08:01:01', 30, -10, 3 union all

 select 1, '2019-06-17 08:01:01', 31, 11, 3 union all

 select 1, '2019-06-17 08:01:01', 38, 100, 3 union all

 select 1, '2019-06-15 08:01:01', 38, 30, 3 union all

 select 1, '2019-06-17 08:01:01', 40, 150, 3 union all

 select 1, '2019-06-12 08:01:01', 38, 50, 3



insert into JLLSTMP

 select 1, '2019-06-17 08:01:01', 30, -10, 4 union all

 select 1, '2019-06-17 08:01:01', 31, -3, 4 union all

 select 1, '2019-06-17 08:01:01', 38, 100, 4 union all

 select 1, '2019-06-17 08:01:01', 40, 150, 4





with u as(

select YHID,bmid,jymid,fsje=sum(fsje),bs=count(1)

 from 

(select YHID,RQ,jymid,fsje,bmid

  from JLLS

 union all

 select YHID,RQ,jymid,fsje,bmid

  from JLLSTMP) t

where YHID=1 and jymid in(30,31,38,40)

group by YHID,bmid,jymid),

v as(

select YHID,bmid,val,

       col=col+case jymid when 38 then '1' when 40 then '2' when 30 then '3' when 31 then '4' else '' end

 from u

 unpivot(val for col in(fsje,bs)) p)

select a.YHID,a.[fsje1],a.[bs1],a.[fsje2],a.[bs2],a.[fsje3],a.[bs3],a.[fsje4],a.[bs4],

       b.Zje,b.Zbs,a.bmid

  from (select YHID,bmid,[fsje1],[bs1],[fsje2],[bs2],[fsje3],[bs3],[fsje4],[bs4]

        from v 

        pivot(max(val) for col in([fsje1],[bs1],[fsje2],[bs2],[fsje3],[bs3],[fsje4],[bs4])) p) a

  inner join (select YHID,bmid,

                     Zje=sum(case when col like 'fsje%' then val else 0 end),

                     Zbs=sum(case when col like 'bs%' then val else 0 end)

              from v

              group by YHID,bmid) b on a.YHID=b.YHID and a.bmid=b.bmid



/*

YHID        fsje1       bs1         fsje2       bs2         fsje3       bs3         fsje4       bs4         Zje         Zbs         bmid

----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------

1           180         3           150         1           -10         1           11          1           331         6           3

1           100         1           150         1           -10         1           -3          1           237         4           4



(2 行受影响)

*/

还有个问题请问：
YHID, RQ, jymid,fsje,bmid
1, 2019-06-17 08:01:01, 30, -10, 3
1, 2019-06-17 08:01:01, 31, 11, 3
1, 2019-06-17 08:01:01, 38, 100, 3
1, 2019-06-15 08:01:01, 38, 30, 3
1, 2019-06-17 08:01:01, 40, 150, 3
1, 2019-06-12 08:01:01, 38, 50, 3

1, 2019-06-17 08:01:01, 30, -10, 4
1, 2019-06-17 08:01:01, 31, -3, 4
1, 2019-06-17 08:01:01, 38, 100, 4
1, 2019-06-17 08:01:01, 40, 150, 4

还有一个表ClassTime 数据：
id time intime type timename
1 8：30 30 1 时间1
2. 17：30 20 2 时间2

id 1 的数据type为1是提前intime的时间也就是8点到8点30
id 2 就是17点30到17点50
然后我需要这两个表在之前的基础动态添加timename的名称为新增列名，并计算该时间的数据
结果：
YHID,fsje1（jymid38的金额总和）,bs1（jymid38的笔数）,fsje2（jymid40的金额总和）,bs2（jymid40的笔数）,fsje3（jymid30的金额总和）,bs3（jymid30的笔数）,fsje4（jymid31的金额总和）,bs4（jymid31的笔数），Zje（总金额），Zbs（总笔数），bmid，时间1金额，时间1笔数，时间2金额，时间2笔数
1, 180, 3, 150, 1, -10, 1, 11, 1, 331, 6, 3,331, 6
1, 100, 1, 150, 1, -10, 1, -3, 1, 237, 4, 4,0,0
这样需要怎么做到麻烦大佬了

唐诗三百首 2019-06-18

打赏
举报


create table JLLS(YHID int,RQ datetime,jymid int,fsje int,bmid int)
create table JLLSTMP(YHID int,RQ datetime,jymid int,fsje int,bmid int)

insert into JLLS
 select 1, '2019-06-17 08:01:01', 30, -10, 3 union all
 select 1, '2019-06-17 08:01:01', 31, 11, 3 union all
 select 1, '2019-06-17 08:01:01', 38, 100, 3 union all
 select 1, '2019-06-15 08:01:01', 38, 30, 3 union all
 select 1, '2019-06-17 08:01:01', 40, 150, 3 union all
 select 1, '2019-06-12 08:01:01', 38, 50, 3

insert into JLLSTMP
 select 1, '2019-06-17 08:01:01', 30, -10, 4 union all
 select 1, '2019-06-17 08:01:01', 31, -3, 4 union all
 select 1, '2019-06-17 08:01:01', 38, 100, 4 union all
 select 1, '2019-06-17 08:01:01', 40, 150, 4


with u as(
select YHID,bmid,jymid,fsje=sum(fsje),bs=count(1)
 from 
(select YHID,RQ,jymid,fsje,bmid
  from JLLS
 union all
 select YHID,RQ,jymid,fsje,bmid
  from JLLSTMP) t
where YHID=1 and jymid in(30,31,38,40)
group by YHID,bmid,jymid),
v as(
select YHID,bmid,val,
       col=col+case jymid when 38 then '1' when 40 then '2' when 30 then '3' when 31 then '4' else '' end
 from u
 unpivot(val for col in(fsje,bs)) p)
select a.YHID,a.[fsje1],a.[bs1],a.[fsje2],a.[bs2],a.[fsje3],a.[bs3],a.[fsje4],a.[bs4],
       b.Zje,b.Zbs,a.bmid
  from (select YHID,bmid,[fsje1],[bs1],[fsje2],[bs2],[fsje3],[bs3],[fsje4],[bs4]
        from v 
        pivot(max(val) for col in([fsje1],[bs1],[fsje2],[bs2],[fsje3],[bs3],[fsje4],[bs4])) p) a
  inner join (select YHID,bmid,
                     Zje=sum(case when col like 'fsje%' then val else 0 end),
                     Zbs=sum(case when col like 'bs%' then val else 0 end)
              from v
              group by YHID,bmid) b on a.YHID=b.YHID and a.bmid=b.bmid

/*
YHID        fsje1       bs1         fsje2       bs2         fsje3       bs3         fsje4       bs4         Zje         Zbs         bmid
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1           180         3           150         1           -10         1           11          1           331         6           3
1           100         1           150         1           -10         1           -3          1           237         4           4

(2 行受影响)
*/

qq_15126639 2019-06-17