高分求多表查询！

原帖子：http://topic.csdn.net/u/20120302/22/144da2a4-9126-4d0e-a6c2-fba465a0d942.html

解决方案 »

免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货

   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式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)

   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。
select yc_pinzhong.yc_name,yc_pinzhong.yc_chandi,yc_pinzhong.yc_guige,yc_jiage.yc_jiage from yc_pinzhong left join yc_jiage on yc_pinzhong.yc_id=yc_jiage.yc_item_id   order by yc_jiage.add_date desc limit 1表结构
-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- 主机: localhost
-- 生成日期: 2012 年 03 月 03 日 05:38
-- 服务器版本: 5.5.16
-- PHP 版本: 5.3.8SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;--
-- 数据库: `data3`
---- ----------------------------------------------------------
-- 表的结构 `yc_jiage`
--CREATE TABLE IF NOT EXISTS `yc_jiage` (
  `yc_id` int(11) NOT NULL AUTO_INCREMENT,
  `yc_jiage` double(10,2) NOT NULL,
  `yc_item_id` int(10) NOT NULL,
  `add_date` date NOT NULL,
  PRIMARY KEY (`yc_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=169 ;--
-- 转存表中的数据 `yc_jiage`
--INSERT INTO `yc_jiage` (`yc_id`, `yc_jiage`, `yc_item_id`, `add_date`) VALUES
(1, 6.00, 35, '1998-01-01'),
(2, 6.00, 35, '1998-02-01'),
(3, 5.00, 35, '1998-03-01'),
(4, 5.00, 35, '1998-04-01'),
(5, 5.00, 35, '1998-05-01'),
(6, 4.00, 35, '1998-06-01'),
(7, 5.00, 35, '1998-07-01'),
(8, 5.00, 35, '1998-08-01'),
(9, 5.00, 35, '1998-08-01'),
(10, 5.00, 35, '1998-10-01'),
(11, 5.00, 35, '1998-11-01'),
(12, 5.00, 35, '1998-12-01'),
(13, 5.00, 35, '1999-01-01'),
(14, 5.00, 35, '1999-02-01'),
(15, 5.00, 35, '1999-03-01'),
(16, 5.00, 35, '1999-04-01'),
(17, 5.00, 35, '1999-05-01'),
(18, 5.00, 35, '1999-06-01'),
(19, 5.00, 35, '1999-07-01'),
(20, 5.00, 35, '1999-08-01'),
(21, 5.00, 35, '1999-08-01'),
(22, 5.00, 35, '1999-10-01'),
(23, 5.00, 35, '1999-11-01'),
(24, 5.50, 35, '1999-12-01'),
(25, 5.50, 35, '2000-01-01'),
(26, 5.50, 35, '2000-02-01'),
(27, 5.50, 35, '2000-03-01'),
(28, 5.50, 35, '2000-04-01'),
(29, 5.50, 35, '2000-05-01'),
(30, 5.50, 35, '2000-06-01'),
(31, 5.50, 35, '2000-07-01'),
(32, 5.50, 35, '2000-08-01'),
(33, 5.50, 35, '2000-08-01'),
(34, 2.80, 35, '2000-10-01'),
(35, 2.80, 35, '2000-11-01'),
(36, 2.80, 35, '2000-12-01'),
(37, 2.80, 35, '2001-01-01'),
(38, 2.80, 35, '2001-02-01'),
(39, 2.80, 35, '2001-03-01'),
(40, 2.80, 35, '2001-04-01'),
(41, 2.80, 35, '2001-05-01'),
(42, 2.80, 35, '2001-06-01'),
(43, 2.80, 35, '2001-07-01'),
(44, 2.60, 35, '2001-08-01'),
(45, 2.60, 35, '2001-08-01'),
(46, 2.60, 35, '2001-10-01'),
(47, 2.60, 35, '2001-11-01'),
(48, 2.60, 35, '2001-12-01'),
(49, 2.50, 35, '2002-01-01'),
(50, 2.50, 35, '2002-02-01'),
(51, 2.50, 35, '2002-03-01'),
(52, 2.50, 35, '2002-04-01'),
(53, 2.50, 35, '2002-05-01'),
(54, 2.50, 35, '2002-06-01'),
(55, 2.50, 35, '2002-07-01'),
(56, 2.50, 35, '2002-08-01'),
(57, 2.50, 35, '2002-08-01'),
(58, 2.50, 35, '2002-10-01'),
(59, 2.50, 35, '2002-11-01'),
(60, 2.50, 35, '2002-12-01'),
(61, 2.80, 35, '2003-01-02'),
(62, 2.80, 35, '2003-02-02'),
(63, 2.80, 35, '2003-03-02'),
(64, 2.80, 35, '2003-04-02'),
(65, 3.00, 35, '2003-05-02'),
(66, 2.80, 35, '2003-06-02'),
(67, 2.80, 35, '2003-07-02'),
(68, 2.80, 35, '2003-08-02'),
(69, 2.80, 35, '2003-08-02'),
(70, 2.80, 35, '2003-10-02'),
(71, 2.80, 35, '2003-11-02'),
(72, 2.80, 35, '2003-12-02'),
(73, 2.80, 35, '2004-01-02'),
(74, 2.80, 35, '2004-02-02'),
(75, 5.50, 35, '2004-03-02'),
(76, 6.00, 35, '2004-04-02'),
(77, 6.00, 35, '2004-05-02'),
(78, 5.50, 35, '2004-06-02'),
(79, 5.50, 35, '2004-07-02'),
(80, 6.50, 35, '2004-08-02'),
(81, 6.50, 35, '2004-08-02'),
(82, 7.00, 35, '2004-10-02'),
(83, 7.00, 35, '2004-11-02'),
(84, 7.50, 35, '2004-12-02'),
(85, 7.30, 35, '2005-01-02'),
(86, 7.30, 35, '2005-02-02'),
(87, 11.00, 35, '2005-03-02'),
(88, 11.00, 35, '2005-04-02'),
(89, 11.00, 35, '2005-05-02'),
(90, 11.00, 35, '2005-06-02'),
(91, 11.00, 35, '2005-07-02'),
(92, 8.00, 35, '2005-08-02'),
(93, 8.00, 35, '2005-08-02'),
(94, 6.50, 35, '2005-10-02'),
(95, 7.50, 35, '2005-11-02'),
(96, 7.50, 35, '2005-12-02'),
(97, 7.00, 35, '2006-01-02'),
(98, 7.00, 35, '2006-02-02'),
(99, 7.00, 35, '2006-03-02'),
(100, 7.00, 35, '2006-04-02'),
(101, 7.00, 35, '2006-05-02'),
(102, 7.00, 35, '2006-06-02'),
(103, 7.00, 35, '2006-07-02'),
(104, 7.00, 35, '2006-08-02'),
(105, 7.00, 35, '2006-08-02'),
(106, 5.00, 35, '2006-10-02'),
(107, 5.00, 35, '2006-11-02'),
(108, 5.00, 35, '2006-12-02'),
(109, 5.00, 35, '2007-01-02'),
(110, 5.00, 35, '2007-02-02'),
(111, 5.00, 35, '2007-03-02'),
(112, 5.00, 35, '2007-04-02'),
(113, 5.00, 35, '2007-05-02'),
(114, 5.00, 35, '2007-06-02'),
(115, 5.50, 35, '2007-07-02'),
(116, 5.50, 35, '2007-08-02'),
(117, 5.50, 35, '2007-08-02'),
(118, 8.00, 35, '2007-10-02'),
(119, 8.00, 35, '2007-11-02'),
(120, 8.00, 35, '2007-12-02'),
(121, 8.00, 35, '2008-01-02'),
(122, 8.00, 35, '2008-02-02'),
(123, 8.00, 35, '2008-03-02'),
(124, 8.00, 35, '2008-04-02'),
(125, 7.00, 35, '2008-05-02'),
(126, 7.00, 35, '2008-06-02'),
(127, 7.00, 35, '2008-07-02'),
(128, 7.00, 35, '2008-08-02'),
(129, 7.00, 35, '2008-08-02'),
(130, 8.00, 35, '2008-10-02'),
(131, 7.00, 35, '2008-11-02'),
(132, 5.50, 35, '2008-12-02'),
(133, 5.50, 35, '2009-01-02'),
(134, 5.50, 35, '2009-02-02'),
(135, 5.50, 35, '2009-03-02'),
(136, 5.50, 35, '2009-04-02'),
(137, 7.00, 35, '2009-05-02'),
(138, 7.00, 35, '2009-06-02'),
(139, 7.00, 35, '2009-07-02'),
(140, 7.00, 35, '2009-08-02'),
(141, 7.00, 35, '2009-08-02'),
(142, 7.00, 35, '2009-10-02'),
(143, 8.50, 35, '2009-11-02'),
(144, 10.00, 35, '2009-12-02'),
(145, 12.00, 35, '2010-01-02'),
(146, 12.00, 35, '2010-02-02'),
(147, 12.00, 35, '2010-03-02'),
(148, 12.00, 35, '2010-04-02'),
(149, 12.00, 35, '2010-05-02'),
(150, 14.50, 35, '2010-06-02'),
(151, 14.00, 35, '2010-07-02'),
(152, 12.00, 35, '2010-08-02'),
(153, 12.00, 35, '2010-08-02'),
(154, 16.00, 35, '2010-10-02'),
(155, 17.00, 35, '2010-11-02'),
(156, 17.00, 35, '2010-12-02'),
(157, 15.00, 35, '2011-01-02'),
(158, 15.00, 35, '2011-02-02'),
(159, 15.00, 35, '2011-03-02'),
(160, 15.00, 35, '2011-04-02'),
(161, 15.00, 35, '2011-05-02'),
(162, 15.00, 35, '2011-06-02'),
(163, 15.00, 35, '2011-07-02'),
(164, 15.00, 35, '2011-08-02'),
(165, 15.00, 35, '2011-08-02'),
(166, 8.00, 35, '2011-10-02'),
(167, 9.00, 35, '2011-11-02'),
(168, 8.50, 35, '2011-12-02');-- ----------------------------------------------------------
-- 表的结构 `yc_pinzhong`
--CREATE TABLE IF NOT EXISTS `yc_pinzhong` (
  `yc_id` int(11) NOT NULL AUTO_INCREMENT,
  `yc_name` varchar(20) CHARACTER SET utf8 NOT NULL,
  `yc_fenlei` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `yc_jianpin` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  `yc_quanpin` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `yc_chandi` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `yc_guige` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `yc_shuxing` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`yc_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=gb2312 COMMENT='药材品种' AUTO_INCREMENT=41 ;--
-- 转存表中的数据 `yc_pinzhong`
--INSERT INTO `yc_pinzhong` (`yc_id`, `yc_name`, `yc_fenlei`, `yc_jianpin`, `yc_quanpin`, `yc_chandi`, `yc_guige`, `yc_shuxing`) VALUES
(35, '白菜 ', '1', 'byz', 'bayuezha', '安徽', '统货', '2'),
(36, '大豆', '3', 'bd', 'badou', '四川', '统货', '2'),
(37, '测试1', '1', 'bjt', 'bajitian', '广东', '肉', '2'),
(38, '测试2', '3', 'bdk', 'baidoukou', '进口', '统货', '2'),
(39, '测试3', '3', 'bhj', 'baihujiao', '海南', '统货', '2'),
(40, '测试4', '9', 'bhs', 'baihuashe', '江西', '金钱/条', '2');/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
===================
我要实现如下查询结果：名称  产地  规格   最新价格
白菜  安徽  统货   8.50
...
...=================
我目前用的查询语句：
select yc_pinzhong.yc_name,yc_pinzhong.yc_chandi,yc_pinzhong.yc_guige,yc_jiage.yc_jiage from yc_pinzhong left join yc_jiage on yc_pinzhong.yc_id=yc_jiage.yc_item_id   执行结果，右表对应的价格有上百条，结果全被取出来，我只要取出最新的一条价格。
其实就是 mysql left join 右表多条数据重复问题
mysql> select yc_name as `名称`,
    ->  yc_chandi as `产地`,
    ->  yc_guige as `规格`,
    ->  yc_jiage as `最新价格`
    -> from yc_pinzhong a ,yc_jiage b
    -> where a.yc_id=b.yc_item_id
    -> and not exists (select 1 from yc_jiage where yc_item_id=b.yc_item_id and add_date>b.add_date);
+-------+------+------+----------+
| 名称  | 产地 | 规格 | 最新价格 |
+-------+------+------+----------+
| 白菜  | 安徽 | 统货 |     8.50 |
+-------+------+------+----------+
1 row in set (0.07 sec)mysql>