汇总排序问题(要求系统开支小)

Layto 2009-04-16 12:41:20
我现在SQL语句如下：对某种商品，按商品类型、月份、票号汇总（goodid,convert(char(7),MAKEDATE,120),union_id ）
现在我得出的结果没有按MAKEDATE排序，请问结果要按MAKEDATE asc按序显示,(wxlx 为视图)
表数据太多，只求解决办法.
select
grouping(goodid),
grouping(convert(char(7),MAKEDATE,120)),grouping(union_id),

(case when grouping(goodid)=1 then '总计' when grouping(convert(char(7),MAKEDATE,120))=1 then '合计' when grouping(union_id)=1 then '小计' else max(CT_Name) end) as dwmc,
(case when grouping(union_id)=1 then null else max(MAKEDATE) end) as MAKEDATE,
(case when grouping(union_id)=1 then null else max(CT_Code) end) as ct_code,
(case when grouping(union_id)=1 then null else max(GD_Name) end) as GD_Name,
(case when grouping(union_id)=1 then null else max(GD_Spec) end) as GD_Spec,
(case when grouping(union_id)=1 then null else max(BTHNUM) end) as BTHNUM,
(case when grouping(union_id)=1 then sum(QTY_J) else max(QTY_J) end) as QTY_J,
(case when grouping(union_id)=1 then sum(QTY_x) else max(QTY_x) end) as QTY_x,
(case when grouping(union_id)=1 then null else max(GD_Unit) end) as GD_Unit,
(case when grouping(union_id)=1 then null else max(TAXPRICE) end) as TAXPRICE,
(case when grouping(union_id)=1 then null else max(BILLTYPE) end) as BILLTYPE,
(case when grouping(union_id)=1 then null else max(GD_Manufacture) end) as GD_Manufacture,
(case when grouping(union_id)=1 then null else max(CHKNO) end) as CHKNO,
(case when grouping(union_id)=1 then null else max(GD_Code) end) as GD_Code


from wxlx
where goodID in(SELECT lg_goodid FROM Login_GID where lg_cstid='899987 ' and lg_type='00')
and MAKEDATE>='2009-2-1' and MAKEDATE<='2009-3-31 23:59:59'
group by goodid,convert(char(7),MAKEDATE,120),union_id with rollup
...全文
132 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
Layto 2009-04-20
  • 打赏
  • 举报
 回复
自已顶，
Layto 2009-04-16
  • 打赏
  • 举报
 回复
taoistong你把with rollup丢了 ，在最后在最中加order by MAKEDATE asc整个小计，合计的排序全乱了
ws_hgo 2009-04-16
  • 打赏
  • 举报
 回复 


select 

grouping(goodid), 

grouping(convert(char(7),MAKEDATE,120)),grouping(union_id), 

(case when grouping(goodid)=1 then '总计' when grouping(convert(char(7),MAKEDATE,120))=1 then '合计' when grouping(union_id)=1 then '小计' else max(CT_Name) end) as dwmc, 

(case when grouping(union_id)=1 then null else  max(MAKEDATE) end) as MAKEDATE, 

(case when grouping(union_id)=1 then null else  max(CT_Code) end) as ct_code, 

(case when grouping(union_id)=1 then null else  max(GD_Name) end) as GD_Name, 

(case when grouping(union_id)=1 then null else  max(GD_Spec) end) as GD_Spec, 

(case when grouping(union_id)=1 then null else  max(BTHNUM) end) as BTHNUM, 

(case when grouping(union_id)=1 then sum(QTY_J) else max(QTY_J) end) as QTY_J, 

(case when grouping(union_id)=1 then sum(QTY_x) else max(QTY_x)  end) as QTY_x, 

(case when grouping(union_id)=1 then null else  max(GD_Unit) end) as GD_Unit, 

(case when grouping(union_id)=1 then null else  max(TAXPRICE) end) as TAXPRICE, 

(case when grouping(union_id)=1 then null else  max(BILLTYPE) end) as BILLTYPE, 

(case when grouping(union_id)=1 then null else  max(GD_Manufacture) end) as GD_Manufacture, 

