SQL函数获取指定表的（行）x（col）的值 [英] SQL function to get a value of (row)x(col) of a specified table

问题描述

您好，是否有SQL函数或语句来获取指定了行和列的表的值？谷歌搜索出来没什么可用的。

提前谢谢。

Hello, is there a SQL function or statement to get a value of a table with row and column specified? Googling came out with nothing usable.
Thanks in advance.

推荐答案

因为你没有指定任何列只有通用的SELECT语句可以为价值案例提供

As you don't specify any columns only a generic SELECT statement can be given for the value case

SELECT specific_column_name
FROM table_name
WHERE another_column_name operator value;

为此， another_column_name 中的值必须是唯一的。



另一种选择是使用行号。

这适用于MySQL（你不知道你使用的是哪个数据库）

For this to work, the values in the another_column_name must be unique.

Another option is to use a row number.
This would work in MySQL (you don't tell which database you use either)

SELECT specific_column_name 
from table_name
limit 10, 1; -- Get value from row 10

对于SQL Server，请参阅此链接 ROW_NUMBER（Transact-SQL） [ ^ ]

-----------sample table structure
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[area](
	[area_id] [bigint] NOT NULL,
	[area_code] [varchar](10) NULL,
	[area_name] [varchar](50) NOT NULL,
	[latitude] [float] NOT NULL,
	[longitude] [float] NOT NULL,
 CONSTRAINT [PK_area] PRIMARY KEY CLUSTERED 
(
	[area_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[area] ([area_id], [area_code], [area_name], [latitude], [longitude]) VALUES (1, N'EKM', N'Ernakulam', 9.97768, 76.29684)
INSERT [dbo].[area] ([area_id], [area_code], [area_name], [latitude], [longitude]) VALUES (2, N'TCR', N'Thrissur', 10.5243, 76.2125)

-------------------------getting (x,y) record from table--------------------------------
declare @inpXAxis INT
declare @inpYAxis INT
declare @reqColXAxis as varchar(100)
declare @reqColYAxis as varchar(100)
declare @sq1Query as varchar(MAX) 
declare @tableName as varchar(100)

---give table name here
SET @tableName='area'
---give X axis here , column no  
SET @inpXAxis=3
----give Y axis here, row no      
SET @inpYAxis=2

---get column name
select @reqColXAxis = column_name from information_schema.columns where table_name = @tableName 
and ordinal_position = @inpXAxis
----drop temp table if exists
	IF OBJECT_ID('tempdb..#temp1') IS NOT NULL
			BEGIN
				drop table #temp1
			END	

---fetch column having all rows along with a new index (myRow) into temp table temp1,also provide row no: as the second query
set @sq1Query = 'select IDENTITY(int,1,1) as myRow, ' + @reqColXAxis+' INTO #temp1  from '+@tableName +' ; SELECT '+@reqColXAxis +' FROM #temp1 WHERE myRow = '+CAST ( @inpYAxis as varchar)
exec(@sq1Query)

good luck ;-)

你好，



检查这个......

Hi,

Check this...

Select * from yourTable

以上将根据您的主题行提供输出。





希望这会对你有所帮助。



干杯

above will give output as per your subject line.


Hope this will help you.

Cheers

这篇关于SQL函数获取指定表的（行）x（col）的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！