如何使用SQL语句在VB.NET中使用min和max函数 [英] How to use min and max functions in VB.NET using SQL statement

问题描述

由于在线博客的帮助，我已经完成了部分工作。现在我想弄清楚如何在声明中添加最小值和最大值。



这就是我现在所拥有的：

I have part of what I am doing figured out, thanks to the help of an online blog. Now I am trying to figure out how to add a min and max value to the statement.

here is what I have now:

     Dim sqlcon As New SqlConnection("mysqlstatement")
        Dim myCmd As New SqlCommand("SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC", sqlcon)

我想要一个特定的列，这是我数据库中人员的年龄，并运行与已经完成的内容相关的最小和最大语句。 />


为了清楚起见改进这个问题 -



我设置了一个listview事件，其中包含以下字段：



表|记录| age_min | age_max |数据库|

sales 2000 18 76 sales2016

员工200 19 64名员工2016



我上面提供的代码加上这段代码：

I want to take a particular column which is the age of the people in my database and run a min and max statement tied into what is already done.

To improve on this question for clarity-

I set up a listview event that has the following fields:

table| records| age_min| age_max| database|
sales 2000 18 76 sales2016
employees 200 19 64 employees2016

the code I provided above plus this code:

sqlcon.Open()
       Dim myReader As SqlDataReader = myCmd.ExecuteReader

       While myReader.Read
           Dim newlistviewitem As New ListViewItem
           newlistviewitem.Text = myReader.GetString(0)
           newlistviewitem.SubItems.Add(myReader.GetInt32(1))
           ListView1.Items.Add(newlistviewitem)


       End While

       sqlcon.Close()

允许我生成有关表和记录的信息，这些信息只是每个表的行数。此信息显示在列表视图中。问题是我需要包含有关该列信息的年龄（最小/最大）的信息，但原始语句的编写方式我不能。我需要帮助，可能会重写以包含所有信息或添加到原始代码。



我尝试过：



我试图在声明中使用syscolumns，并将其标记为sc。

所以我尝试使用sc.name和sc设置它.id当我插入名字时，我得到的只是名字，这是年龄，而不是我想要获得的数值。

Allows me to generate information about the tables and records which is just the amount of rows per table. This information is displayed in a listview. Problem is I need to include information about the age (min/max) of that column information, but the way the original statement is written I can't. I am needing help with a possible rewrite to include all information or adding to the original code.

What I have tried:

I have tried to use the syscolumns in the statement, and labeled it sc.
so I tried to set it up using sc.name and sc.id which when I plugged in the name all I got was just that "the name", which was "age" and not the numerical values I was looking to get.

推荐答案

您不能在单个 SELECT 语句中将表记录与聚合在一个表的cloumn上混合。解决此问题的最简单方法是使用聚合值加入第二个查询，如下所示：

You can't mix table records with aggregates over a cloumn of the same table in a single SELECT statement. The simpliest way to solve this is to join a second query with the aggregated values like this:

SELECT t.[Age]
	 , mm.[MIN]
	 , mm.[MAX]
  FROM [dbo].[<Tablename>] t
      ,(SELECT MIN([Age]) AS [MIN]
	         , MAX([Age]) AS [MAX]
         FROM [dbo].[<Tablename>]
		WHERE ([Age] IS NOT NULL)) mm

想一想您的问题，这是我认为您搜索的解决方案：

1）创建一个存储过程，如

Thought a while about your problem and here's the solution I think you search for:
1) Create a Stored Procedure like

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetTableInfos

AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @Schema nvarchar(200);
	DECLARE @Table nvarchar(200);
	DECLARE @Column nvarchar(200);
	DECLARE @Cmd nvarchar(4000);
	DECLARE @TableInfos TABLE
			(
			 [SchemaName] nvarchar(200)
			,[TableName] nvarchar(200)
			,[RowCount] int
			,[MinAge] int
			,[MaxAge] int
			)

	DECLARE curLoop CURSOR LOCAL FAST_FORWARD FOR
		SELECT [TABLE_SCHEMA]
			 , [TABLE_NAME]
			 , [COLUMN_NAME]
		  FROM [INFORMATION_SCHEMA].[COLUMNS]
		 WHERE ([COLUMN_NAME] = 'Age')
		   AND ([DATA_TYPE] = 'int');

	OPEN curLoop;

	WHILE (1 = 1)
		BEGIN
			FETCH NEXT FROM curLoop INTO @Schema, @Table, @Column;

			IF (@@FETCH_STATUS <> 0)
				BREAK;

			SET @Cmd = 'SELECT ''' + @Schema + '''
							 , ''' + @Table + '''
							 , (SELECT COUNT(*) FROM [' + @Schema + '].[' + @Table + '])
							 , (SELECT MIN([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))
							 , (SELECT MAX([' + @Column + ']) FROM [' + @Schema + '].[' + @Table + '] WHERE ([' + @Column + '] IS NOT NULL))'

			 INSERT INTO @TableInfos
							 (
							  [SchemaName]
							 ,[TableName]
							 ,[RowCount]
							 ,[MinAge]
							 ,[MaxAge]
							 )
			 EXEC sp_executesql @Cmd;
		END

	CLOSE curLoop;
	DEALLOCATE curLoop;

	SELECT * FROM @TableInfos;
END
GO

您可以更改包含要聚合的年龄的列的名称，或者在结果集中添加更多信息列。



）更改SELECT语句以获取表格信息

You may alter the name of the column that contains the ages to aggregate or add more info columns to the result set.

2) Change your SELECT statement to get the table infos into

EXECUTE [dbo].[GetTableInfos]

这将从存储过程返回结果集。

希望这符合您的需求？



祝你好运！

This will return the result set from the stored procedure.
Hope this will fit your needs?

Good luck!

引用：

如何在VB.NET中使用SQL语句使用min和max函数

How to use min and max functions in VB.NET using SQL statement

尝试使用SQL教程网站



SQL MAX（）函数 [ ^ ]

SQL MIN（）函数 [ ^ ]

SQL教程 [ ^ ]

try with a SQL tutorial site

SQL MAX() Function[^]
SQL MIN() Function[^]
SQL Tutorial[^]

这篇关于如何使用SQL语句在VB.NET中使用min和max函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！