统计汇总的存储过程

Question

select case GuestName when '小计' then '小计' else GuestNumber end as GuestNumber
,case GuestName when '小计' then '' else GuestName end as GuestName
,sun_GuestMoney from(
SELECT CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计' 
            ELSE ISNULL(GuestNumber, 'UNKNOWN') 
      END AS name, 
      CASE WHEN (GROUPING(GuestNumber) = 1) THEN '' 
            when(grouping(GuestName)=1)then'小计' 
            ELSE ISNULL(GuestName, 'UNKNOWN') 
      END AS sun_GuestMoney, 
      SUM(GuestMoney) /*AS*/'sun_GuestMoney'
FROM OpenRoomInfo
GROUP BY GuestNumber,GuestName WITH ROLLUP )bb
go系统提示错误如下：
服务器: 消息 207，级别 16，状态 1，行 1
列名 'GuestName' 无效。
服务器: 消息 207，级别 16，状态 1，行 1
列名 'GuestNumber' 无效。
服务器: 消息 207，级别 16，状态 1，行 2
列名 'GuestName' 无效。
服务器: 消息 207，级别 16，状态 1，行 2
列名 'GuestName' 无效。
服务器: 消息 8156，级别 16，状态 1，行 1
多次为 'bb' 指定了列 'sun_GuestMoney'。
望各位大侠给予解决！

Answer 1

