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 ROLLUP
Item 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
SELECT stu_no,stu_name, score
FROM tbname
where stu_no < 100003
UNION
SELECT stu_no='null' , stu_name ='合计', SUM(score) AS scroe
FROM scroes
where stu_no < 100003
在oracle里提了一下没有满意的答案。
http://community.csdn.net/Expert/topic/3626/3626169.xml?temp=.2323419
如果 from 和where的内容比较多
这样会不会效率比较低?
select stu_no,stu_name,score from tbname where stu_no<100003
union all
select '','合计',sum(score),from tbname where stu_no<100003;
实际上第一个select已经把第二次要用的结果选出来了呀,如果不用临时表,怎样利用这个结果?
我是举的一个简化的例子,实际上要关联的表较多,数据量较大,有没有好一点的办法?