交叉连接之疑问，在线求解答ing

[kris_in_java]

Mysql数据库表为product，其创建sql为：

CREATE TABLE `product` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) collate utf8_unicode_ci default NULL,
  `country` varchar(20) collate utf8_unicode_ci default NULL,
  `amount` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records 
-- ----------------------------
INSERT INTO `product` VALUES ('1', '手套', 'American', '2');
INSERT INTO `product` VALUES ('2', '手套', 'Japan', '5');
INSERT INTO `product` VALUES ('3', '帽子', 'China', '7');
INSERT INTO `product` VALUES ('4', '电视机', 'Korea', '4');
INSERT INTO `product` VALUES ('5', '苹果', 'China', '20');
INSERT INTO `product` VALUES ('6', '电视机', 'Germany', '11');
INSERT INTO `product` VALUES ('7', '苹果', 'Greece', '10');
INSERT INTO `product` VALUES ('8', '帽子', 'American', '8');

现想输出以下报表：



我的解决sql是：

select name,sum(case country when 'American' then amount else null end) as American,sum(case country when 'China' then amount else null end) as China,
sum(case country when 'Japan' then amount else null end) as Japan,sum(case country when 'Korea' then amount else null end) as Korea,
sum(case country when 'Germany' then amount else null end) as Germany,sum(case country when 'Greece' then amount else null end) as Greece,
sum(amount) as total from product group by name
union
select 'total',sum(r.American),sum(r.China),sum(r.Japan),sum(r.Korea),sum(r.Germany),sum(r.Greece),sum(r.total) from (select name,sum(case country when 'American' then amount else null end) as American,sum(case country when 'China' then amount else null end) as China,
sum(case country when 'Japan' then amount else null end) as Japan,sum(case country when 'Korea' then amount else null end) as Korea,
sum(case country when 'Germany' then amount else null end) as Germany,sum(case country when 'Greece' then amount else null end) as Greece,
sum(amount) as total from product group by name) as r

太麻烦，请问交叉连接select * from table1，table2 这样的交叉连接在这里能用上吗？有什么别的办法么？

求明白人指点！在线等ing

[ACMAIN_CHM]

[Quote]那select * from table1，table2 这样的交叉连接到底有什么应用场景？[/Quote]

比如你有两个表
学生（SNO，SNAME）
成绩（SNO，CNO，SCORE）

这个时候你要显示所有学生的详细信息和考试结果，就需要用JOIN查询了。

[ACMAIN_CHM]

《数据库系统概论（第四版）》 王珊 萨师煊 高等教育出版社 (掌握基础知识和概念)

然后再粗略浏览一遍MYSQL的官方手册。（方便以后查找，避免类似于考试的时候，给你本政治书也不知道答案在第几章，第几页）MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

[wwwwb]

你可以将
select name,sum(case country when 'American' then amount else null end) as American,sum(case country when 'China' then amount else null end) as China,
sum(case country when 'Japan' then amount else null end) as Japan,sum(case country when 'Korea' then amount else null end) as Korea,
sum(case country when 'Germany' then amount else null end) as Germany,sum(case country when 'Greece' then amount else null end) as Greece,
sum(amount) as total from product group by name
存为 VIEW1

SELECT * FROM VIEW1
UNION
select 'total',sum(r.American),sum(r.China),sum(r.Japan),sum(r.Korea),sum(r.Germany),sum(r.Greece),sum(r.total) from VIEW1

请问交叉连接select * from table1，table2 这样的交叉连接在这里能用上吗？
详细说明

[kris_in_java]

谢谢

那select * from table1，table2 这样的交叉连接到底有什么应用场景？

上面那个例子还有没有其他的sql方式？