SELECT * FROM" multiple_tables" WHERE" field_（in_those_tables）_have_the_same_paricular_v alue" ？ [英] SELECT * FROM "multiple_tables" WHERE "field_(in_those_tables)_have_the_same_paricular_v alue" ?
问题描述
大家好！
我有几个表格，我想从中提取SAME值（以及
其他参考数据）。
所有值都在表格的同一列中。
如何实现这一目标？
TIA。
Andro
*************************
例如:(管道账单）表格代表管道系统的材料）
TBL1-water
pos。项目类型大小评级
---------------------------------
01法兰3 300＃
02阀门晶片2 150＃
03阀球1 150＃
04 elr90 2"
TBL2-oil
（类似于上面）........ .etc。
----------------------------------
如何从这些表中选择（比方说）BALL VALVES以及pos。，size，
等等？
Hi everybody!
I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?
TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)
TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"
TBL2-oil
(similar like above).........etc.
----------------------------------
how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?
推荐答案
2006年4月24日星期一00:04:40 +0200，andro写道：
On Mon, 24 Apr 2006 00:04:40 +0200, andro wrote:
大家好！
我有几个表我想从中提取SAME值（以及其他引用的数据）。
所有值都在表格的同一列中。
我怎样才能实现这个目标？
TIA。
Andro
************************* 示例:(管道物料清单，表格代表管道系统）
TBL1-water
pos。项目类型尺寸等级
---------------------------------
01 flange 3 300＃
02阀门晶片2 150＃
03阀球1 150＃
04 elr90 2"
TBL2-oil
（类似上面）.........等............ ----- -----------------------------
如何从这些表中选择（比方说）BALL VALVES pos。，尺寸，
评级等？
Hi everybody!
I have several tables from which I want to exract the SAME value (along with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?
TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)
TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"
TBL2-oil
(similar like above).........etc.
----------------------------------
how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?
你好Andro，
选择''水'' AS系统，pos，项目，类型，尺寸，等级
FROM TBL1_water
WHERE type =''ball''
UNION ALL
选择''油''AS系统，pos，项目，类型，尺寸，等级
FROM TBL2_oil
WHERE type =''ball''
UNION ALL
等
但是更好的解决方案是使用一张桌子，用水/油/等
作为额外的列（主键的一部分），而不是将数据分成几个类似的表格。
-
Hugo Kornelis，SQL Server MVP
Hi Andro,
SELECT ''water'' AS system, pos, item, type, size, rating
FROM TBL1_water
WHERE type = ''ball''
UNION ALL
SELECT ''oil'' AS system, pos, item, type, size, rating
FROM TBL2_oil
WHERE type = ''ball''
UNION ALL
etc
But a better solution would be to use just one table, with water/oil/etc
as extra column (part of the primary key), instead of splitting the data
over several similar tables.
--
Hugo Kornelis, SQL Server MVP
Hugo！
非常感谢f或者你的帮助。
（我注意到这样一个简单的问题并不是那么容易解决，而是基于我的基本SQL知识）。
每次进入TBL_NAME都很难使用第一种方法（系统
）。
有没有更简单的方法 - 使用表名作为参数？
我喜欢你的第二种方法。
你会更具体地说明额外的列（***部分主要部分）
key ***）"
你的意思是什么？主键的一部分？
谢谢你。
" Hugo Kornelis" <胡** @ perFact.REMOVETHIS.info.INVALID>在消息中写道
news：f9 ******************************** @ 4ax.com ...
Hugo !
Thank you very much for your help.
(I''ve noticed that such a simple question is not so simple to resolve with
basic SQL knowledge such as mine).
It is difficult to use first approach by entering everytime TBL_NAME (system
here).
Is there any easier way - by using table names as "parameters"?
I like your second approach.
Would you be more specific about "extra column (***part of the primary
key***)"
What do you mean by "part of the primary key"?
Thank you.
"Hugo Kornelis" <hu**@perFact.REMOVETHIS.info.INVALID> wrote in message
news:f9********************************@4ax.com...
2006年4月24日星期一00:04:40 +0200，andro写道：
On Mon, 24 Apr 2006 00:04:40 +0200, andro wrote:
大家好！
我有几个表格，我想从中提取SAME值（沿
其他参考数据）。
所有值都在表格的同一列中。
我怎样才能做到这一点？
TIA。
Andro
********************* ****
例如:(管道物料清单，表格代表管道系统）
TBL1-water
pos。项目类型尺寸等级
---------------------------------
01 flange 3 300＃
02阀门晶片2 150＃
03阀球1 150＃
04 elr90 2"
TBL2-oil
（类似上面）.........等............ ----- -----------------------------
如何从这些表中选择（比方说）BALL VALVES pos。，尺寸，
评级等？
Hi everybody!
I have several tables from which I want to exract the SAME value (along
with
other referenced data).
All the values are in the same column within the tables.
How can I achieve this?
TIA.
Andro
*************************
example: (piping bill of material where tables represent piping systems)
TBL1-water
pos. item type size rating
---------------------------------
01 flange 3" 300#
02 valve wafer 2" 150#
03 valve ball 1" 150#
04 elr90 2"
TBL2-oil
(similar like above).........etc.
----------------------------------
how to select (say) BALL VALVES from these tables along with pos.,size,
rating etc.?
您好Andro，
SELECT''water''AS系统，pos，项目，类型，尺寸，等级
FROM TBL1_water
WHERE type =''ball''
UNION ALL
选择''油''AS系统，位置，项目，类型，尺寸，等级
FROM TBL2_oil
WHERE type =''ball''
UNION ALL
但更好的解决方案是只使用一张桌子，水/油/等
作为额外的列（主键的一部分），而不是将数据分成几个相似的表格。
-
Hugo Kornelis ，SQL Server MVP
Hi Andro,
SELECT ''water'' AS system, pos, item, type, size, rating
FROM TBL1_water
WHERE type = ''ball''
UNION ALL
SELECT ''oil'' AS system, pos, item, type, size, rating
FROM TBL2_oil
WHERE type = ''ball''
UNION ALL
etc
But a better solution would be to use just one table, with water/oil/etc
as extra column (part of the primary key), instead of splitting the data
over several similar tables.
--
Hugo Kornelis, SQL Server MVP
On Mon，2006年4月24日00:46:02 +0200，andro写道：
On Mon, 24 Apr 2006 00:46:02 +0200, andro wrote:
雨果！
谢谢你非常感谢你的帮助。
（我注意到这样一个简单的问题并不像我的基本SQL知识那样简单解决）。
很难用第一种方法是每次进入TBL_NAME（系统
此处）。
嗨Andro，
对不起，我不理解你留言的这一部分。
有没有更简单的方法 - 使用表名作为参数？
有，但不建议，因为安全
的影响。如果你想了解方法*和*它b / b
的风险，请转到 http://www.sommarskog.se/dynamic_sql.html 。
我喜欢你的第二种方法。
你会更具体地说明额外栏目（***主要
键***的一部分）"
你的意思是什么？主键的一部分？
Hugo !
Thank you very much for your help.
(I''ve noticed that such a simple question is not so simple to resolve with
basic SQL knowledge such as mine).
It is difficult to use first approach by entering everytime TBL_NAME (system
here).
Hi Andro,
I''m sorry, I don''t understand this part of your message.
Is there any easier way - by using table names as "parameters"?
There is, but it''s not recommended, because of the security
implications. If you want to read about the method *and* the risks it
has, go to http://www.sommarskog.se/dynamic_sql.html.
I like your second approach.
Would you be more specific about "extra column (***part of the primary
key***)"
What do you mean by "part of the primary key"?
如果你告诉我究竟你现在看起来如何
表，那会更容易回答。
我会使用制作用于说明这一点的示例。请考虑以下
（坏！）设计每日销售数据：
CREATE TABLE North_Sales
（SaleDate smalldatetime NOT NULL，
ProductNo int NOT NULL，
AmountSold int NOT NULL，
PRIMARY KEY（SaleDate，ProductNo），
FOREIGN KEY（ProductNo）REFERENCES产品
）
CREATE TABLE East_Sales
（SaleDate smalldatetime NOT NULL，
ProductNo int NOT NULL，
AmountSold int NOT NULL，
PRIMARY KEY（SaleDate，ProductNo），
FOREIGN KEY（ProductNo）REFERENCES产品
）
（南部和西部地区还有两个）
这四个表可以而且应该被这个表替换：
CREATE TABLE销售
（SaleDate smalldatetime NOT NULL，
Region char（5）NOT NULL，
ProductNo int NOT NULL，
AmountSold int NOT NULL，
PRIMARY KEY（SaleDate，Region，ProductNo），
FOREIGN KEY（ProductNo）REFERENCES产品，
检查（区域IN（''北''，''南''，''东''，''西''））
）
注意我是如何添加一个列Region，_AND *将此列添加到组成列的
列中主键。
（我还添加了一个检查约束 - 如果数据库中有Regions表，则应该用FOREIGN
KEY约束替换另外）。
-
Hugo Kornelis，SQL Server MVP
That would be easier to answer if you had told me exactly how yoour
tables currently look.
I''ll use a made-up example to illustrate this. Consider the following
(bad!) design for daily sales data:
CREATE TABLE North_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
CREATE TABLE East_Sales
(SaleDate smalldatetime NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products
)
(and two more for regions South and West)
These four tables can and should be replaced by this single table:
CREATE TABLE Sales
(SaleDate smalldatetime NOT NULL,
Region char(5) NOT NULL,
ProductNo int NOT NULL,
AmountSold int NOT NULL,
PRIMARY KEY (SaleDate, Region, ProductNo),
FOREIGN KEY (ProductNo) REFERENCES Products,
CHECK (Region IN (''North'', ''South'', ''East'', ''West''))
)
Notice how I added a column "Region", _AND* added this column to the
list of columns that make up the primary key.
(I also added a check constraint - this should be replaced by a FOREIGN
KEY constraint if there''s a Regions table in the database as well).
--
Hugo Kornelis, SQL Server MVP
这篇关于SELECT * FROM" multiple_tables" WHERE" field_（in_those_tables）_have_the_same_paricular_v alue" ？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