表结构脚本如下：
USE [MyHotelManager]
GO
/****** 对象:  Table [dbo].[OpenRoomInfo]    脚本日期: 03/27/2009 12:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OpenRoomInfo](
[OpenRoomId] [int] IDENTITY(1,1) NOT NULL,
[RoomId] [int] NOT NULL,
[GuestNumber] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GuestName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[GuestMoney] [money] NOT NULL,
[OpenTime] [datetime] NOT NULL DEFAULT (getdate()),
[Re] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
[OpenRoomId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]GO
SET ANSI_PADDING OFF
GO
USE [MyHotelManager]
GO
ALTER TABLE [dbo].[OpenRoomInfo]  WITH CHECK ADD FOREIGN KEY([RoomId])
REFERENCES [dbo].[Room] ([RoomId])

Answer 2

select 
    case GuestName when '小计' then '小计' else GuestNumber end as GuestNumber 
   ,case GuestName when '小计' then '' else GuestName end as GuestName 
   ,sun_GuestMoney from( 
                        SELECT CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计' 
                                    ELSE ISNULL(GuestNumber, 'UNKNOWN') 
                                END AS name, 
                               CASE WHEN (GROUPING(GuestNumber) = 1) THEN '' 
                                     when(grouping(GuestName)=1)then'小计' 
                                     ELSE ISNULL(GuestName, 'UNKNOWN') 
                                END AS sun_GuestMoney, 
                           SUM(GuestMoney)  sun_GuestMoney_1 --这里列名与上一个重了。
                        FROM OpenRoomInfo 
                        GROUP BY GuestNumber,GuestName
                            WITH ROLLUP
                        ) bb 
go 

Answer 3

select 
    case GuestName when '小计' then '小计' else GuestNumber end as GuestNumber 
   ,case GuestName when '小计' then '' else GuestName end as GuestName 
   ,sun_GuestMoney from( 
                        SELECT CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计' 
                                    ELSE ISNULL(GuestNumber, 'UNKNOWN') 
                                END AS name, 
                               CASE WHEN (GROUPING(GuestNumber) = 1) THEN '' 
                                     when(grouping(GuestName)=1)then'小计' 
                                     ELSE ISNULL(GuestName, 'UNKNOWN') 
                                END AS sun_GuestMoney, 
                           SUM(GuestMoney)  sun_GuestMoney_1
                        FROM OpenRoomInfo 
                        GROUP BY CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计' 
                                      ELSE ISNULL(GuestNumber, 'UNKNOWN') 
                                  END,
                                 CASE WHEN (GROUPING(GuestNumber) = 1) THEN '' 
                                      when(grouping(GuestName)=1)then'小计' 
                                      ELSE ISNULL(GuestName, 'UNKNOWN') 
                                  END
                            WITH ROLLUP
                      ) bb 
go 这样试试

Answer 4

--TRY：
select case GuestName when '小计' then '小计' else GuestNumber end as GuestNumber
,case GuestName when '小计' then '' else GuestName end as GuestName
,sun_GuestMoney
 from(
SELECT CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计'
            ELSE ISNULL(GuestNumber, 'UNKNOWN')
      END AS name,
      CASE WHEN (GROUPING(GuestNumber) = 1) THEN ''
            when(grouping(GuestName)=1)then'小计'
            ELSE ISNULL(GuestName, 'UNKNOWN')
      END AS GuestName,
      SUM(GuestMoney) as [sun_GuestMoney]
FROM OpenRoomInfo
GROUP BY GuestNumber,GuestName WITH ROLLUP )bb
go 

Answer 5

表结构：OpenRoomId int         Unchecked
RoomId         int         Unchecked
GuestNumber varchar(100) Unchecked
GuestName varchar(50) Unchecked
GuestMoney decimal(18, 0) Unchecked
OpenTime datetime Unchecked
Re         varchar(200) Checked

测试数据：
1 1 12345 chen 200 2008-9-1 8:54:06 才来的
2 2 54321 elva 200 2008-9-1 8:54:06 才来的
3 4 45678 jay 300 2008-9-2 8:54:06 才来的
4 5 78945 lele 400 2008-9-2 8:54:06 才来的
5 9 65487 aaaa 400 2008-9-3 8:54:06 才来的
6 10 32158 bbbb 500 2008-9-3 8:54:06 才来的
7 15 98789 cccc 500 2008-9-4 8:54:06 才来的
8 19 58256 dddd 800 2008-9-4 8:54:06 才来的
9 21 96965 eeee 1000 2008-9-5 8:54:06 不晓得
10 3 51235 kkkk 500 2008-9-5 9:29:09 已退想要的结果：
2008-9-1 8:54:06 chen 200
2008-9-1 8:54:06 elva 200
小计                   400
2008-9-2 8:54:06 jay 200
2008-9-2 8:54:06 lele 300
小计                   500
2008-9-3 8:54:06 aaaa 400
2008-9-3 8:54:06 bbbb 500
小计                   900
总计                   1800

Answer 6

CREATE TABLE [dbo].[OpenRoomInfo]( 
[OpenRoomId] [int] IDENTITY(1,1) NOT NULL, 
[RoomId] [int] NOT NULL, 
[GuestNumber] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL, 
[GuestName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL, 
[GuestMoney] [money] NOT NULL, 
[OpenTime] [datetime] NOT NULL DEFAULT (getdate()), 
[Re] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL
)insert OpenRoomInfo
select 1,12345,'chen',200,'2008-9-1 8:54:06','才来的' union all
select 2,54321,'elva',200,'2008-9-1 8:54:06','才来的' union all
select 4,45678,'jay',300,'2008-9-2 8:54:06','才来的' union all
select 5,78945,'lele',400,'2008-9-2 8:54:06','才来的' union all
select 9,65487,'aaaa',400,'2008-9-3 8:54:06','才来的' union all
select 10,32158,'bbbb',500,'2008-9-3 8:54:06','才来的' union all
select 15,98789,'cccc',500,'2008-9-4 8:54:06','才来的' union all
select 19,58256,'dddd',800,'2008-9-4 8:54:06','才来的' union all
select 21,96965,'eeee',1000,'2008-9-5 8:54:06','不晓得' union all
select 3,51235,'kkkk',500,'2008-9-5 9:29:09','已退'select 
  isnull(convert(char(19),OpenTime,120),'总计') as [OpenTime],
  isnull(GuestName,'小计') as [GuestName],
  sum(GuestMoney) as [GuestMoney] 
from OpenRoomInfo 
where OpenTime<'2008-09-04'
group by OpenTime,GuestName 
with rollupdrop table OpenRoomInfo/**
OpenTime            GuestName                                          GuestMoney            
------------------- -------------------------------------------------- --------------------- 
2008-09-01 08:54:06 chen                                               200.0000
2008-09-01 08:54:06 elva                                               200.0000
2008-09-01 08:54:06 小计                                                 400.0000
2008-09-02 08:54:06 jay                                                300.0000
2008-09-02 08:54:06 lele                                               400.0000
2008-09-02 08:54:06 小计                                                 700.0000
2008-09-03 08:54:06 aaaa                                               400.0000
2008-09-03 08:54:06 bbbb                                               500.0000
2008-09-03 08:54:06 小计                                                 900.0000
总计                  小计                                                 2000.0000（所影响的行数为 10 行）
**/
看看这个格式不是你想要的？

