不必客气!:) 看你的SQL语句,我感觉,你好象要求COUNT(DefectSamples.Class)的汇总,我不知道这样能不能达到你的要求? SELECT 序号=identity(int,1,1), DefectClasses.ClassId, DefectClasses.ChineseName, DefectSamples.Class, COUNT(DefectSamples.Class) AS MyCount into #temp1 FROM DefectSamples INNER JOIN DefectClasses ON DefectSamples.Class = DefectClasses.ClassId WHERE (DefectSamples.Verified = 1) GROUP BY DefectSamples.Class, DefectClasses.ClassId, DefectClasses.ChineseName with rollup ORDER BY MyCount DESC我没有你的环境,因此不知道能不能行的通:) 或者举个例子,给出数据,及你想得到的结果?
看这个例子: 简单表 Inventory 中包含:Item Color Quantity -------------------- -------------------- -------------------------- Table Blue 124 Table Red 223 Chair Blue 101 Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL' ELSE ISNULL(Item, 'UNKNOWN') END AS Item, CASE WHEN (GROUPING(Color) = 1) THEN 'ALL' ELSE ISNULL(Color, 'UNKNOWN') END AS Color, SUM(Quantity) AS QtySum FROM Inventory GROUP BY Item, Color WITH ROLLUPItem Color QtySum -------------------- -------------------- -------------------------- Chair Blue 101.00 Chair Red 210.00 Chair ALL 311.00 Table Blue 124.00 Table Red 223.00 Table ALL 347.00 ALL ALL 658.00 (7 row(s) affected)
自己解决了:SELECT DefectClasses.ClassId, DefectClasses.ChineseName, DefectSamples.Class, COUNT(DefectSamples.Class) AS MyCount into #temp1 FROM DefectSamples INNER JOIN DefectClasses ON DefectSamples.Class = DefectClasses.ClassId WHERE (DefectSamples.Verified = 1) GROUP BY DefectSamples.Class, DefectClasses.ClassId, DefectClasses.ChineseNameSELECT 序号=identity(int,1,1), ClassId, ChineseName, Class, MyCount into #temp2 FROM #temp1 ORDER BY MyCount DESC DROP TABLE #temp1select cast(序号 as varchar) as 序号, ClassId, ChineseName, Class, MyCount from #temp2 union all SELECT '合计' as 序号, null as ClassId, null as ChineseName, null as Class, COUNT(DefectSamples.Class) AS MyCount FROM DefectSamples INNER JOIN DefectClasses ON DefectSamples.Class = DefectClasses.ClassId WHERE (DefectSamples.Verified = 1)DROP TABLE #temp2用了2个临时表,有没有更好的办法?
create table temp(chinesename varchar(5),mycount int) insert temp values('name5',50) insert temp values('name3',30) insert temp values('name1',10) insert temp values('name4',40) insert temp values('name2',20)select id=identity(int,1,1),* into #temp1 from temp order by mycount desc select id=cast(id as varchar),chinesename,mycount from #temp1 union all select '合计','',sum(mycount) from #temp1 ---------------------------------------------------------------- id chinesename mycount ------------------------------ ----------- ----------- 1 name5 50 2 name4 40 3 name3 30 4 name2 20 5 name1 10 合计 150(所影响的行数为 6 行)--------------------------------------------------------------- 如果数据量少,这个办法应该可以,如果数据量多,那么你可以在查询前在ID字段上创建一个聚集索引就可以了;create clustered index ind_clu on #temp1(id)
看你的SQL语句,我感觉,你好象要求COUNT(DefectSamples.Class)的汇总,我不知道这样能不能达到你的要求?
SELECT 序号=identity(int,1,1), DefectClasses.ClassId, DefectClasses.ChineseName, DefectSamples.Class,
COUNT(DefectSamples.Class) AS MyCount
into #temp1
FROM DefectSamples INNER JOIN
DefectClasses ON DefectSamples.Class = DefectClasses.ClassId
WHERE (DefectSamples.Verified = 1)
GROUP BY DefectSamples.Class, DefectClasses.ClassId, DefectClasses.ChineseName with rollup
ORDER BY MyCount DESC我没有你的环境,因此不知道能不能行的通:)
或者举个例子,给出数据,及你想得到的结果?
按照你的SQL得到了很奇怪的结果,就是每行数据重复了3遍总共得到了3备于原来的数据量,后面两遍中ChineseName为空,MyCount数值正确。排序也比较乱。例子还真不好举,因为是从数据库中读出来的。我用这个SQL做报表用。请问with rollup是什么意思?还能怎么改改呢?
简单表 Inventory 中包含:Item Color Quantity
-------------------- -------------------- --------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red 210 下列查询将生成小计报表:SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
ELSE ISNULL(Item, 'UNKNOWN')
END AS Item,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'UNKNOWN')
END AS Color,
SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUPItem Color QtySum
-------------------- -------------------- --------------------------
Chair Blue 101.00
Chair Red 210.00
Chair ALL 311.00
Table Blue 124.00
Table Red 223.00
Table ALL 347.00
ALL ALL 658.00 (7 row(s) affected)
因为我要实现的报表是这样的:
前面每一行显示一个ChineseName和相应的MyCount
最后一行是对上面所有行数据的MyCount数值总和行如:序号 | 名称(ChineseName) | 数量(MyCount)
----------------------------------------------
1 name1 50
2 name2 40
3 name3 30
4 name4 20
5 name5 10
合计 150要求是:按照数量(MyCount)降序排列,“序号”字段是手工添加的,也要递增显示,现在的问题是加上了“ ORDER BY MyCount DESC”之后,数量(MyCount)是降序排列了,但是“序号”字段的数据也乱了,不再是按照顺序递增了。
COUNT(DefectSamples.Class) AS MyCount
into #temp1
FROM DefectSamples INNER JOIN
DefectClasses ON DefectSamples.Class = DefectClasses.ClassId
WHERE (DefectSamples.Verified = 1)
GROUP BY DefectSamples.Class, DefectClasses.ClassId, DefectClasses.ChineseNameSELECT 序号=identity(int,1,1), ClassId, ChineseName, Class, MyCount
into #temp2
FROM #temp1 ORDER BY MyCount DESC
DROP TABLE #temp1select cast(序号 as varchar) as 序号, ClassId, ChineseName, Class, MyCount from #temp2
union all
SELECT '合计' as 序号, null as ClassId, null as ChineseName, null as Class,
COUNT(DefectSamples.Class) AS MyCount
FROM DefectSamples INNER JOIN
DefectClasses ON DefectSamples.Class = DefectClasses.ClassId
WHERE (DefectSamples.Verified = 1)DROP TABLE #temp2用了2个临时表,有没有更好的办法?
insert temp values('name5',50)
insert temp values('name3',30)
insert temp values('name1',10)
insert temp values('name4',40)
insert temp values('name2',20)select id=identity(int,1,1),* into #temp1 from temp order by mycount desc
select id=cast(id as varchar),chinesename,mycount from #temp1
union all
select '合计','',sum(mycount) from #temp1
----------------------------------------------------------------
id chinesename mycount
------------------------------ ----------- -----------
1 name5 50
2 name4 40
3 name3 30
4 name2 20
5 name1 10
合计 150(所影响的行数为 6 行)---------------------------------------------------------------
如果数据量少,这个办法应该可以,如果数据量多,那么你可以在查询前在ID字段上创建一个聚集索引就可以了;create clustered index ind_clu on #temp1(id)