(case when grouping(union_id)=1 then null else  max(CHKNO) end) as CHKNO, 

(case when grouping(union_id)=1 then null else  max(GD_Code) end) as GD_Code 

from wxlx 

where goodID in(SELECT lg_goodid FROM Login_GID where lg_cstid='899987 ' and lg_type='0') 

and MAKEDATE>='2009-2-1' and MAKEDATE <='2009-3-31 23:59:59' 

group by goodid,convert(char(7),MAKEDATE,120),union_id  with  rollup order by MAKEDATE asc
taoistong 2009-04-16
  • 打赏
  • 举报
 回复
这个可以创建视图
taoistong 2009-04-16
  • 打赏
  • 举报
 回复 




select top 1000000

grouping(goodid), 

grouping(convert(char(7),MAKEDATE,120)),grouping(union_id), 



(case when grouping(goodid)=1 then '总计' when grouping(convert(char(7),MAKEDATE,120))=1 then '合计' when grouping(union_id)=1 then '小计' else max(CT_Name) end) as dwmc, 

(case when grouping(union_id)=1 then null else  max(MAKEDATE) end) as MAKEDATE, 

(case when grouping(union_id)=1 then null else  max(CT_Code) end) as ct_code, 

(case when grouping(union_id)=1 then null else  max(GD_Name) end) as GD_Name, 

(case when grouping(union_id)=1 then null else  max(GD_Spec) end) as GD_Spec, 

(case when grouping(union_id)=1 then null else  max(BTHNUM) end) as BTHNUM, 

(case when grouping(union_id)=1 then sum(QTY_J) else max(QTY_J) end) as QTY_J, 

(case when grouping(union_id)=1 then sum(QTY_x) else max(QTY_x)  end) as QTY_x, 

(case when grouping(union_id)=1 then null else  max(GD_Unit) end) as GD_Unit, 

(case when grouping(union_id)=1 then null else  max(TAXPRICE) end) as TAXPRICE, 

(case when grouping(union_id)=1 then null else  max(BILLTYPE) end) as BILLTYPE, 

(case when grouping(union_id)=1 then null else  max(GD_Manufacture) end) as GD_Manufacture, 

(case when grouping(union_id)=1 then null else  max(CHKNO) end) as CHKNO, 

(case when grouping(union_id)=1 then null else  max(GD_Code) end) as GD_Code 





from wxlx 

where goodID in(SELECT lg_goodid FROM Login_GID where lg_cstid='899987 ' and lg_type='00') 

and MAKEDATE>='2009-2-1' and MAKEDATE <='2009-3-31 23:59:59' 

group by goodid,convert(char(7),MAKEDATE,120),union_id  

order by MAKEDATE asc
ws_hgo 2009-04-16
  • 打赏
  • 举报
 回复
看下
htl258_Tony 2009-04-16
  • 打赏
  • 举报
 回复
代码看来看有点累，后面加order by 排序后有什么问题？
让你望见影子的墙 2009-04-16
  • 打赏
  • 举报
 回复 
from wxlx 

where goodID in(SELECT lg_goodid FROM Login_GID where lg_cstid='899987 ' and lg_type='00') 



改为



from wxlx join lg_goodid on wxlx.gooid=lg_goodid .goodid and lg_cstid='899987 ' and lg_type='00'



另外给你个列子，觉得你的case写的比较繁琐。



DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)

INSERT @t SELECT 'aa','Table','Blue', 124

UNION ALL SELECT 'bb','Table','Red',  -23

UNION ALL SELECT 'bb','Cup'  ,'Green',-23

UNION ALL SELECT 'aa','Chair','Blue', 101

UNION ALL SELECT 'aa','Chair','Red',  -90



--汇总显示

SELECT Groups=CASE 

		WHEN GROUPING(Color)=0 THEN Groups

		WHEN GROUPING(Groups)=1 THEN '总计'

		ELSE '' END,

	Item=CASE 

		WHEN GROUPING(Color)=0 THEN Item

		WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计'

		ELSE '' END,

	Color=CASE 

		WHEN GROUPING(Color)=0 THEN Color

		WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计'

		ELSE '' END,

	Quantity=SUM(Quantity)