Answer 7

再调一下格式
select 
  case when len(guestname)=0 and OpenTime<>'总计' then '小计' else OpenTime end as [OpenTime],
  GuestName,
  GuestMoney
from
(
select 
  isnull(convert(char(19),OpenTime,120),'总计') as [OpenTime],
  isnull(GuestName,' ') as [GuestName],
  sum(GuestMoney) as [GuestMoney] 
from OpenRoomInfo 
where OpenTime<'2008-09-04'
group by OpenTime,GuestName 
with rollup) tdrop table OpenRoomInfo/**
OpenTime            GuestName                                          GuestMoney            
------------------- -------------------------------------------------- --------------------- 
2008-09-01 08:54:06 chen                                               200.0000
2008-09-01 08:54:06 elva                                               200.0000
小计                                                                     400.0000
2008-09-02 08:54:06 jay                                                300.0000
2008-09-02 08:54:06 lele                                               400.0000
小计                                                                     700.0000
2008-09-03 08:54:06 aaaa                                               400.0000
2008-09-03 08:54:06 bbbb                                               500.0000
小计                                                                     900.0000
总计                                                                     2000.0000（所影响的行数为 10 行）
**/

Answer 8

修改后的sql
select case bb.GuestName when '小计' then '小计' else bb.GuestNumber end as GuestNumber 
,case bb.GuestName when '小计' then '' else bb.GuestName end as GuestName 
,sun_GuestMoney from( 
SELECT CASE WHEN (GROUPING(GuestNumber) = 1) THEN '总计' 
            ELSE ISNULL(GuestNumber, 'UNKNOWN') 
      END AS name, 
      CASE WHEN (GROUPING(GuestNumber) = 1) THEN '' 
            when(grouping(GuestName)=1)then'小计' 
            ELSE ISNULL(GuestName, 'UNKNOWN') 
      END AS sun_GuestMoney, 
      SUM(GuestMoney) /*AS*/'sun_GuestMoney1' 
FROM OpenRoomInfo 
GROUP BY GuestNumber,GuestName WITH ROLLUP )bb 
go 

Answer 9

(SELECT CASE
                 WHEN (GROUPING(GuestNumber) = 1) THEN
                  '总计'
                 ELSE
                  ISNULL(GuestNumber, 'UNKNOWN')
               END AS name,
               CASE
                 WHEN (GROUPING(GuestNumber) = 1) THEN
                  ''
                 when (grouping(GuestName) = 1) THEN
                  '小计'
                 ELSE
                  ISNULL(GuestName, 'UNKNOWN')
               END AS sun_GuestMoney,
               SUM(GuestMoney) 'sun_GuestMoney'
          FROM OpenRoomInfo
         GROUP BY GuestNumber, GuestName WITH ROLLUP)
这是你内层查询获得内容  并自定义了名为 bb   就相当于这些字段在一个bb的表里那么外层再查询字段   就要加上你自定义的名称  不然就会找不到字段的select case bb.GuestName
         when '小计' then
          '小计'
         else
          bb.GuestNumber
       end as GuestNumber,
       case bb.GuestName
         when '小计' then
          ''
         else
          bb.GuestName
       end as GuestName,
      bb.sun_GuestMoney
  from (SELECT CASE
                 WHEN (GROUPING(GuestNumber) = 1) THEN
                  '总计'
                 ELSE
                  ISNULL(GuestNumber, 'UNKNOWN')
               END AS name,
               CASE
                 WHEN (GROUPING(GuestNumber) = 1) THEN
                  ''
                 when (grouping(GuestName) = 1) THEN
                  '小计'
                 ELSE
                  ISNULL(GuestName, 'UNKNOWN')
               END AS sun_GuestMoney,
               SUM(GuestMoney) as 'sun_GuestMoney'
          FROM OpenRoomInfo
         GROUP BY GuestNumber, GuestName WITH ROLLUP) bb go

