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
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
存为 VIEW1SELECT * 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 这样的交叉连接在这里能用上吗？
详细说明
比如你有两个表
学生（SNO，SNAME）
成绩（SNO，CNO，SCORE）这个时候你要显示所有学生的详细信息和考试结果，就需要用JOIN查询了。