使用SELECT语句从另一个表中填充带有数据的临时表

发布于 2025-01-20 08:38:49 字数 1022 浏览 0 评论 0原文

我想根据宠物表中的宠物物种的选择,以选择临时表(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;

I want to achieve the expected output in this image

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

二手情话 2025-01-27 08:38:49

您可以这样实现(在这里

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT 
 case species when 'cat' then name else '' end,
 case species when 'dog' then name else '' end,
 case species when 'weasel' then name else '' end,
 case species when 'chinchilla' then name else '' end
from pets;

You can achieve it like this (result here)

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT 
 case species when 'cat' then name else '' end,
 case species when 'dog' then name else '' end,
 case species when 'weasel' then name else '' end,
 case species when 'chinchilla' then name else '' end
from pets;
旧时模样 2025-01-27 08:38:49

有点复杂,但可能是这样的:

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT MAX(CASE WHEN species='cat' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'cats',
       MAX(CASE WHEN species='dog' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'dogs',
       MAX(CASE WHEN species='weasel' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'weasel',
       MAX(CASE WHEN species='chinchilla' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'chinchillas' 
  FROM
  (SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species) p 
 JOIN 
  (SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
    ON seq <= cnt
GROUP BY seq
ORDER BY seq;

基本查询组件是:

SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species

将返回以下结果:

物种snamecnte cntecnt
catgrit,shogun,sierra3
ChinchillaChevy,Fay,Mojo3
DogHex,Lunar2
Weasel 2 WeaselBarker,Peter2

将其作为子查询,然后添加查询以将自定义数字序列生成join> join带有基本查询:

(SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
/*with ON like this*/
  ON seq <= cnt

此处的主要亮点可能是max(case .. expression in 选择。 获取。

>

It's a bit complicated but it's possible like this:

INSERT INTO tmp(cats, dogs, weasels, chinchillas) 
SELECT MAX(CASE WHEN species='cat' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'cats',
       MAX(CASE WHEN species='dog' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'dogs',
       MAX(CASE WHEN species='weasel' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'weasel',
       MAX(CASE WHEN species='chinchilla' 
             THEN SUBSTRING_INDEX(SUBSTRING_INDEX(sname,',',seq),',',-1) END) AS 'chinchillas' 
  FROM
  (SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species) p 
 JOIN 
  (SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
    ON seq <= cnt
GROUP BY seq
ORDER BY seq;

The base query component is:

SELECT species, 
       GROUP_CONCAT(name) AS sname,
       COUNT(*) cnt
    FROM pets
   GROUP BY species

That will return the following result:

speciessnamecnt
catGrit,Shogun,Sierra3
chinchillaChevy,Fay,Mojo3
dogHex,Lunar2
weaselBarker,Peter2

Make that as a subquery then add a query to generate a custom number sequences to JOIN with the base query:

(SELECT 1 seq UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) s
/*with ON like this*/
  ON seq <= cnt

The main highlight here is probably the MAX(CASE .. expression in SELECT. We're doing SUBSTRING_INDEX() twice and make use of the custom generated sequence numbers to obtain which part of the group concatenated value of sname to get.

Demo fiddle

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文