Answer 10

恩！是这样的，谢谢！如果通过存储过程的实现的话，出现如下错误！
解决此问题结贴！过程如下：
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable   nvarchar(100),               -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon   int = 0                --   台面钱数
AS 
BEGIN
select case '@GuestNa' when '小计' then '小计' else '@GuestNum' end as '@GuestNum'
,case '@GuestNa' when '小计' then '' else '@GuestNa' end as '@GuestNa'
,sun_GuestMoney
from(
SELECT CASE WHEN (GROUPING('@GuestNum') = 1) THEN '总计'
            ELSE ISNULL('@GuestNum', 'UNKNOWN')
      END AS '@GuestNum',
      CASE WHEN (GROUPING('@GuestNum') = 1) THEN ''
            when(grouping('@GuestNa')=1)then'小计'
            ELSE ISNULL('@GuestNa', 'UNKNOWN')
      END AS '@GuestNa',
      SUM('@GuestMon') as [sun_GuestMoney]
FROM @GuestTable
GROUP BY '@GuestNum','@GuestNa' WITH ROLLUP )bb
--go
end消息 1087，级别 15，状态 2，过程 GusetInfo，第 20 行
必须声明表变量 "@GuestTable"。
消息 319，级别 15，状态 1，过程 GusetInfo，第 21 行
关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

Answer 11

或者如何把这段SQL语句通过存储过程实现呢！
我就是在这段语句的基础上定义的存储过程并且声明了其中相关的输入参数，可是有语法错误，小弟水平有限，但是有分，解决结贴！

Answer 12

在存储过程中用到参数的SQL语句,要转换成动态组建SQL语句的形式才行
set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable  nvarchar(100),              -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon  int = 0                --  台面钱数 
AS 
DECLARE @SQL NVARCHAR(MAX)
BEGIN 
SET @SQL=N'select case '+@GuestNa+' when '''小计''' then '''小计''' else ''''+@GuestNum+'''' end as '+@GuestNum+N' 
,case '+@GuestNa+N' when '''小计''' then '''''' else ''''+@GuestNa+'''' end as '+@GuestNa+N' 
,sun_GuestMoney 
from( 
SELECT CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''总计''' 
            ELSE ISNULL('+@GuestNum+N', '''UNKNOWN''') 
      END AS '+@GuestNum+N', 
      CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''''' 
            when(grouping('+@GuestNa+N')=1)then'''小计''' 
            ELSE ISNULL('+@GuestNa+N', '''UNKNOWN''') 
      END AS '+@GuestNa+N', 
      SUM('+@GuestMon+N') as [sun_GuestMoney] 
FROM '+@GuestTable+N' 
GROUP BY '+@GuestNum+N','+@GuestNa+N' WITH ROLLUP )bb 
EXEC SP_SQLEXEC
--go 
end 

Answer 13

在存储过程中用到参数的SQL语句,要转换成动态组建SQL语句的形式才行
set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable  nvarchar(100),              -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon  int = 0                --  台面钱数 
AS 
DECLARE @SQL NVARCHAR(MAX)
BEGIN 
SET @SQL=N'select case '+@GuestNa+' when '''小计''' then '''小计''' else ''''+@GuestNum+'''' end as '+@GuestNum+N' 
,case '+@GuestNa+N' when '''小计''' then '''''' else ''''+@GuestNa+'''' end as '+@GuestNa+N' 
,sun_GuestMoney 
from( 
SELECT CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''总计''' 
            ELSE ISNULL('+@GuestNum+N', '''UNKNOWN''') 
      END AS '+@GuestNum+N', 
      CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''''' 
            when(grouping('+@GuestNa+N')=1)then'''小计''' 
            ELSE ISNULL('+@GuestNa+N', '''UNKNOWN''') 
      END AS '+@GuestNa+N', 
      SUM('+@GuestMon+N') as [sun_GuestMoney] 
FROM '+@GuestTable+N' 
GROUP BY '+@GuestNum+N','+@GuestNa+N' WITH ROLLUP )bb 
EXEC SP_SQLEXEC
--go 
end 

Answer 14

在存储过程中用到参数的SQL语句,要转换成动态组建SQL语句的形式才行
set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable  nvarchar(100),              -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon  int = 0                --  台面钱数 
AS 
DECLARE @SQL NVARCHAR(MAX)
BEGIN 
SET @SQL=N'select case '+@GuestNa+' when '''小计''' then '''小计''' else ''''+@GuestNum+'''' end as '+@GuestNum+N' 
,case '+@GuestNa+N' when '''小计''' then '''''' else ''''+@GuestNa+'''' end as '+@GuestNa+N' 
,sun_GuestMoney 
from( 
SELECT CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''总计''' 
            ELSE ISNULL('+@GuestNum+N', '''UNKNOWN''') 
      END AS '+@GuestNum+N', 
      CASE WHEN (GROUPING('+@GuestNum+N') = 1) THEN '''''' 
            when(grouping('+@GuestNa+N')=1)then'''小计''' 
            ELSE ISNULL('+@GuestNa+N', '''UNKNOWN''') 
      END AS '+@GuestNa+N', 
      SUM('+@GuestMon+N') as [sun_GuestMoney] 
