CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.');

/////////////////////////////////////////////////////////


然后进行实际查询:

SELECT a.id, a.rev, a.content
FROM `docs` a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev;


SELECT a.*
FROM `docs` a
LEFT OUTER JOIN `docs` b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;


a是某种占位符?
在哪里可以找到对此英语的解释?
我希望有人能引导我们完成这项工作并使之易于理解。
谢谢!

最佳答案

“ a”是表别名:

SELECT a.id, a.rev, a.content
FROM `docs` a    # HERE, on this line, the table is given an alias of a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev;

That alias is declared immediately after the table name in the from clause (as indicated above).

Aliases are used to abbreviate and/or to clarify a query. There are several places where one can declare an alias e.g.

#table alias
from tablex as x

#derived table (subquery) alias
select d.* from (select * from t where col1 = 'xyz') as d

#column alias
select col1 as xyz
from tablex as x


请注意,在大多数数据库中,上面看到的“ as”是可选的,Oracle在声明表或子查询别名时不允许使用“ as”。

自我加入

将表联接到表别名时,表别名是至关重要的

select
     t1.id as t1_id
   , t2.id as t2_id
from tablex as t1
inner join tablex as t2 on t1.fk = t2.id


没有这些别名t1和t2,该查询将无法正常工作。



编辑

令人遗憾的是,我们经常看到别名,其中别名是在查询中“按顺序”定义的。在您的示例中,第一个是“ a”,第二个是“ b”,依此类推。

那是不好的做法。使用别名的一种更有意义的方法是使用表名称中每个单词的“首字母”或为子查询分配一些含义。

在示例中,我建议使用“ d”(对于文档)和“ mr”(对于max(rev))

SELECT d.id, d.rev, d.content, mr.rev
FROM `docs` as d
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM `docs`
    GROUP BY id
) as mr ON d.id = mr.id AND d.rev = mr.rev;

关于mysql - 在此sql Fifddle示例中,“a。”字符从何而来?它根本不在 table 上,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/54936813/

10-11 10:36