在 SQL 内连接中使用别名 [英] Using aliases in SQL inner join
问题描述
我看过一些使用连接来获得组中最高值的示例,但我尝试过的方法不喜欢在内连接之外使用别名.
I've had a look at a few examples of using joins to get the highest value in a group, but the methods I have tried don't like the use of aliases outside of the inner join.
SELECT f.year, f.name, f.date_start, f.date_end, f.max_year FROM(SELECT EXTRACT(year FROM date_start) AS year, MAX(DATEDIFF(date_end,date_start)) AS max_yearFROM mytable GROUP BY 年份)AS x 内连接 mytable AS f on f.year = x.year 和 f.max_year = x.max_year;
所以如果我有一张桌子:
So if I have a table:
名称 date_start date_end约翰 1950-04-05 1960-07-08杰克 1950-04-06 1960-12-31马克 1954-01-01 1970-01-01简 1954-10-10 1978-10-01
然后我希望它为 start_date 的每一年调出两个日期之间差距最大的条目:
Then I want it to bring up the entries that have the greatest gap between the two dates, for each year of the start_date:
年份名称 date_start date_end max_year1950 杰克 1950-04-06 1960-12-31 39221954 简 1954-10-10 1978-10-01 8758
关于如何解决这个问题有什么建议吗?
Any suggestions of how to get around this?
推荐答案
看起来您在 SELECT 列表中使用了错误的别名,这应该更好:
It looks like you are using wrong aliases in your SELECT list, this should be better:
SELECT x.year, f.name, f.date_start, f.date_end, x.max_year FROM
(
SELECT
EXTRACT(year FROM date_start) AS year,
MAX(DATEDIFF(date_end,date_start)) AS max_year
FROM mytable GROUP BY year
) AS x inner join
mytable AS f on EXTRACT(year FROM f.date_start) = x.year
AND DATEDIFF(f.date_end, f.date_start) = x.max_year;
但是,我会这样做:
SELECT name, date_start, date_end
FROM mytable f
WHERE NOT EXISTS (
SELECT * FROM mytable
WHERE
EXTRACT(year FROM date_start) = EXTRACT(year FROM f.date_start) AND
DATEDIFF(date_end, date_start) > DATEDIFF(f.date_end, f.date_start)
)
这篇关于在 SQL 内连接中使用别名的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!