使用SELECT语句从另一个表中填充带有数据的临时表
我想根据宠物表中的宠物物种的选择,以选择临时表(TMP)填充我的临时表(TMP)。但是我对所有专栏都无效 是我到目前为止写过的查询的创建表
create table pets ( name varchar(255), species varchar(255) );
insert into pets (name, species) values ('Barker', 'weasel'), ('Chevy', 'chinchilla'), ('Fay', 'chinchilla'), ('Grit', 'cat'), ('Hex', 'dog'), ('Lunar', 'dog'), ('Mojo', 'chinchilla'), ('Peter', 'weasel'), ('Shogun', 'cat'), ('Sierra', 'cat');
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
cats VARCHAR(255),
dogs VARCHAR(255),
weasels VARCHAR(255),
chinchillas VARCHAR(255));
INSERT INTO tmp(cats, dogs, weasels, chinchillas)
VALUES
(
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'cat')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'dog')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'weasel')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'chinchilla'))
);
SELECT * FROM tmp;
这 /a>
I want to populate my temporary table (tmp) with the result of the SELECT statement based on the species of the pet from the pets table. But I am getting NULL for all columns
This is my CREATE TABLE with the queries I have written so far
create table pets ( name varchar(255), species varchar(255) );
insert into pets (name, species) values ('Barker', 'weasel'), ('Chevy', 'chinchilla'), ('Fay', 'chinchilla'), ('Grit', 'cat'), ('Hex', 'dog'), ('Lunar', 'dog'), ('Mojo', 'chinchilla'), ('Peter', 'weasel'), ('Shogun', 'cat'), ('Sierra', 'cat');
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY TABLE tmp (
cats VARCHAR(255),
dogs VARCHAR(255),
weasels VARCHAR(255),
chinchillas VARCHAR(255));
INSERT INTO tmp(cats, dogs, weasels, chinchillas)
VALUES
(
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'cat')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'dog')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'weasel')),
(SELECT name FROM pets WHERE species IN (SELECT name FROM pets WHERE species = 'chinchilla'))
);
SELECT * FROM tmp;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您可以这样实现(在这里)
You can achieve it like this (result here)
有点复杂,但可能是这样的:
基本查询组件是:
将返回以下结果:
将其作为子查询,然后添加查询以将自定义数字序列生成
join> join
带有基本查询:此处的主要亮点可能是
max(case ..
expression in选择
。 获取。>
It's a bit complicated but it's possible like this:
The base query component is:
That will return the following result:
Make that as a subquery then add a query to generate a custom number sequences to
JOIN
with the base query:The main highlight here is probably the
MAX(CASE ..
expression inSELECT
. We're doingSUBSTRING_INDEX()
twice and make use of the custom generated sequence numbers to obtain which part of the group concatenated value ofsname
to get.Demo fiddle