需要有关复杂查询的帮助
需要有关复杂查询的帮助
提问于 2011-05-12 06:32:40
我的结果集看起来很好，但我得到了额外的重复结果，这是我想要避免的，下面是这种情况：
我使用两个表：
订单
id|companyname|routeid|orderdate
1 |company1 | 23dsa |
2 |company1 | 23dsa | 2011-04-28
3 |company1 | 23dsa | 2011-04-05
4 |company2 | 24gcc | 2011-22-05车辆
id| vehiclename | routeid
1 | van1 | 23dsa
2 | van2 | 24gcc 这是我的问题1：
select t1.companyname, max(t1.orderdate) as orderdate
from order t1, vehicle t2 where t1.routeid=t2.routeid
group by t1.companyname, t1.orderdate
order by t1.id这是查询2：
select t1.companyname, max(t1.orderdate) as orderdate
from order t1, vehicle t2 where t1.routeid=t2.routeid
group by t1.companyname, t1.orderdate having count(1) = 1
order by t1.id这是我的结果：
companyname | orderdate
company1 |
company1 | 2011-04-28
company1 | 2011-04-05
company2 | 2011-22-05我希望是这样的：
companyname | orderdate
company1 | 2011-04-28
company2 | 2011-22-05我缺少的不是null：
select t1.companyname, max(t1.orderdate) as orderdate
from order t1, vehicle t2 where t1.orderdate is not null and t1.routeid=t2.routeid
group by t1.companyname, t1.orderdate
order by t1.id回答 2
Stack Overflow用户
回答已采纳
发布于 2011-05-12 06:40:43
下面是什么：
SELECT
companyname, MAX(orderdate) AS orderdate
FROM
`order`
GROUP BY
companyname
ORDER BY
id如果你需要车里的东西：
SELECT
companyname, MAX(orderdate) AS orderdate
FROM
`order` o, vehicle v
WHERE
o.routeid=v.routeid
GROUP BY
companyname
ORDER BY
o.idStack Overflow用户
发布于 2011-05-12 06:39:20
select t1.companyname, max(t1.orderdate) as orderdate
from order t1, vehicle t2 where t1.routeid=t2.routeid
group by t1.companyname
order by t1.id不需要按orderdate分组，因为您只选择了一个。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5971357
复制相关文章
相似问题
腾讯云开发者
