表a:product_type
+----------+----------+--------+---------------------+--------+---------------------+------+
| id | protype |inputtor| inputtime | confirm| confirmtime | notes|
+----------+----------+--------+---------------------+--------+---------------------+------+
| 100 | 男装 | 1 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
| 101 | 衬衫 | 2 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
| 102 | 牛仔裤 | 1 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 200 | 女装 | 1 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 300 | 男性配饰 | 2 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 400 | 女性配饰 | 2 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
+----------+----------+--------+---------------------+--------+---------------------+------+表b:employee
+----------+----------+
| id | name |
+----------+----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----------+----------+输出结果:
+----------+----------+--------+---------------------+--------+---------------------+------+
| id | protype |inputtor| inputtime | confirm| confirmtime | notes|
+----------+----------+--------+---------------------+--------+---------------------+------+
| 100 | 男装 | 张三 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
| 101 | 衬衫 | 李四 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
| 102 | 牛仔裤 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 200 | 女装 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 300 | 男性配饰 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 400 | 女性配饰 | 李四 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
+----------+----------+--------+---------------------+--------+---------------------+------+
我知道的有两个种方法可以实现。
方法一:
SELECT * FROM
(SELECT p.id,p.protype,e.name,p.inputtime FROM product_type AS p JOIN employee AS e ON p.inputtor=e.id) AS a
JOIN
(SELECT p.id,e.name,p.confirmtime,p.notes FROM product_type AS p JOIN employee AS e ON p.confirm=e.id) AS b
ON a.id=b.id;方法二:
SELECT
id,protype,
(SELECT name FROM employee WHERE employee.id=product_type.inputtor),
inputtime,
(SELECT name FROM employee WHERE employee.id=product_type.confirm),
confirmtime,notes
FROM product_type;两种方法测试后发现效率相差不大,为什么?
有没有更好的办法?
+----------+----------+--------+---------------------+--------+---------------------+------+
| id | protype |inputtor| inputtime | confirm| confirmtime | notes|
+----------+----------+--------+---------------------+--------+---------------------+------+
| 100 | 男装 | 1 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
| 101 | 衬衫 | 2 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
| 102 | 牛仔裤 | 1 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 200 | 女装 | 1 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 300 | 男性配饰 | 2 | 2009-10-28 11:00:00 | 2 | 2009-10-28 12:00:00 | NULL |
| 400 | 女性配饰 | 2 | 2009-10-28 11:00:00 | 1 | 2009-10-28 12:00:00 | NULL |
+----------+----------+--------+---------------------+--------+---------------------+------+表b:employee
+----------+----------+
| id | name |
+----------+----------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----------+----------+输出结果:
+----------+----------+--------+---------------------+--------+---------------------+------+
| id | protype |inputtor| inputtime | confirm| confirmtime | notes|
+----------+----------+--------+---------------------+--------+---------------------+------+
| 100 | 男装 | 张三 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
| 101 | 衬衫 | 李四 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
| 102 | 牛仔裤 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 200 | 女装 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 300 | 男性配饰 | 张三 | 2009-10-28 11:00:00 | 李四 | 2009-10-28 12:00:00 | NULL |
| 400 | 女性配饰 | 李四 | 2009-10-28 11:00:00 | 张三 | 2009-10-28 12:00:00 | NULL |
+----------+----------+--------+---------------------+--------+---------------------+------+
我知道的有两个种方法可以实现。
方法一:
SELECT * FROM
(SELECT p.id,p.protype,e.name,p.inputtime FROM product_type AS p JOIN employee AS e ON p.inputtor=e.id) AS a
JOIN
(SELECT p.id,e.name,p.confirmtime,p.notes FROM product_type AS p JOIN employee AS e ON p.confirm=e.id) AS b
ON a.id=b.id;方法二:
SELECT
id,protype,
(SELECT name FROM employee WHERE employee.id=product_type.inputtor),
inputtime,
(SELECT name FROM employee WHERE employee.id=product_type.confirm),
confirmtime,notes
FROM product_type;两种方法测试后发现效率相差不大,为什么?
有没有更好的办法?
解决方案 »
- 急,高手帮帮忙看看一个sql
- 问一下order by的问题
- 我不小心把管理员账户删除了 ,怎么再建一个呀(新手)
- 这个sql如何优化date>='2009-1-1' and date<'2010-5-1' 是否需要 改写为between ('2009-1-1','2010-
- mysql 复合索引和单列索引优缺点及什么情况下使用
- mysql 存储过程里的@是什么意思?加和不加有什么区别?
- ASP无法向MYSQL提交中文数据
- 小妹现在做毕设,请问那里可以找到有关C API编程的一些原代码
- sql分组求和
- 关于死锁和lock_wait_timeout的问题
- 【安全】保护你的数据库安全
- mysql 数据库 线程问题 望高手帮忙处理
select *
from product_type a ,employee i,employee c
where a.inputtor=i.id and a.confirm=c.id
你的两个查询。
第一个产生了两个临时记录集,无索引可以再利用了。
第二个,则是每条记录会查二次 employee
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
select * from product_type a
inner join
employee b on a.inputtor=b.id
inner join
employee c on a.confirm=c.id