coalesce 如果有输出,没有就写后面的字符串
select * from material order by name,color
select coalesce(name, '狗狗总数'),coalesce(color,'同名'),sum(num) from material
group by name,color with rollup
数据的插入
CREATE TABLE material(
pid INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(100),
color VARCHAR(50),
num INT
);
INSERT INTO material(name,color,num)
SELECT 'ball','red',12
UNION ALL
SELECT 'ball','red',24
UNION ALL
SELECT 'cat','yellow',13
UNION ALL
SELECT 'ball','yellow',13
UNION ALL
SELECT 'dog','yellow',13
UNION ALL
SELECT 'cat','black',4
UNION ALL
SELECT 'cat','white',5