create view 视图名
as
select a_id=a.id,a_name=a.name
,b_id=min(b.id),b_name=b.name --表2的id保留最小值
from 表1 a,表2 b
where a.name=b.name
group by a.id,a.name,b.name
as
select a_id=a.id,a_name=a.name
,b_id=min(b.id),b_name=b.name --表2的id保留最小值
from 表1 a,表2 b
where a.name=b.name
group by a.id,a.name,b.name
as
select a_id=a.id,a_name=a.name
,b_id=max(b.id),b_name=b.name --表2的id保留最大值
from 表1 a,表2 b
where a.name=b.name
group by a.id,a.name,b.name
as
select a_id=a.id,a_name=a.name,b_id,b.name
from 表1 a,(select Max(id),name from 表2 group by Name) b
where a.name=b.name
as
SELECT *
FROM 表1 a LEFT OUTER JOIN
(SELECT *
FROM 表2
WHERE id IN
(SELECT MAX(id)
FROM t3
GROUP BY name)) b ON a.name = b.name
如果a有多个字段>2,b有多个字段>2,上述各位group by后怎么查出其它字段呢?create view 视图名
as
SELECT *
FROM 表1 a LEFT OUTER JOIN
(SELECT *
FROM 表2
WHERE id IN
(SELECT MAX(id)
FROM 表2
GROUP BY name)) b ON a.name = b.name