2个查询结果
第一个查询结果得到
SELECT id,name,in_qty FROM xxx
+----+------+--------+
| id | name | in_qty |
+----+------+--------+
| 11 | cccc | 545465 |
+----+------+--------+
| 23 | bbbb | 545780 |
+----+------+--------+第二个查询结果除了最后一个字段和上面不一样,其余都一样
SELECT id,name,out_qty FROM xxx
+----+------+---------+
| id | name | out_qty |
+----+------+---------+
| 23 | bbbb | 5457805 |
+----+------+---------+
| 98 | aaaa | 5454656 |
+----+------+---------+想要得到下面的结果+----+------+---------+--------+
| id | name | out_qty | in_qty |
+----+------+---------+--------+
| 11 | cccc | ______0 | 545465 |
+----+------+---------+--------+
| 23 | bbbb | 5457805 | 545780 |
+----+------+---------+--------+
| 98 | aaaa | 5454656 | _____0 |
+----+------+---------+--------+
第一个查询结果得到
SELECT id,name,in_qty FROM xxx
+----+------+--------+
| id | name | in_qty |
+----+------+--------+
| 11 | cccc | 545465 |
+----+------+--------+
| 23 | bbbb | 545780 |
+----+------+--------+第二个查询结果除了最后一个字段和上面不一样,其余都一样
SELECT id,name,out_qty FROM xxx
+----+------+---------+
| id | name | out_qty |
+----+------+---------+
| 23 | bbbb | 5457805 |
+----+------+---------+
| 98 | aaaa | 5454656 |
+----+------+---------+想要得到下面的结果+----+------+---------+--------+
| id | name | out_qty | in_qty |
+----+------+---------+--------+
| 11 | cccc | ______0 | 545465 |
+----+------+---------+--------+
| 23 | bbbb | 5457805 | 545780 |
+----+------+---------+--------+
| 98 | aaaa | 5454656 | _____0 |
+----+------+---------+--------+
union
select b.*,COALESCE(a.in_qty,0) from 第一个查询 a right join 第二个查询 b on a.name=b.name
(不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
(
2楼;
)
CREATE TABLE `pp` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`pin` tinyint(1) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;DROP TABLE IF EXISTS `goods_in`;
CREATE TABLE `goods_in` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `goods_out`;
CREATE TABLE `goods_out` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO `pp` (`id`,`pin`) VALUES
(1,1),
(2,1),
(3,1);INSERT INTO `goods_in` (`id`,`date`) VALUES
(1,'2000-01-01'),
(2,'2010-05-02'),
(3,'2010-05-06'),
(4,'2010-05-15');
INSERT INTO `goods_out` (`id`,`date`) VALUES
(1,'2000-01-01'),
(2,'2010-04-30'),
(3,'2010-05-09'),
(4,'2010-05-20');DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`pid` int(4) NOT NULL,
`name` varchar(20) NOT NULL,
`in_id` int(4) NOT NULL DEFAULT '1',
`out_id` int(4) NOT NULL DEFAULT '1',
`qty` int(6) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `pid` (`pid`),
KEY `in_id` (`in_id`),
KEY `out_id` (`out_id`),
CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`pid`) REFERENCES `pp` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `goods_ibfk_2` FOREIGN KEY (`in_id`) REFERENCES `goods_in` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `goods_ibfk_3` FOREIGN KEY (`out_id`) REFERENCES `goods_out` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO `goods` VALUES
(1,1,'AAAA',1,2,12),
(2,1,'AAAA',3,2,12),
(3,1,'AAAA',3,2,12),
(4,1,'AAAA',3,1,12),
(5,1,'AAAA',3,1,6),
(6,1,'AAAA',4,2,12),
(7,1,'AAAA',4,3,12),
(8,2,'AAAA',4,3,8),
(9,2,'AAAA',4,1,12),
(10,2,'AAAA',1,4,12),
(11,2,'AAAA',1,4,12),
(12,2,'AAAA',3,4,10),
(13,2,'AAAA',2,4,12),
(14,2,'AAAA',1,4,12),
(15,2,'AAAA',4,1,12),
(16,3,'BBBB',1,1,12);select pp.id,pp.pin,sum(gs.qty) as in_qty
from pp, goods as gs, goods_in as gi
where pp.id = gs.pid and gs.in_id = gi.id
and gi.date between '2010-05-01' and '2010-06-01'
and gs.in_id <> 1
group by pp.id;
select pp.id,pp.pin,sum(gs.qty) as out_qty
from pp, goods as gs, goods_out as go
where pp.id = gs.pid and gs.out_id = go.id
and go.date between '2010-05-01' and '2010-06-01'
and gs.in_id <> 1
group by pp.id;第一个查询结果是
+----+-----+--------+
| id | pin | in_qty |
+----+-----+--------+
| _1 | __1 | ____66 |
+----+-----+--------+
| _2 | __1 | ____54 |
+----+-----+--------+
| _3 | __1 | ____12 |
+----+-----+--------+第二个查询结果
+----+-----+---------+
| id | pin | out_qty |
+----+-----+---------+
| _1 | __1 | _____12 |
+----+-----+---------+
| _2 | __1 | _____30 |
+----+-----+---------+期望结果
+----+-----+--------+---------+
| id | pin | in_qty | out_qty |
+----+-----+--------+---------+
| _1 | __1 | ____66 | _____12 |
+----+-----+--------+---------+
| _2 | __1 | ____54 | _____30 |
+----+-----+--------+---------+
| _3 | __1 | ____12 | ______0 |
+----+-----+--------+---------+
+----+-----+--------+
| id | pin | in_qty |
+----+-----+--------+
| _1 | __1 | ____66 |
+----+-----+--------+
| _2 | __1 | ____54 |
+----+-----+--------+
| _3 | __1 | ____12 |
+----+-----+--------+mysql> select pp.id,pp.pin,sum(gs.qty) as in_qty
-> from pp, goods as gs, goods_in as gi
-> where pp.id = gs.pid and gs.in_id = gi.id
-> and gi.date between '2010-05-01' and '2010-06-01'
-> and gs.in_id <> 1
-> group by pp.id;
+----+-----+--------+
| id | pin | in_qty |
+----+-----+--------+
| 1 | 1 | 66 |
| 2 | 1 | 54 |
+----+-----+--------+
2 rows in set (0.00 sec)可惜显然不是
不过第二个查询那里不能用 b.* 来查询,这样会有重复数据
而用4楼说的在最外层添加 SELECT DISTINCT * FROM (...)来查询
则提示
Every derived table must have its own alias
我是在第二个查询直接把字段写出来
b.id,b.name,COALESCE(a.in_qty,0),b.out_qty
这样就会自动去掉重复数据
本来还担心说效率会很低,但执行了下,发现还可以...
感谢二楼ing...