天道酬勤,学无止境

sql-server

如何在包含连续 int 值的列中获得第一个间隙(How to get first gap in a column containing sequential int values)

问题 我在 SQL Server 数据库表中有一个列,有一列包含整数值,我想获得最大值,除非系列中有间隙,然后我想获得第一个缺失值。 例如从下表中,我希望获得价值 4 1 2 null 3 上面很简单,但是如果表包含如下数据,我怎么能找到我缺少的 id,在这种情况下它将是 8 1 3 2 4 null 5 7 6 null 10 9 /////////////////// 编辑: 我最初实现了@podiluska 的答案并打算接受它,但是当我用超过 10K 行的表进行测试时,它很慢,所以我采用了@vmvadivel 的答案并对其稍作更改,如下所示,它的工作速度非常快,但仍然不是我想要的 100%: SELECT Number FROM ( SELECT ROW_NUMBER() OVER(ORDER BY iValue) AS Number FROM tblNumbers WHERE iValue IS NOT NULL ) temp WHERE Number NOT IN ( SELECT iValue FROM tblNumbers WHERE iValue IS NOT NULL ) 这很有效,但是如果所有iValue字段都为空,那么我什么也得不到,但我希望它返回 1,因为它将是系列中的第一个值,显然我可以在 C# 代码中处理这个问题,但我是如果上面的查询没有返回一行

2021-10-27 04:06:17    分类:技术分享    sql   sql-server   sql-server-2008-r2

无法加载 DLL Microsoft.Data.SqlClient.x86.dll(来自 HRESULT 的异常:0x800707E)(Unable to load DLL Microsoft.Data.SqlClient.x86.dll (Exception From HRESULT: 0x800707E))

问题 描述错误 我正在尝试使用 DevExpress winform 报告向导编辑报告上的连接字符串 当我单击下一步按钮时出现此错误 当我尝试安装 Microsoft.Data.SqlClient 1.1.3 的最新稳定版本时,出现两个不同的错误。 01-使用Windows身份验证 02-带服务器认证 注意:运行应用程序时,所有报告都可以正常工作,没有任何问题。 技术细节 Microsoft.Data.SqlClient version: 2.0.0-preview4.20142.4 Microsoft.Data.SqlClient.SNI version: 2.0.0-preview1.20141.10 System.Configuration.ConfigurationManager version: 5.0.0-preview.4.20251.6 .NET target: Framework 4.7.2 SQL Server version: SQL Server 2019 Operating system: Windows 10 Version 2004 DevExpress 20.1.3 Microsoft Visual Studio Enterprise 2019 Preview Version 16.7.0 Preview 2.0 更新: 好像和 DevExpress

2021-10-27 03:56:13    分类:技术分享    c#   sql-server   winforms   devexpress

.NET Core Web API Application cannot connect to SQL server running on Docker

