如何选择多列作为一列 [英] How do I SELECT multiple columns as one column
问题描述
在一张有动物id的表格中，它的大坝id和sire id，我想看看
所有动物id的组合列表。我试过以下，没有工作，但没有给出任何错误信息：
从中选择regnum（
从nullid.angus_in union中选择regnum
从nullid.angus_in union选择sire_regnum作为regnum
从nullid.angus_in选择dam_regnum作为regnum
）as t
group by t.regnum有count（*）> 1;
我显然错过了SQL中的一些内容，但我看不出是什么。任何
的帮助，或指向精美手册相应部分的指示都将是
非常感谢，
In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:
select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don''t see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,
推荐答案
" Robert Stearns" < RS ********** @ charter.net>在消息中写道
新闻：10 ************* @ corp.supernews.com ...
"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...
在一张表中有一个动物id，它的大坝id和sire id，我想看看
所有动物id的组合列表。我尝试了以下，但没有工作，但没有给出任何错误信息：
从中选择regnum（
从nullid.angus_in union中选择regnum
选择sire_regnum作为来自nullid.angus_in union的regnum
选择dam_regnum作为来自nullid.angus_in的regnum
）作为t
分组由t.regnum进行计数（*）> 1;
我显然错过了SQL中的一些内容，但我看不出是什么。非常感谢任何
帮助或指向精细手册相应部分的指示，
In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:
select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don''t see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,
你能给我们表DDL和格式报告你想要的（
a几个例子）。我不太明白你在找什么。
Could you give us the table DDL and the format of the report you want (with
a couple of examples). I don''t understand exactly what you are looking for.
Mark A写道：
Mark A wrote:
" Robert Stearns" < RS ********** @ charter.net>在消息中写道
新闻：10 ************* @ corp.supernews.com ...
"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...
在一张表中有一个动物id，它的大坝id和sire id，我想看看
所有动物id的组合列表。我尝试了以下，但没有工作，但没有给出任何错误信息：
从中选择regnum（
从nullid.angus_in union中选择regnum
选择sire_regnum作为来自nullid.angus_in union的regnum
选择dam_regnum作为来自nullid.angus_in的regnum
）作为t
分组由t.regnum进行计数（*）> 1;
我显然错过了SQL中的一些内容，但我看不出是什么。任何
帮助，或指向精细手册相应部分的指示将非常感谢，
In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:
select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don''t see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,
你能给我们表DDL和格式的报告你想要的（有几个例子）。我不太明白你在找什么。
Could you give us the table DDL and the format of the report you want (with
a couple of examples). I don''t understand exactly what you are looking for.
桌子的ddl是：
- halshome.net-DB2inst1-ANIMALS生成脚本的开始（是）
- Oct-19-2003 at 15:09:00
- drop table nullid.angus_in;
CREATE TABLE nullid.angus_in
（纹身CHARACTER（10）NOT NULL，
tag CHARACTER （5），
性别CHARACTER（1），
birth_date DATE，
regnum BIGINT，
name VARCHAR （50），
dam_tattoo字符（10），
sire_tattoo字符（10），
活动字符（1）非空，
sire_regnum BIGINT，
dam_regnum BIGINT
）
数据捕获无;
我想要的输出是所有regnums的单列，在三个regnum列中的任何一个组合中至少两次出现
。
The ddl for the table is :
-- Start of generated script for halshome.net-DB2inst1-ANIMALS (is)
-- Oct-19-2003 at 15:09:00
-- drop table nullid.angus_in;
CREATE TABLE nullid.angus_in
(tattoo CHARACTER(10) NOT NULL,
tag CHARACTER(5),
sex CHARACTER(1),
birth_date DATE,
regnum BIGINT,
name VARCHAR(50),
dam_tattoo CHARACTER(10),
sire_tattoo CHARACTER(10),
active CHARACTER(1) NOT NULL,
sire_regnum BIGINT,
dam_regnum BIGINT
)
DATA CAPTURE NONE;
The output I want is a single column of all regnums which occur at
least twice in any of the three regnum columns combined.
试试这个：
SELECT T.regnum FROM nullid.angus_in，
TABLE（VALUES（regnum），
（sire_regnum），
（dam_regnum））
AS T（regnum）
GROUP BY T.regnum HAVING COUNT（*）> 1;
干杯
Serge
-
Serge Rielau
DB2 SQL编译器开发
IBM多伦多实验室
Try this:
SELECT T.regnum FROM nullid.angus_in,
TABLE(VALUES (regnum),
(sire_regnum),
(dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
这篇关于如何选择多列作为一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