FROM '+@GuestTable+N' 
GROUP BY '+@GuestNum+N','+@GuestNa+N' WITH ROLLUP )bb 
EXEC SP_SQLEXEC
--go 
end 

Answer 15

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable  nvarchar(100),              -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon  int = 0                --  台面钱数 
AS 
BEGIN 
set nocount on
declare @s varchar(8000)
set @s='select case '+@GuestNa+' when ''小计'' then ''小计'' else '+@GuestNum+' end as ['+@GuestNum +'] 
,case '+@GuestNa+' when ''小计'' then '''' else '+@GuestNa+' end as ['+@GuestNa+'] 
,sun_GuestMoney 
from( 
SELECT CASE WHEN (GROUPING('+@GuestNum+') = 1) THEN ''总计'' 
            ELSE ISNULL('+@GuestNum+', ''UNKNOWN'') 
      END AS ['+@GuestNum+'], 
      CASE WHEN (GROUPING('+@GuestNum+') = 1) THEN '''' 
            when(grouping('+@GuestNa+')=1)then ''小计'' 
            ELSE ISNULL('+@GuestNa+', ''UNKNOWN'') 
      END AS ['+@GuestNa+'], 
      SUM('+@GuestMon+') as [sun_GuestMoney] 
FROM '+@GuestTable +'
GROUP BY '+@GuestNum+','+@GuestNa+' WITH ROLLUP ) bb' 
exec(@s) 
end 

Answer 16

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON 
go 
ALTER PROCEDURE [dbo].[GusetInfo]  
@GuestTable  nvarchar(100),              -- 表名 
@GuestNum nvarchar(50) = '*',      -- 台号 
@GuestNa nvarchar(100)='',              -- 台名 
@GuestMon  int = 0                --  台面钱数 
AS 
BEGIN 
set nocount on
declare @s varchar(8000)
set @s='select case '+@GuestNa+' when ''小计'' then ''小计'' else '+@GuestNum+' end as ['+@GuestNum +'] 
,case '+@GuestNa+' when ''小计'' then '''' else '+@GuestNa+' end as ['+@GuestNa+'] 
,sun_GuestMoney 
from( 
SELECT CASE WHEN (GROUPING('+@GuestNum+') = 1) THEN ''总计'' 
            ELSE ISNULL('+@GuestNum+', ''UNKNOWN'') 
      END AS ['+@GuestNum+'], 
      CASE WHEN (GROUPING('+@GuestNum+') = 1) THEN '''' 
            when(grouping('+@GuestNa+')=1)then ''小计'' 
            ELSE ISNULL('+@GuestNa+', ''UNKNOWN'') 
      END AS ['+@GuestNa+'], 
      SUM('+@GuestMon+') as [sun_GuestMoney] 
FROM '+@GuestTable +'
GROUP BY '+@GuestNum+','+@GuestNa+' WITH ROLLUP ) bb' 
exec(@s) 
end 消息 208，级别 16，状态 6，过程 GusetInfo，第 24 行
对象名  'dbo.GusetInfo' 无效。
郁闷~~~~~~~~~~~~