Using MAC. Error (provider: TCP Provider, error: 40 - Could not open a connection to SQL Server). Connection string "Data Source=localhost,1433;Initial Catalog=MyDatabase;Integrated Security=True;User id=sa;Password=MyPassword;MultipleActiveResultSets=true" Using .NET Core 3.1. The Web API is running in a container The database is running on docker container. ports mapped 1433:1433 Able to query the database from Azure Data Studio. But while trying to connect from .net.core application it giving me that error above. using System.Data.SqlClient; SqlConnection connection = new SqlConnection(

2021-10-27 03:50:56    分类:问答    c#   sql-server   macos   docker   asp.net-core-3.1

在不同帐户下使用 T-SQL 运行 SSIS 包(Run SSIS package using T-SQL under different account)

问题 我通常使用 Sql 代理作业和代理用户运行 SSIS 包,如下所述:https://www.sqlservercentral.com/articles/run-an-ssis-package-under-a-different-account 我现在需要使用 T-SQL 使用相同的代理用户运行相同的包。 我一直在尝试使用[catalog].[create_execution]和[catalog].[start_execution]过程来执行此操作,但似乎没有指定用户的方法。 如何以不同的用户身份执行包? 我最好的办法是使用 T-SQL 来执行配置为使用代理用户的 SQL 代理作业吗? 回答1 我不知道代理实际上是如何让代理用户工作的——尤其是在 SSIS 包方面。 在SSMS中的“正常”sql会话中,如果我想以另一个用户身份运行查询 EXECUTE AS USER = 'TurgidWizard'; SELECT USER_NAME() AS WhoAmI; REVERT; 该代码将允许我模拟您,直到我按下REVERT调用。 但是,如果您交换对 create_execution/start_execution 的调用,您将遇到与尝试使用本地 sql server 用户在使用 SSISDB 中的方法时遇到的相同问题 - 它不起作用。 SSISDB 中的方法在开始之前都会运行检查

2021-10-27 03:49:23    分类:技术分享    sql-server   tsql   ssis

Passing huge amounts of data as an hexadecimal (0x123AB...) parameter of a clr stored procedure in sql server

I post this question has followup of This question, since the thread is not recieving more answers. I'm trying to understand if it is possible to pass as a parameter of a CLR stored procedure a large amount of data as "0x5352532F...". This is to avoid to send the data directly to the CLR stored procedure, instead of sending ti to a temporary DB field and from there passing it as varbinary(max) parmeter to the CLR stored procedure. I have a triple question: 1) is it possible, if yes how? Let's say i want to pass a pdf file to the CLR stored procedure (not the path, the full bits that make up

2021-10-27 03:44:26    分类:问答    sql-server   hex   sqlclr

当我的 where 语句中有 NOT NULL 子句时,为什么我的查询返回空值(Why is my query returning null values when I have a NOT NULL clause in my where statement)

问题 我正在尝试获取 DISTINCT 和 NOT NULL 值,但这似乎不起作用: SELECT DISTINCT ITEM FROM TABLE WHERE ITEM IS NOT NULL 这会返回 DISTINCT 值,但也会返回 NULL VALUES。 样本: ITEM a a b b c c NULL 输出: a b c NULL 回答1 尝试这个。 “IS NOT NULL”和“IS NULL”不适用于空白值。 如果列允许空白,您可以同时覆盖两者。 SELECT DISTINCT item FROM table WHERE ISNULL(item,'') <> '' 回答2 此语句将返回abc除非您有字符串NULL 而不是列中的symbol : SELECT DISTINCT ITEM FROM TABLE WHERE ITEM IS NOT NULL 要测试这一点,请尝试这些 --check for SELECT DISTINCT ITEM FROM TABLE WHERE ITEM = 'NULL' --modify original query SELECT DISTINCT ITEM FROM TABLE WHERE NULLIF(ITEM, 'NULL') IS NOT NULL 回答3 检查 Item 是否为 Varchar 类型,并且您在其中存储值

2021-10-27 03:40:49    分类:技术分享    sql-server

如何明智地按一组增加列(How to increment column by one group wise)

问题 我有一个名为productLocation的表,其数据结构如下,SQLFiddle +--------------+-------------+-----------+ | FkLocationId | FkProductId | SortValue | +--------------+-------------+-----------+ | 1 | 100 | 0 | +--------------+-------------+-----------+ | 1 | 101 | 0 | +--------------+-------------+-----------+ | 1 | 102 | 0 | +--------------+-------------+-----------+ | 1 | 103 | 2 | +--------------+-------------+-----------+ | 1 | 104 | 1 | +--------------+-------------+-----------+ | 1 | 105 | 3 | +--------------+-------------+-----------+ | 2 | 100 | 0 | +--------------+-------------+-----------+ | 2 | 101 | 0

2021-10-27 03:39:04    分类:技术分享    sql   sql-server   sql-update   sql-order-by

Pattern for normalized tables

Im fairly new to SQL, so difficult for me to know whats good, bad, better or best design. I have a SQL 2008 database which I'm using along with Entity Framework 4.3. I'm trying to normalize my database. In my design I have 2 tables Applications and AcceptedApplications. AcceptedApplications is simply an extension of the Applications table, for, you guessed it, AcceptedApplications. This simply contains further information that is not pertinent to rejected applications. There is a foreign key relationship between Application and AcceptedApplications so an Application must exist before an

2021-10-27 03:27:32    分类:问答    sql   sql-server   normalize

在完成某项交易时确定员工的职称(determining the staff's title when a certain transaction is done)

问题 我有一个包含相关数据和用户名的交易表。 此外,还有一个表按日期存储员工的职称历史。 然而,标题历史表没有每一天的日志。 这是我的交易表的一个例子: username tran_date amount -------- ---------- ------ anderson 16/02/2019 1200 julie 17/02/2019 54000 williams 20/02/2019 32650 和员工的职称日志表: username date title -------- ---- ------ anderson 01/01/2019 assistant anderson 06/04/2019 chief williams 07/03/2019 chief julie 15/02/2019 technician julie 27/05/2019 chief technician 我想在最近的一天加入工作人员的头衔。 如; username tran_date amount title -------- ---------- ------ ------ anderson 16/02/2019 1200 assistant julie 17/02/2019 54000 technician williams 20/02/2019 32650 chief

2021-10-27 03:27:19    分类:技术分享    sql   sql-server

Chart for Benchmark data isn't calculated properly

I posed this question a few days ago but it has diverged in the comments enough that I think it's worthy of asking many of the points in the comments in a new question. I apologize for the length of this question. I hope that it isn't terribly unclear. I'm working on creating a chart using ggplot2 in a R Sweave file used to track benchmarks for software. I'm looking specifically at run times for the benchmark and either want to use % Deviation or deviation by minutes from the earliest test run that we have data for. The reason I say earliest and not the first is because in earlier versions of

2021-10-27 03:27:06    分类:问答    sql-server   r   charts   ggplot2