RT 假设有这么一张表 字段如下

t1  t2 t3

01 a1 1

01 a2 2

02 b1 1

02 b2 2

7个回答

[code="sql"]select wp.*, wb.* from wu_plan wp, wu_bom wb,
(select plan, min(amount) amount from wu_bom group by plan) t
where t.plan=wb.plan and t.amount = wb.amount and t.plan = wp.plan
and wp.plannum>wp.prixis[/code]

[code="sql"]
--oracle 不知是否满足要求
select a1.t2 from test a1 where a1.t3 in(select min(a2.t3) from test a2 group by a2.t1) and a1.t1='01';
[/code]

CREATE TABLE `test` (
`t1` varchar(20) default NULL,
`t2` varchar(20) default NULL,
`t3` varchar(20) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `test` */

insert into `test`(`t1`,`t2`,`t3`) values ('01','a1','1'),('01','a2','2'),('02','b1','1'),('02','b2','2');

select t1,t2,t3 from test group by t1 order by t3我用的是mysql

t1 t2 t3

01 a1 1
02 b1 1

select t2 from table1 c ,(select t1 , min(t3) ts from table1 a group by t1) b where c.t1 = b.t1 and c.t3 = b.ts

[code="java"]
CREATE TABLE `wu_bom` (
`id` bigint(20) default NULL,
`plan` varchar(20) default NULL,
`pact` varchar(20) default NULL,
`amount` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `wu_bom` */

insert into `wu_bom`(`id`,`plan`,`pact`,`amount`) values (1,'00001','aa1',300),(2,'00001','aa2',200),(3,'00002','bb1',500),(4,'00002','bb2',800),(5,'00002','bb3',400);

/*Table structure for table `wu_plan` */

DROP TABLE IF EXISTS `wu_plan`;

CREATE TABLE `wu_plan` (
`id` bigint(20) default NULL,
`plan` varchar(20) default NULL,
`model` varchar(20) default NULL,
`corp_code` varchar(20) default NULL,
`plannum` int(11) default NULL,
`prixis` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `wu_plan` */

insert into `wu_plan`(`id`,`plan`,`model`,`corp_code`,`plannum`,`prixis`) values (1,'00001','exx22','nokia',2000,2001),(2,'00002','lc001','sony',3000,0);

[/code]

[code="java"]
set profiling=1; //这个先执行

select b.id,b.plan,b.pact,b.amount,p.id,p.model,p.corp_code,p.plannum,p.prixis from wu_bom b
inner join wu_plan p on b.plan=p.plan and plannum>prixis
group by plan order by amount;

show profiles; //这个可以用来查看上面这条SQL语句执行的时间是多少秒
[/code]

[code="java"]select wu_plan.*, pact, amount from wu_plan
left join (
select * from wu_bom where plan in(
select plan, min(amount) from wu_bom
group by plan
)
)tmp on wu_plan.plan = tmp.plan
where plannum > prixis[/code]

[quote]

xutao5641745 写道

Java代码 收藏代码

1. CREATE TABLE `wu_bom` (
2. `id` bigint(20) default NULL,
3. `plan` varchar(20) default NULL,
4. `pact` varchar(20) default NULL,
5. `amount` int(11) default NULL
6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

7. /*Data for the table `wu_bom` */
1. insert into `wu_bom`(`id`,`plan`,`pact`,`amount`) values (1,'00001','aa1',300),(2,'00001','aa2',200),(3,'00002','bb1',500),(4,'00002','bb2',800),(5,'00002','bb3',400);

2. /*Table structure for table `wu_plan` */

3. DROP TABLE IF EXISTS `wu_plan`;

4. CREATE TABLE `wu_plan` (
5. `id` bigint(20) default NULL,
6. `plan` varchar(20) default NULL,
7. `model` varchar(20) default NULL,
8. `corp_code` varchar(20) default NULL,
9. `plannum` int(11) default NULL,
10. `prixis` int(11) default NULL
11. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

12. /*Data for the table `wu_plan` */

13. insert into `wu_plan`(`id`,`plan`,`model`,`corp_code`,`plannum`,`prixis`) values (1,'00001','exx22','nokia',2000,2001),(2,'00002','lc001','sony',3000,0);

14. CREATE TABLE `wu_bom` (
`id` bigint(20) default NULL,
`plan` varchar(20) default NULL,
`pact` varchar(20) default NULL,
`amount` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `wu_bom` */

insert into `wu_bom`(`id`,`plan`,`pact`,`amount`) values (1,'00001','aa1',300),(2,'00001','aa2',200),(3,'00002','bb1',500),(4,'00002','bb2',800),(5,'00002','bb3',400);

/*Table structure for table `wu_plan` */

DROP TABLE IF EXISTS `wu_plan`;

CREATE TABLE `wu_plan` (
`id` bigint(20) default NULL,
`plan` varchar(20) default NULL,
`model` varchar(20) default NULL,
`corp_code` varchar(20) default NULL,
`plannum` int(11) default NULL,
`prixis` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `wu_plan` */

insert into `wu_plan`(`id`,`plan`,`model`,`corp_code`,`plannum`,`prixis`) values (1,'00001','exx22','nokia',2000,2001),(2,'00002','lc001','sony',3000,0);

Java代码 收藏代码

1. set profiling=1; //这个先执行

2. select b.id,b.plan,b.pact,b.amount,p.id,p.model,p.corp_code,p.plannum,p.prixis from wu_bom b
3. inner join wu_plan p on b.plan=p.plan and plannum>prixis
4. group by plan order by amount;

5. show profiles; //这个可以用来查看上面这条SQL语句执行的时间是多少秒

set profiling=1; //这个先执行

select b.id,b.plan,b.pact,b.amount,p.id,p.model,p.corp_code,p.plannum,p.prixis from wu_bom b
inner join wu_plan p on b.plan=p.plan and plannum>prixis
group by plan order by amount;

show profiles; //这个可以用来查看上面这条SQL语句执行的时间是多少秒

[/quote]

``````我的语句中没有包含聚合函数，，，自然 group by 的时候不需要包含前面查询的所有列。。。。
``````