例子献给大家
CREATE TABLE `order` (
`Id_O` int(11) DEFAULT NULL,
`OrderNo` int(11) DEFAULT NULL,
`Id_P` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `order`(Id_O,OrderNo,Id_P) values (1,77895,3);
insert into `order`(Id_O,OrderNo,Id_P) values (2,44678,3);
insert into `order`(Id_O,OrderNo,Id_P) values (3,22456,1);
insert into `order`(Id_O,OrderNo,Id_P) values (4,24562,1);
insert into `order`(Id_O,OrderNo,Id_P) values (5,34764,65);CREATE TABLE `person` (
`Id_P` int(11) DEFAULT NULL,
`LastName` varchar(50) DEFAULT NULL,
`FirstName` varchar(50) DEFAULT NULL,
`Address` varchar(50) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (2,'Bush','George','Fifth Avenue','New York',8);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (3,'Carter','Thomas','Changan Street','Beijing',2);
我执行这个语句
SELECT p.Id_P,
p.LastName,
p.FirstName,
p.Address,
p.City,
p.col,
sum(p.col)
FROM person AS p, `order` AS o
GROUP BY p.Id_P然后sum(p.col)的结果是15 40 10
Id_P LastName FirstName Address City col sum(p.col)
1 Adams John Oxford Street London 3 15
2 Bush George Fifth Avenue New York 8 40
3 Carter Thomas Changan Street Beijing 2 10我的问题是:
怎样在关联一个新表的情况下做查询可以使得sum(p.col)依然是 3 8 2? 也就是跟person表中的sum(col)一样。不知道描述的清不?谢谢大家
CREATE TABLE `order` (
`Id_O` int(11) DEFAULT NULL,
`OrderNo` int(11) DEFAULT NULL,
`Id_P` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `order`(Id_O,OrderNo,Id_P) values (1,77895,3);
insert into `order`(Id_O,OrderNo,Id_P) values (2,44678,3);
insert into `order`(Id_O,OrderNo,Id_P) values (3,22456,1);
insert into `order`(Id_O,OrderNo,Id_P) values (4,24562,1);
insert into `order`(Id_O,OrderNo,Id_P) values (5,34764,65);CREATE TABLE `person` (
`Id_P` int(11) DEFAULT NULL,
`LastName` varchar(50) DEFAULT NULL,
`FirstName` varchar(50) DEFAULT NULL,
`Address` varchar(50) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (2,'Bush','George','Fifth Avenue','New York',8);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (3,'Carter','Thomas','Changan Street','Beijing',2);
我执行这个语句
SELECT p.Id_P,
p.LastName,
p.FirstName,
p.Address,
p.City,
p.col,
sum(p.col)
FROM person AS p, `order` AS o
GROUP BY p.Id_P然后sum(p.col)的结果是15 40 10
Id_P LastName FirstName Address City col sum(p.col)
1 Adams John Oxford Street London 3 15
2 Bush George Fifth Avenue New York 8 40
3 Carter Thomas Changan Street Beijing 2 10我的问题是:
怎样在关联一个新表的情况下做查询可以使得sum(p.col)依然是 3 8 2? 也就是跟person表中的sum(col)一样。不知道描述的清不?谢谢大家
是15、40、10连接什么表?
p.LastName,
p.FirstName,
p.Address,
p.City,
p.col,
p.col as sum(p.col)
FROM person AS p, `order` AS o
GROUP BY p.Id_P
多谢
求和是必要的 ~
其实目的就是求和。只不过现在求和结果不对。我要的结果是 无论关联了多少表 p表里的sum 始终不变举个例子 工资,不能因为关联了其他表 我们部门工资总数就变了。这下不知道描述清楚点没
你这里根本就没有表关联啊!就算你想本部门的工资不变,sum 就是总工资,那你就不要group by p.ID_P
确实不太清楚 看这个CREATE TABLE `person` (
`Id_P` int(11) DEFAULT NULL,
`LastName` varchar(50) DEFAULT NULL,
`FirstName` varchar(50) DEFAULT NULL,
`Address` varchar(50) DEFAULT NULL,
`City` varchar(50) DEFAULT NULL,
`col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (2,'Bush','George','Fifth Avenue','New York',8);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (3,'Carter','Thomas','Changan Street','Beijing',2);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);
insert into person(Id_P,LastName,FirstName,Address,City,col) values (1,'Adams','John','Oxford Street','London',3);SELECT p.Id_P,
p.LastName,
p.FirstName,
p.Address,
p.City
sum(p.col)
FROM person AS p, `order` AS o
GROUP BY p.Id_P现在我希望sum(p.col)的值是Id_P LastName FirstName Address City sum(p.col)
1 Adams John Oxford Street London 12
2 Bush George Fifth Avenue New York 8
3 Carter Thomas Changan Street Beijing 2关联的order只是一个例子,将来我会关联类似的表,但是sum(p.col)应该保持 id_p1 是3+3+3+3。idp2是