请大家帮忙,关于生成月报表查询的语句,急! select typeName as '类别名称' ,left(tDate,6) as '年月' ,sum(tNum) as '数量'from ProNamegroup by typeName,left(tDate,6) 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 SELECT typeName,CONVERT(CHAR(7),tDate,120),SUM(tNum)FROM ProName AGROUP BY typeName,CONVERT(CHAR(7),tDate,120) --生成测试数据CREATE TABLE #ProName(tId INT,typeName VARCHAR(20),tDate DATETIME,tNum INT)INSERT INTO #ProName SELECT 1,'电脑教材','2005-03-02',12INSERT INTO #ProName SELECT 2,'电脑教材','2005-03-03',44INSERT INTO #ProName SELECT 3,'电脑教材','2005-03-30',44INSERT INTO #ProName SELECT 4,'考研教材','2005-05-02',12INSERT INTO #ProName SELECT 5,'考研教材','2005-05-03',44INSERT INTO #ProName SELECT 6,'考研教材','2005-05-30',44INSERT INTO #ProName SELECT 7,'高校教材','2005-04-02',330INSERT INTO #ProName SELECT 8,'高校教材','2005-04-03',440INSERT INTO #ProName SELECT 9,'高校教材','2005-04-30',664--执行查询SELECT 类别 = typeName, 月份 = CONVERT(CHAR(7),tDate,120), 数量 = SUM(tNum)FROM #ProNameGROUP BY typeName,CONVERT(CHAR(7),tDate,120)--输出结果/*类别 月份 数量-------- ------- -------电脑教材 2005-03 100高校教材 2005-04 1434考研教材 2005-05 100*/ declare @ProName table( tId INT,typeName VARCHAR(20),tDate varchar(10),tNum INT)INSERT INTO @ProName SELECT 1,'电脑教材','2005-03-02',12INSERT INTO @ProName SELECT 2,'电脑教材','2005-03-03',44INSERT INTO @ProName SELECT 3,'电脑教材','2005-03-30',44INSERT INTO @ProName SELECT 4,'考研教材','2005-05-02',12INSERT INTO @ProName SELECT 5,'考研教材','2005-05-03',44INSERT INTO @ProName SELECT 6,'考研教材','2005-05-30',44INSERT INTO @ProName SELECT 7,'高校教材','2005-04-02',330INSERT INTO @ProName SELECT 8,'高校教材','2005-04-03',440INSERT INTO @ProName SELECT 9,'高校教材','2005-04-30',664--查询select typeName as '类别名称' ,reverse(stuff(reverse(tDate),1,charindex('-',reverse(tDate)),'')) as '年月' ,sum(tNum) as '数量'from @ProNamegroup by typeName, reverse(stuff(reverse(tDate),1,charindex('-',reverse(tDate)),''))--结果/*类别名称 年月 数量 -------------------- ------------------------------------电脑教材 2005-03 100.00高校教材 2005-04 1,434.00考研教材 2005-05 100.00(所影响的行数为 3 行)*/ select tId, typeName, tDate=max(tdate), tNum=sum(tnum)from ProNamegroup by typename SQL截取字符串 请帮我看看错在哪里?谢谢 数据库 合并两个表 非常感谢 一对多关系问题 关于统计数据的问题,急救!! Sql server2008 使用链接服务器执行insert,update SQL2000查询问题 SQL語句, 有答案也想不通的问题,请高手指点 100分求此存储过程的解法?乐于助人的和有实力的来拿!小弟先行谢过啦!不够分再加! 为什么restore SQL SERVER数据库时出错?
typeName,CONVERT(CHAR(7),tDate,120),SUM(tNum)
FROM
ProName A
GROUP BY
typeName,CONVERT(CHAR(7),tDate,120)
CREATE TABLE #ProName(tId INT,typeName VARCHAR(20),tDate DATETIME,tNum INT)
INSERT INTO #ProName SELECT 1,'电脑教材','2005-03-02',12
INSERT INTO #ProName SELECT 2,'电脑教材','2005-03-03',44
INSERT INTO #ProName SELECT 3,'电脑教材','2005-03-30',44
INSERT INTO #ProName SELECT 4,'考研教材','2005-05-02',12
INSERT INTO #ProName SELECT 5,'考研教材','2005-05-03',44
INSERT INTO #ProName SELECT 6,'考研教材','2005-05-30',44
INSERT INTO #ProName SELECT 7,'高校教材','2005-04-02',330
INSERT INTO #ProName SELECT 8,'高校教材','2005-04-03',440
INSERT INTO #ProName SELECT 9,'高校教材','2005-04-30',664--执行查询
SELECT
类别 = typeName,
月份 = CONVERT(CHAR(7),tDate,120),
数量 = SUM(tNum)
FROM
#ProName
GROUP BY
typeName,CONVERT(CHAR(7),tDate,120)--输出结果
/*
类别 月份 数量
-------- ------- -------
电脑教材 2005-03 100
高校教材 2005-04 1434
考研教材 2005-05 100
*/
(
tId INT,typeName VARCHAR(20),tDate varchar(10),tNum INT
)
INSERT INTO @ProName SELECT 1,'电脑教材','2005-03-02',12
INSERT INTO @ProName SELECT 2,'电脑教材','2005-03-03',44
INSERT INTO @ProName SELECT 3,'电脑教材','2005-03-30',44
INSERT INTO @ProName SELECT 4,'考研教材','2005-05-02',12
INSERT INTO @ProName SELECT 5,'考研教材','2005-05-03',44
INSERT INTO @ProName SELECT 6,'考研教材','2005-05-30',44
INSERT INTO @ProName SELECT 7,'高校教材','2005-04-02',330
INSERT INTO @ProName SELECT 8,'高校教材','2005-04-03',440
INSERT INTO @ProName SELECT 9,'高校教材','2005-04-30',664
--查询
select typeName as '类别名称'
,reverse(stuff(reverse(tDate),1,charindex('-',reverse(tDate)),'')) as '年月'
,sum(tNum) as '数量'
from @ProName
group by typeName,
reverse(stuff(reverse(tDate),1,charindex('-',reverse(tDate)),''))--结果
/*
类别名称 年月 数量
-------------------- ------------------------------------
电脑教材 2005-03 100.00
高校教材 2005-04 1,434.00
考研教材 2005-05 100.00(所影响的行数为 3 行)
*/
typeName,
tDate=max(tdate),
tNum=sum(tnum)
from ProName
group by typename