FROM @t

GROUP BY Groups,Item,Color WITH ROLLUP

/*--结果

Groups Item       Color           Quantity    

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

aa     Chair      Blue            101

aa     Chair      Red             -90

                 Chair 小计       11

aa     Table      Blue            124

                 Table 小计       124

       aa 合计                    135

bb     Cup        Green           -23

                  Cup 小计        -23

bb     Table      Red             -23

                 Table 小计       -23

       bb 合计                    -46

总计                              89

--*/
Layto 2009-04-16
  • 打赏
  • 举报
 回复
道士佟这办法可行，top 10000假若20000，那我top 20000了，有没有更好的办法吗
play7788 2009-04-16
  • 打赏
  • 举报
 回复
嗯。
taoistong 2009-04-16
  • 打赏
  • 举报
 回复 






create view v_taoistong as 



select top 10000 * from 

(select 

grouping(goodid), 

grouping(convert(char(7),MAKEDATE,120)) make_date,grouping(union_id), 



(case when grouping(goodid)=1 then '总计' when grouping(convert(char(7),MAKEDATE,120))=1 then '合计' when grouping(union_id)=1 then '小计' else max(CT_Name) end) as dwmc, 

(case when grouping(union_id)=1 then null else  max(MAKEDATE) end) as MAKEDATE, 

(case when grouping(union_id)=1 then null else  max(CT_Code) end) as ct_code, 

(case when grouping(union_id)=1 then null else  max(GD_Name) end) as GD_Name, 

(case when grouping(union_id)=1 then null else  max(GD_Spec) end) as GD_Spec, 

(case when grouping(union_id)=1 then null else  max(BTHNUM) end) as BTHNUM, 

(case when grouping(union_id)=1 then sum(QTY_J) else max(QTY_J) end) as QTY_J, 

(case when grouping(union_id)=1 then sum(QTY_x) else max(QTY_x)  end) as QTY_x, 

(case when grouping(union_id)=1 then null else  max(GD_Unit) end) as GD_Unit, 

(case when grouping(union_id)=1 then null else  max(TAXPRICE) end) as TAXPRICE, 

(case when grouping(union_id)=1 then null else  max(BILLTYPE) end) as BILLTYPE, 

(case when grouping(union_id)=1 then null else  max(GD_Manufacture) end) as GD_Manufacture, 

(case when grouping(union_id)=1 then null else  max(CHKNO) end) as CHKNO, 

(case when grouping(union_id)=1 then null else  max(GD_Code) end) as GD_Code 

from wxlx 

where goodID in(SELECT lg_goodid FROM Login_GID where lg_cstid='899987 ' and lg_type='00') 

and MAKEDATE>='2009-2-1' and MAKEDATE <='2009-3-31 23:59:59' 

group by goodid,convert(char(7),MAKEDATE,120),union_id  with  rollup

) a order by make_date asc


这下可以了，结贴吧
zxb9118b 2009-04-16
  • 打赏
  • 举报
 回复
楼上的解法是对的，得汇总，不然肯定乱的
sdhdy 2009-04-16
  • 打赏
  • 举报
 回复
楼主，你得把小计、合计和group by 那些列表项结合起来才可以的，要不怎么排序。
给你个例子。
DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)

INSERT @t SELECT 'aa','Table','Blue', 124

UNION ALL SELECT 'bb','Table','Red',  -23

UNION ALL SELECT 'bb','Cup'  ,'Green',-23

UNION ALL SELECT 'aa','Chair','Blue', 101

UNION ALL SELECT 'aa','Chair','Red',  -90



SELECT Groups=CASE  

        WHEN GROUPING(Groups)=1 THEN '总计'

		WHEN GROUPING(ITEM)=1 AND GROUPING(color)=1 THEN Groups+'合计'

        ELSE Groups END,

    Item=CASE 

        WHEN GROUPING(color)=1 AND GROUPING(Item)=0 THEN item+' 合计'

		WHEN GROUPING(color)=1 AND GROUPING(Item)=1 THEN ''

        ELSE Item END,

    Color=CASE 

        WHEN GROUPING(Color)=0 THEN Color

        ELSE '' END,

    Quantity=SUM(Quantity)

