连接上的最小行
我有一个奇怪的问题,请检查以下 SQL
CREATE TABLE `tablea` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL
) ENGINE = MYISAM ;
CREATE TABLE `tableb` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL ,
`refid` INT NOT NULL ,
`position` INT NOT NULL
) ENGINE = MYISAM ;
INSERT INTO tablea (`id`,`name`) VALUE (1,'a'),(2,'b'),(3,'c');
INSERT INTO tableb (`name`, `refid`, `position`) VALUE ('a', 1, 2),('b', 1, 1);
INSERT INTO tableb (`name`, `refid`, `position`) VALUE ('a', 2, 1),('b', 2, 2);
Tableb 包含 0 或更多行引用 tablea。我想调用 tablea 中的记录以及 tableb 中具有最低 (MIN) 位置的记录。所以目前这是一段简单的 SQL。
SELECT
a.id,
a.name AS namea,
b.name AS nameb,
GROUP_CONCAT(b.name),
CAST(group_concat(b.position) AS CHAR )
FROM tablea AS a
LEFT JOIN tableb AS b
ON b.refid=a.id
GROUP BY a.id
ORDER BY b.position ASC
如果运行此命令,您将看到它输出
id namea nameb concat concat
3 c NULL NULL NULL
1 a b b,a 1,2
2 b b b,a 2,1
The first &第二行是正确的,但第三行我期望 nameb 是 a 而不是 b。我尝试过与 MIN 一起玩,但无法找到总能让我得到我所期望的东西。感谢任何帮助,目前我只需要做 2 个查询:(
I have a bit of a strange problem, please examine the following SQL
CREATE TABLE `tablea` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL
) ENGINE = MYISAM ;
CREATE TABLE `tableb` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL ,
`refid` INT NOT NULL ,
`position` INT NOT NULL
) ENGINE = MYISAM ;
INSERT INTO tablea (`id`,`name`) VALUE (1,'a'),(2,'b'),(3,'c');
INSERT INTO tableb (`name`, `refid`, `position`) VALUE ('a', 1, 2),('b', 1, 1);
INSERT INTO tableb (`name`, `refid`, `position`) VALUE ('a', 2, 1),('b', 2, 2);
Tableb holds 0 or more rows referencing tablea. I want to recall the records in tablea with the record in tableb that has the lowest (MIN) position. So at the moment this is a simple bit of SQL.
SELECT
a.id,
a.name AS namea,
b.name AS nameb,
GROUP_CONCAT(b.name),
CAST(group_concat(b.position) AS CHAR )
FROM tablea AS a
LEFT JOIN tableb AS b
ON b.refid=a.id
GROUP BY a.id
ORDER BY b.position ASC
If you run this you will see it output
id namea nameb concat concat
3 c NULL NULL NULL
1 a b b,a 1,2
2 b b b,a 2,1
The first & second row is correct but the third I was expecting nameb to be a and not b. I have tried playing around with MIN but cannot seam to get something that always returns me what I am expecting. Any help appreciated, at the moment I am just having to do 2 queries :(
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)