表A
BuyID ClientID ProductID
1 1 1
2 3 2
3 2 3表B
ProductID Networth Date
1 2.135 2010-4-1
1 2.3215 2010-5-1
2 100 2010-4-10
2 89 2010-5-12现得到如下结果:
BuyID ClientID ProductID Networth
1 1 1 2.3215(这个要最新日期的数据)
BuyID ClientID ProductID
1 1 1
2 3 2
3 2 3表B
ProductID Networth Date
1 2.135 2010-4-1
1 2.3215 2010-5-1
2 100 2010-4-10
2 89 2010-5-12现得到如下结果:
BuyID ClientID ProductID Networth
1 1 1 2.3215(这个要最新日期的数据)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式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)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
[征集]分组取最大N条记录方法征集,及散分....
`BuyID` int(11) NOT NULL auto_increment,
`ClientID` varchar(50) default NULL,
`ProductID` int(11) default NULL,
PRIMARY KEY (`BuyID`)
) TYPE=MyISAM;
insert into Zcml_Buy select '01','01','01'
insert into test1 select '02','01','02'CREATE TABLE `Zcml_Networth` (
`NetworthID` int(11) NOT NULL auto_increment,
`ProductID` int(11) default NULL,
`Networth` double default NULL,
`Date` datetime default NULL,
PRIMARY KEY (`NetworthID`)
) TYPE=MyISAM;
insert into test1 select '01','01','2.1','2010-2-1'
insert into test1 select '02','01','2.2','2010-1-1'
insert into test1 select '02','02','50','2010-5-8'
insert into test1 select '02','02','47','2010-3-10'想要得到这样的结果
BuyID ProductID Networth Date
---------- -------------------- ---------- --------------------
01 01 2.1 2010-2-1
02 02 50 2010-5-8
-> `BuyID` int(11) NOT NULL auto_increment,
-> `ClientID` varchar(50) default NULL,
-> `ProductID` int(11) default NULL,
-> PRIMARY KEY (`BuyID`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.13 sec)mysql> insert into Zcml_Buy select '01','01','01';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> insert into test1 select '02','01','02';
ERROR 1146 (42S02): Table 'csdn.test1' doesn't exist
mysql>
mysql> CREATE TABLE `Zcml_Networth` (
-> `NetworthID` int(11) NOT NULL auto_increment,
-> `ProductID` int(11) default NULL,
-> `Networth` double default NULL,
-> `Date` datetime default NULL,
-> PRIMARY KEY (`NetworthID`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.02 sec)mysql> insert into test1 select '01','01','2.1','2010-2-1';
ERROR 1146 (42S02): Table 'csdn.test1' doesn't exist
mysql> insert into test1 select '02','01','2.2','2010-1-1';
ERROR 1146 (42S02): Table 'csdn.test1' doesn't exist
mysql> insert into test1 select '02','02','50','2010-5-8';
ERROR 1146 (42S02): Table 'csdn.test1' doesn't exist
mysql> insert into test1 select '02','02','47','2010-3-10';
ERROR 1146 (42S02): Table 'csdn.test1' doesn't exist
mysql>
Query OK, 0 rows affected (0.28 sec)mysql> insert into a values(1,1,1),(2,3,2),(3,2,3);
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> create table B(productid int,networth varchar(20),date varchar(20));
Query OK, 0 rows affected (0.25 sec)mysql> insert into b values(1,'2.135','2010-04-01'),(1,'2.3215','2010-05-01'),(2,'100','2010-04-01'),(2,'89','2010-05-12');
Query OK, 4 rows affected (0.19 sec)
Records: 4 Duplicates: 0 Warnings: 0mysql> select * from A;
+-------+----------+-----------+
| buyid | clientid | productid |
+-------+----------+-----------+
| 1 | 1 | 1 |
| 2 | 3 | 2 |
| 3 | 2 | 3 |
+-------+----------+-----------+
3 rows in set (0.00 sec)mysql> select * from B;
+-----------+----------+------------+
| productid | networth | date |
+-----------+----------+------------+
| 1 | 2.135 | 2010-04-01 |
| 1 | 2.3215 | 2010-05-01 |
| 2 | 100 | 2010-04-01 |
| 2 | 89 | 2010-05-12 |
+-----------+----------+------------+
4 rows in set (0.00 sec)mysql> select buyid,clientid,a.productid,networth,date from b inner join a on a.productid=b.productid
-> inner join (select productid,max(date) as b from b group by productid) c on a.productid=c.productid
-> where b.date=c.b;
+-------+----------+-----------+----------+------------+
| buyid | clientid | productid | networth | date |
+-------+----------+-----------+----------+------------+
| 1 | 1 | 1 | 2.3215 | 2010-05-01 |
| 2 | 3 | 2 | 89 | 2010-05-12 |
+-------+----------+-----------+----------+------------+
2 rows in set (0.00 sec)mysql>
版主,可以留个邮箱给我吗??有些技术问题,我想请教您。
TABLE `Zcml_Buy` (
`BuyID` int(11) NOT NULL auto_increment,
`ClientID` varchar(50) default NULL,
`ProductID` int(11) default NULL,
PRIMARY KEY (`BuyID`)
) TYPE=MyISAM;1 2 3
2 1 4
3 2 4
4 1 3
5 3 3
6 4 4
7 4 3
8 3 4CREATE TABLE `Zcml_Networth` (
`NetworthID` int(11) NOT NULL auto_increment,
`ProductID` int(11) default NULL,
`Networth` double default NULL,
`Date` datetime default NULL,
PRIMARY KEY (`NetworthID`)
) TYPE=MyISAM;
1 3 2.15688 2010-01-01 00:00:00
2 3 2.38 2010-02-01 00:00:00
3 3 2.66 2010-03-01 00:00:00
4 4 10000000 2010-05-01 00:00:00
5 4 11000000 2010-04-01 00:00:00
6 4 100000001 2010-03-01 00:00:00
7 4 999999999 2010-01-01 00:00:00
8 4 100000001 2010-02-01 00:00:00
9 3 3.122223 2010-04-01 00:00:00上面的数据是表里面复制出来的,肯定没错。
Zcml_Buy记录客户购买的产品,Zcml_Networth是产品的价格,想查询客户购买了哪些产品,以及该产品的最新价格。
select
Zcml_Buy.BuyID,
Zcml_Buy.ProductID,
Zcml_Networth.Networth,
Zcml_Networth.PublishDate
from Zcml_Buy
left join Zcml_Networth on Zcml_Buy.ProductID=Zcml_Networth.ProductID
where Zcml_Buy.ClientID='1'
group by Zcml_Buy.BuyID
order by Zcml_Buy.BuyStatus,Zcml_Buy.BuyEndDate DESC
结果如下:
4 3 2.15688 2010-01-01 00:00:00
2 4 10000000 2010-05-01 00:00:00
这样得到的产品价格不是最新的,我想要得到这样的结果
BuyID ProductID Networth Date
---------- -------------------- ---------- --------------------
4 3 2.66 2010-03-01 00:00:00
2 4 10000000 2010-05-01 00:00:00
另数据库是mysql4.0,好像不支持子查询。望不啬赐教。