FROM @t

GROUP BY Groups,Item,Color WITH ROLLUP

/*

Groups Item            Color      Quantity    

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

aa     Chair           Blue       101

aa     Chair           Red        -90

aa     Chair 合计                   11

aa     Table           Blue       124

aa     Table 合计                   124

aa合计                              135

bb     Cup             Green      -23

bb     Cup 合计                     -23

bb     Table           Red        -23

bb     Table 合计                   -23

bb合计                              -46

总计                                89



（所影响的行数为 12 行）

*/
claro 2009-04-16
  • 打赏
  • 举报
 回复
帮顶。
Layto 2009-04-16
  • 打赏
  • 举报
 回复
简化一下条件
select
grouping(goodid),
grouping(convert(char(7),MAKEDATE,120)),grouping(union_id),

(case when grouping(goodid)=1 then '总计' when grouping(convert(char(7),MAKEDATE,120))=1 then '合计' when grouping(union_id)=1 then '小计' else max(CT_Name) end) as dwmc,
(case when grouping(union_id)=1 then null else max(MAKEDATE) end) as MAKEDATE,
(case when grouping(union_id)=1 then null else max(CT_Code) end) as ct_code,
(case when grouping(union_id)=1 then null else max(GD_Name) end) as GD_Name,
(case when grouping(union_id)=1 then null else max(GD_Spec) end) as GD_Spec,
(case when grouping(union_id)=1 then null else max(BTHNUM) end) as BTHNUM,
(case when grouping(union_id)=1 then sum(QTY_J) else max(QTY_J) end) as QTY_J,
(case when grouping(union_id)=1 then sum(QTY_x) else max(QTY_x) end) as QTY_x,
(case when grouping(union_id)=1 then null else max(GD_Unit) end) as GD_Unit,
(case when grouping(union_id)=1 then null else max(TAXPRICE) end) as TAXPRICE,
(case when grouping(union_id)=1 then null else max(BILLTYPE) end) as BILLTYPE,
(case when grouping(union_id)=1 then null else max(GD_Manufacture) end) as GD_Manufacture,
(case when grouping(union_id)=1 then null else max(CHKNO) end) as CHKNO,
(case when grouping(union_id)=1 then null else max(GD_Code) end) as GD_Code

from wxlx group by goodid,convert(char(7),MAKEDATE,120),union_id with rollup
Layto 2009-04-16
  • 打赏
  • 举报
 回复
请大家不要考虑显示字段,关键在小计、合计后，排序问题
新源广告业务管理系统网络版广告公司业务管理系统
新源广告公司业务管理系统（以下简称...（6）数据库稳定，数据的安全是所有客户都比较关心的问题，本系统采用的是MS SQL Server数据库，其是目前管理系统采用的主流数据库系统，数据运行速度快，运算处理，安全稳定。
c++排序算法详解【汇总版】（附代码）
当作考试前复习一下了。都是非递归写法实现的，都只实现升序。此处需要排序的都是int型的数组，而且都是从0开始存的数字。所有的排序函数都只需要两个参数：数组的头指针和数组的长度。直接插入排序。
Excel数据管理系列课程Execl排序与分类汇总
要使制作或设计的工作表更加具有可读和容易分析，可以通过一些常用的管理来实现，包括排序、分类汇总和筛选等。本课程将介绍相关的知识和技巧。
Excel2010数据透视表中的筛选排序
大多数时候我们需要利用到排序这个功能。最近我参加了国内著名的Excel网站Excelhome。初入Excelhome 培训中心，很感谢Kevin、BIN_YANG168和其他前辈的指点。在培训中使用的是Excel2003，所以在这里我主要介绍一下在...
排序算法大汇总（C++实现）
文章目录直接插入排序(insert sort)折半插入排序(binary insert sort)希尔排序(shell sort)冒泡排序(bubble sort)快速排序(quick sort)简单选择排序(selection sort)堆排序(heap sort)归并排序(merge sort)基数排序...
MS-SQL Server

34,588

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章