mysql - 连接查询中的“未知列”错误,旨在选择感兴趣组中的单行
问题描述
我可以知道为什么我会出错
“字段列表”中的未知列“wp_postmeta.post_ID”
以下是有问题的表格:
create table wp_posts (
ID integer primary key auto_increment,
post_title varchar(30),
post_type varchar(30)
);
create table wp_postmeta (
ID integer primary key auto_increment,
post_id integer,
meta_key varchar(30) not null default '_regular_price',
meta_value integer not null
);
insert into wp_posts (post_title, post_type) values
('Apple Pie','Product'),
('French Toast','Product'),
('Shepards Pie','Product'),
('Jam Pie','Product'),
('Jam Pie','Product'),
('Plate','Not a Product'),
('Bucket','Not a Product'),
('Chequebook','Not a Product'),
('French Toast','Product'),
('French Toast','Product');
insert into wp_postmeta (post_id, meta_value) values
(1,10),
(2,5),
(3,9),
(4,8),
(5,11),
(6,12),
(7,10),
(8,6),
(9,1),
(10,1);
这是查询:
SELECT wp_postmeta.post_ID, wp_posts.post_title, wp_postmeta.meta_value FROM (
SELECT wp_posts.post_title, MIN(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
WHERE wp_posts.post_type = 'Product'
GROUP BY wp_posts.post_title
)
as x inner join
(
SELECT wp_postmeta.post_id, wp_posts.post_title, wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
WHERE wp_posts.post_type = 'Product'
ORDER BY wp_posts.post_title, wp_postmeta.meta_value
)
as f on x.post_title = f.post_title and f.meta_value = x.minprice
如果您有兴趣,我正在改编本教程中的这段代码,它将为您提供我正在做的事情的上下文。
解决方案
您必须在选择中使用子查询别名,因为您使用了子查询
SELECT f.post_ID, x.post_title, f.meta_value
FROM (
SELECT wp_posts.post_title, MIN(wp_postmeta.meta_value) AS minprice
FROM wp_postmeta
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
WHERE wp_posts.post_type = 'Product'
GROUP BY wp_posts.post_title
)
as x inner join
(
SELECT wp_postmeta.post_id, wp_posts.post_title, wp_postmeta.meta_value
FROM wp_postmeta
JOIN wp_posts ON wp_postmeta.post_ID=wp_posts.ID
WHERE wp_posts.post_type = 'Product'
ORDER BY wp_posts.post_title, wp_postmeta.meta_value
)
as f on x.post_title = f.post_title and f.meta_value = x.minprice
推荐阅读
- google-cloud-platform - 如何使用 gcloud help 命令获取非交互式文本输出
- python - Python:chrome webdriver设置存储扩展文件的路径
- javascript - 具有 div class= parallax_background parallax-window 的网页未在 django/pycharm 中加载
- excel - 更改单元格颜色后 Excel 崩溃
- swift - 有没有更有效的方法可以在 swift 5 中组合日期和时间?
- microsoft-teams - 如何通过 API 查询 MS Teams 设备的状态?
- javascript - 如何每分钟自动打开一个新选项卡并关闭除 chrome 中的新选项卡以外的所有其他选项卡?
- java - 是否打算彻底检查现有的 Java 平台类(JEP 360)?
- javascript - AngularJS的ng-repeat in line,逗号分隔,两个属性在同一行
- dbt - 为正值编写 dbt 测试