ORDER BY 不行的因为语句运行后显示出来的就跟报表一样,很多汇总栏是空白,空白如果用order by就会被全部排到下面或者上面,而达不到我要
显示格式的要求,例如以下格式:
XX01
20090500038 0001 2301 20090500046 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
合计: 1000.000 19340.00 1000.000000 .000000 XX01
20090500039 0001 100 2301 20090500045 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
合计: 100.000 3465.00 100.000000 .000000 XX01
20090500040 0001 2301 20090500045 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
合计: 100.000 3465.00 100.000000 .000000
合计: 152945441.000 2533181600.20 128182828.000000 2160775972.210000
总计: 152945441.000 2533181600.20 128182828.000000 2160775972.210000 要求SQL查询出来就是这种格式,我内层已经实现,可嵌套外层联接顺序就乱了请问谁知道怎么解决打乱顺序的问题
显示格式的要求,例如以下格式:
XX01
20090500038 0001 2301 20090500046 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
小计: 1000.000 19340.00 1000.000000 .000000
合计: 1000.000 19340.00 1000.000000 .000000 XX01
20090500039 0001 100 2301 20090500045 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
合计: 100.000 3465.00 100.000000 .000000 XX01
20090500040 0001 2301 20090500045 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
小计: 100.000 3465.00 100.000000 .000000
合计: 100.000 3465.00 100.000000 .000000
合计: 152945441.000 2533181600.20 128182828.000000 2160775972.210000
总计: 152945441.000 2533181600.20 128182828.000000 2160775972.210000 要求SQL查询出来就是这种格式,我内层已经实现,可嵌套外层联接顺序就乱了请问谁知道怎么解决打乱顺序的问题
FROM (
SELECT grouping(dc201) as or1,grouping(dc202) as or2,grouping(dc203) as or3,
grouping(dc211) as or4,grouping(dc001) as or5,grouping(dc002) as or6,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC201 END DC201,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC202 END DC202,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DD203 END DD203,
CASE
WHEN GROUPING(DC201)=1 THEN '总计:'
WHEN GROUPING(DC202)=1 THEN '合计:'
WHEN GROUPING(DD203)=1 THEN '合计:'
WHEN GROUPING(DC211)=1 THEN '小计:'
WHEN GROUPING(OH001)=1 THEN '小计:'
WHEN GROUPING(OH002)=1 THEN '小计:'
ELSE DC211 END DC211,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH001 END OH001,
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH002 END OH002,
SUM(OH008) OH008,
SUM(OH035+OH036) OH035036,
SUM(OH204) OH204,
SUM((OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0351,
SUM(OH008-OH042-OH204) OH008042204,
SUM(OH035+OH036-(OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0352,
SUM(ISNULL(OA029,0)) OA029,
SUM(ISNULL(OA031,0)) OA031,
SUM(ISNULL(OA029,0)-ISNULL(OA031,0)) OA029031
FROM FGMDC
INNER JOIN FGMDD ON DC201=DD201 AND DC202=DD202
LEFT JOIN BVMOH ON OH001=DD205 AND OH002=DD206 AND OH003=DD207
LEFT JOIN BVMOG ON OH001=OG001 AND OH002=OG002
LEFT JOIN FGMOB ON OB005=OH001 AND OB006=OH002 AND OB007=OH003
LEFT JOIN FGMOA ON OB001=OA001 AND OB002=OA002
LEFT JOIN FGMOD ON OD006=OB001 AND OD007=OB002
LEFT JOIN FGMOC ON OD001=OC001 AND OD002=OC002
GROUP BY DC201,DC202,DD203,DC211,OH001,OH002 WITH ROLLUP
) A
LEFT JOIN FGMDC
ON A.DC201=FGMDC.DC201 AND A.DC202=FGMDC.DC202
order by a.or1,a.or2,......自己调试一下
FROM (
SELECT
CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC201 END DC201, CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DC202 END DC202, CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE DD203 END DD203, CASE
WHEN GROUPING(DC201)=1 THEN '总计:'
WHEN GROUPING(DC202)=1 THEN '合计:'
WHEN GROUPING(DD203)=1 THEN '合计:'
WHEN GROUPING(DC211)=1 THEN '小计:'
WHEN GROUPING(OH001)=1 THEN '小计:'
WHEN GROUPING(OH002)=1 THEN '小计:'
ELSE DC211 END DC211, CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH001 END OH001, CASE WHEN GROUPING(OH002)=1 THEN ''
ELSE OH002 END OH002,
SUM(OH008) OH008,
SUM(OH035 + OH036) OH035036,
SUM(OH204) OH204,
SUM((OH035+OH036)*( CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0351, SUM(OH008-OH042-OH204) OH008042204, SUM(OH035+OH036-(OH035+OH036)*(
CASE OH008
WHEN 0 THEN 0
ELSE OH042/OH008 END)*(
CASE OH042
WHEN 0 THEN 0
ELSE OH204/OH042 END)) OH0352, SUM(ISNULL(OA029,0)) OA029, SUM(ISNULL(OA031,0)) OA031, SUM(ISNULL(OA029,0)-ISNULL(OA031,0)) OA029031 FROM FGMDC
INNER JOIN FGMDD ON DC201=DD201 AND DC202=DD202
LEFT JOIN BVMOH ON OH001=DD205 AND OH002=DD206 AND OH003=DD207
LEFT JOIN BVMOG ON OH001=OG001 AND OH002=OG002
LEFT JOIN FGMOB ON OB005=OH001 AND OB006=OH002 AND OB007=OH003
LEFT JOIN FGMOA ON OB001=OA001 AND OB002=OA002
LEFT JOIN FGMOD ON OD006=OB001 AND OD007=OB002
LEFT JOIN FGMOC ON OD001=OC001 AND OD002=OC002
GROUP BY DC201,DC202,DD203,DC211,OH001,OH002 WITH ROLLUP
) A
LEFT JOIN FGMDC ON A.DC201=FGMDC.DC201 AND A.DC202=FGMDC.DC202这样应该没问题了吧。
前面省略共8w多条,规律应该是以下
0 0 0 0 0 0
0 0 0 0 0 1
0 0 0 0 1 1
0 0 0 1 1 1
0 0 1 1 1 1
0 0 0 0 0 0
0 0 0 0 0 1
0 0 0 0 1 1
0 0 0 1 1 1
0 0 1 1 1 1
0 1 1 1 1 1
1 1 1 1 1 1
我在外层用GROUPING()排序后的结果是or1排完再用or2........or6
结果大相径庭:
同样省略前面只给出最后,规律完全不一样
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 0 1 1 1 1
0 1 1 1 1 1
1 1 1 1 1 1
难道是因为联接和with rollup计算量过大导致数据溢出丢失?小弟诚信请教,还望赐教
不要觉得临时表麻烦,其实很有用的。
不同的用户都可以创建相同名称的临时表,并不冲突。