-- =============================================
-- Create table YW_TRANSRECORDTJ
-- =============================================
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'YW_TRANSRECORDTJ'
AND type = 'U')
DROP TABLE YW_TRANSRECORDTJ
GOCREATE TABLE [dbo].[YW_TRANSRECORDTJ] (
[TRANDATE] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
[TRANNAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[TranStatistic] [int] NULL ,
[Percentage] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[SumM] [int] NULL
) ON [PRIMARY]
GO-- =============================================
--
-- =============================================
DECLARE @SUM INT
SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM)SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTH
TRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
@SUM AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE<=GETDATE()
GROUP BY TRANNAME, DATEPART(MONTH, TRANDATE)
ORDER BY TRANDATE,TRANNAME
GO运行上面这段代码,出现如下错误信息:
服务器: 消息 107,级别 16,状态 3,行 2
列前缀 'YW_TRANSRECORDTJ' 与查询中所用的表名或别名不匹配。
服务器: 消息 8127,级别 16,状态 1,行 4
列名 'YW_TRANSRECORDTJ.TRANDATE' 在 ORDER BY 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。哪位大虾能否帮我看一下,谢谢!
-- Create table YW_TRANSRECORDTJ
-- =============================================
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N'YW_TRANSRECORDTJ'
AND type = 'U')
DROP TABLE YW_TRANSRECORDTJ
GOCREATE TABLE [dbo].[YW_TRANSRECORDTJ] (
[TRANDATE] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
[TRANNAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[TranStatistic] [int] NULL ,
[Percentage] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[SumM] [int] NULL
) ON [PRIMARY]
GO-- =============================================
--
-- =============================================
DECLARE @SUM INT
SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM)SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTH
TRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
@SUM AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE<=GETDATE()
GROUP BY TRANNAME, DATEPART(MONTH, TRANDATE)
ORDER BY TRANDATE,TRANNAME
GO运行上面这段代码,出现如下错误信息:
服务器: 消息 107,级别 16,状态 3,行 2
列前缀 'YW_TRANSRECORDTJ' 与查询中所用的表名或别名不匹配。
服务器: 消息 8127,级别 16,状态 1,行 4
列名 'YW_TRANSRECORDTJ.TRANDATE' 在 ORDER BY 子句中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。哪位大虾能否帮我看一下,谢谢!
SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM) SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTHTRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
@SUM AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE <=GETDATE()
GROUP BY TRANNAME, DATEPART(MONTH, TRANDATE)
ORDER BY TRANDATE,TRANNAME
GO
FROM sysobjects
WHERE name = N'YW_TRANSRECORDTJ'
AND type = 'U')
DROP TABLE YW_TRANSRECORDTJ
GO CREATE TABLE [dbo].[YW_TRANSRECORDTJ] (
[TRANDATE] [varchar] (14) COLLATE Chinese_PRC_CI_AS NULL ,
[TRANNAME] [varchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[TranStatistic] [int] NULL ,
[Percentage] [varchar] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[SumM] [int] NULL
) ON [PRIMARY]
GO DECLARE @SUM INT
SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM) SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTH,
TRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
@SUM AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE <=GETDATE()
GROUP BY TRANNAME, DATEPART(MONTH, TRANDATE)
ORDER BY DATEPART(MONTH, TRANDATE),TRANNAME
GO
SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM) SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTH
TRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
@SUM AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE <=GETDATE()
GROUP BY DATEPART(MONTH, TRANDATE),TRANNAME
ORDER BY TRANDATE,TRANNAME
列前缀 'YW_TRANSRECORDTJ' 与查询中所用的表名或别名不匹配。
--SELECT @SUM = SUM(YW_TRANSRECORDTJ.SumM)
SELECT
DATEPART(MONTH,TRANDATE) AS TRANDATE_MONTH,
TRANNAME,
SUM(TranStatistic) AS TOTALTRAN,
--convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/@SUM ))+'%' as '百分比' ,
convert(varchar(10), convert(numeric(5,2), COUNT(*)*100.00/SUM(YW_TRANSRECORDTJ.SumM)))+'%' as '百分比' ,
--@SUM AS '总数'
SUM(YW_TRANSRECORDTJ.SumM) AS '总数'
FROM YW_TRANSRECORDTJ
WHERE TRANDATE>= DATEADD(MONTH, -1, GETDATE()) and TRANDATE <=GETDATE()
GROUP BY DATEPART(MONTH, TRANDATE),TRANNAME
ORDER BY DATEPART(MONTH, TRANDATE),TRANNAME