USE pubs
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO--查询结果Category Shortened Title priceBusiness Cooking with Computers: S 11.9500 Business
Business The Busy Executive's Data 19.9900 Business
Business Straight Talk About Compu 19.9900 AVG
13.7300我要怎么写才能让AVG和price对齐?如下面的格式.
Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99 avg
==========================
13.73
GO
SELECT Category =
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO--查询结果Category Shortened Title priceBusiness Cooking with Computers: S 11.9500 Business
Business The Busy Executive's Data 19.9900 Business
Business Straight Talk About Compu 19.9900 AVG
13.7300我要怎么写才能让AVG和price对齐?如下面的格式.
Category Shortened Title Price
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99 avg
==========================
13.73
------------------- ------------------------- --------------------------
Business You Can Combat Computer S 2.99
Business Cooking with Computers: S 11.95
Business The Busy Executive's Data 19.99
Business Straight Talk About Compu 19.99 avg
==========================
13.73
GO
SELECT Category,[Shortened Title],price
FROM
(
SELECT Category =
CASE type
WHEN 'popular_comp ' THEN 'Popular Computing '
WHEN 'mod_cook ' THEN 'Modern Cooking '
WHEN 'business ' THEN 'Business '
WHEN 'psychology ' THEN 'Psychology '
WHEN 'trad_cook ' THEN 'Traditional Cooking '
ELSE 'Not yet categorized '
END,
CAST(title AS varchar(25)) AS 'Shortened Title ',
price=CAST(Price AS VARCHAR(20)),
c1 = 0,
c2 = type,
c3 = 0
FROM titles
WHERE price IS NOT NULL
UNION ALL
SELECT '','',[price]=CAST(AVG(Price) AS VARCHAR(20)),c1 = 0,c2 = type, c3 = 3
FROM titles
WHERE price IS NOT NULL
GROUP BY type
UNION ALL
SELECT '','','avg',c1 = 0,c2 = type, c3 = 1
FROM titles
WHERE price IS NOT NULL
GROUP BY type
UNION ALL
SELECT '','','==================',c1 = 0,c2 = type, c3 = 2
FROM titles
WHERE price IS NOT NULL
GROUP BY type
) A
ORDER BY c1,c2,c3
GO
Category Shortened Title price
-------------------- ------------------------- --------------------
Business The Busy Executive's Data 19.99
Business Cooking with Computers: S 11.95
Business You Can Combat Computer S 2.99
Business Straight Talk About Compu 19.99
avg
==================
13.73
Modern Cooking Silicon Valley Gastronomi 19.99
Modern Cooking The Gourmet Microwave 2.99
avg
==================
11.49
Popular Computing But Is It User Friendly? 22.95
Popular Computing Secrets of Silicon Valley 20.00
avg
==================
21.48
Psychology Computer Phobic AND Non-P 21.59
Psychology Is Anger the Enemy? 10.95
Psychology Life Without Fear 7.00
Psychology Prolonged Data Deprivatio 19.99
Psychology Emotional Security: A New 7.99
avg
==================
13.50
Traditional Cooking Onions, Leeks, and Garlic 20.95
Traditional Cooking Fifty Years in Buckingham 11.95
Traditional Cooking Sushi, Anyone? 14.99
avg
==================
15.96（所影响的行数为 31 行）