问题描述
如何从表中选择数据并将数据类型包含在值中?例如,我有一个名为EMPLOYEE的表,其中包含fname,lastname,phone等.如果我这样做:
How could I select data from a table and include the data type with the values?For example, I have table called EMPLOYEE with fname, lastname, phone, etc.If I do:
SELECT * FROM EMPLOYEE
我得到所有专栏.如果我这样做:
I get all the columns. If I do:
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'EMPLOYEE'
ORDER BY ORDINAL_POSITION
我获得了所有列名及其数据类型.
I get all the column names and their data types.
我需要将这两者结合起来,以便我将显示的值与数据类型一起显示.
I need to combine these two so I have the values displayed along with the data type.
我是SQL的新手,但是我已经在网上搜索了一些内容,并尝试了各种JOIN和UNION语句,但是我可能只是在输入错误的信息.
I'm new to SQL but I have searched on the web some and tried various JOIN and UNION statements, but I could just be entering them wrong.
推荐答案
您可以SELECT EMPLOYEE.COL1, INFO.DATA_TYPE FROM EMPLOYEE, INFORMATION_SCHEMA.COLUMNS INFO WHERE INFO.TABLE_NAME='EMPLOYEE' AND COLUMN_NAME='COL1'
但是要选择更多字段,您必须再次添加另一个别名不同的INFORMATION_SCHEMA.COLUMNS实例.
You could SELECT EMPLOYEE.COL1, INFO.DATA_TYPE FROM EMPLOYEE, INFORMATION_SCHEMA.COLUMNS INFO WHERE INFO.TABLE_NAME='EMPLOYEE' AND COLUMN_NAME='COL1'
But to select more fields, you'd have to add another instance of INFORMATION_SCHEMA.COLUMNS again with a different alias.
但是出于多种原因,您不应该这样做.
But you should not do this for many reasons.
从技术上讲:CROSS JOIN(在FROM中的表仅用逗号列出)在数据库服务器上压力很大. FROM T1, T2
将T1的所有行与T2的所有行配对,并检查结果行.如果T1有n行,T2有m,则结果有n * m行.
Technically: CROSS JOIN (the tables in FROM listed simply with a comma) is much strain on the database server. FROM T1, T2
pairs up all rows of T1 with all rows of T2 and examines the result rows. If T1 has n rows and T2 has m then the result has n*m rows.
逻辑1:您不需要返回此信息.当您输入查询(SELECT)时,返回的架构是已知的.查询确定结果列是什么数据类型.逻辑2:由于每一行在列中具有相同的数据类型,因此不需要在每一行中返回类型信息.例如.您的示例返回1000名员工的数据将不必要地在每一行上传输,即AGE字段是INTEGER,NAME是VARCHAR,依此类推...
Logically 1: You should not need this information to be returned. When you enter a query (a SELECT) the the returned schema is known; the query determines what datatypes the result columns are.Logically 2: Since every row has the same datatypes in the columns, you do not need the type information to be returned in every row. E.g. your example returning data of 1000 employees would unnecessarily transfer on every line that the AGE field is INTEGER, the NAME is VARCHAR and so on...
如果您不知道结果的模式(例如,由于生成查询等),则上述解决方案将无济于事.
If you somehow would not know the schema of the result (e.g. because of generated queries or the like) then the above solution would not help you.
这篇关于SQL Select包括数据类型和数据值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!