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/