天道酬勤,学无止境

tsql

Filtering duplicates in a table without a primary key

I would really appreciate your help with the following problem: I have a table (stock_header) which has 3 columns of interest catalogue_code purcha stock_ -------------- ------ ------ 1001 Box Box 1001 Box Box 1002 EA EA 1002 Set Set 1002 Kit Kit 1004 Set Set I would like to extract the information using the following rules: If a catalogue_code is a duplicate and all its purcha and stock_ attributes have the same value (e.g catalogue_code 1001), select any record. If a catalogue_code is a duplicate and some of its purcha and stock_ attributes contain different values (e.g catalogue_code 1002)

2021-09-25 21:38:00    分类:问答    sql   tsql   sql-server-2008

如何为数据库中的所有表创建视图?(How to create view for all tables in database?)

问题 我想从数据库中已经存在的所有表中创建视图,这是一项艰巨的任务,要一张一张地捕获表并使创建视图 XXXX 为 select * from Table_name 。我发现使用游标可以实现的东西,代码是: DECLARE @TableName sysname DECLARE @ColumnCount INT DECLARE @ColumnID INT DECLARE @SelectColumn NVARCHAR(500) DECLARE @sql NVARCHAR(max) = '' DECLARE QUERYINFO CURSOR FOR SELECT t.name AS TableName, ccount.ColumnCount, c.column_id AS ColumnID, CASE WHEN c.column_id <> ccount.ColumnCount THEN c.name + ', ' ELSE c.name END AS SelectColumn FROM sys.tables t INNER JOIN sys.columns c ON t.object_id=c.object_id INNER JOIN ( SELECT object_id,COUNT(*) AS ColumnCount FROM sys.columns GROUP BY object

2021-09-25 18:53:24    分类:技术分享    tsql   view

拆分字符串然后旋转结果(Splitting a string then pivoting result)

问题 如果从 .Net 应用程序传递一个字符串,如下所示 2023|F66451,1684|648521,1684|600271,2137|019592 我已经开始使用下面的方法解析出字符串,但我需要旋转从 Split(由 * 包围)函数返回的数据,以便插入 #tmpExceptions 表 DECLARE @ExceptionsList as nvarchar(MAX) SET @ExceptionsList = '2023|F66451,1684|648521,1684|600271,2137|019592' SET NOCOUNT ON; DECLARE @CurrentLineItem as nvarchar(255) CREATE TABLE #ParsePassOne ( LineItem nvarchar(255) ) CREATE TABLE #tmpExceptions ( AccountNumber int, ClaimNumber nvarchar(50) ) INSERT INTO #ParsePassOne SELECT value FROM Split( ',' ,@ExceptionsList) WHILE EXISTS(SELECT LineItem FROM #ParsePassOne) BEGIN SELECT TOP 1

2021-09-25 17:42:22    分类:技术分享    sql   sql-server   tsql   sql-server-2005   parsing

How to create view for all tables in database?

I want to make views from all tables already exist in database that’s hard task to catch the tables one by one and make create view XXXX as select * from Table_name .I find something that it is possible with cursor and the code is : DECLARE @TableName sysname DECLARE @ColumnCount INT DECLARE @ColumnID INT DECLARE @SelectColumn NVARCHAR(500) DECLARE @sql NVARCHAR(max) = '' DECLARE QUERYINFO CURSOR FOR SELECT t.name AS TableName, ccount.ColumnCount, c.column_id AS ColumnID, CASE WHEN c.column_id <> ccount.ColumnCount THEN c.name + ', ' ELSE c.name END AS SelectColumn FROM sys.tables t INNER JOIN

2021-09-25 05:07:59    分类:问答    tsql   view

SQL Server loop for changing multiple values of different users

I have the next table: Supposing these users are sort in descending order based on their inserted date. Now, what I want to do, is to change their sorting numbers in that way that for each user, the sorting number has to start from 1 up to the number of appearances of each user. The result should look something like: Can someone provide me some clues of how to do it in sql server ? Thanks.

2021-09-25 04:06:08    分类:问答    sql-server   database   tsql

Splitting a string then pivoting result

If have a string passed from a .Net application that looks like the below 2023|F66451,1684|648521,1684|600271,2137|019592 I have started to parse out the string using the method below but I need to Pivot the data returned from the Split ( surrounded by *'s) function in order to insert into the #tmpExceptions table DECLARE @ExceptionsList as nvarchar(MAX) SET @ExceptionsList = '2023|F66451,1684|648521,1684|600271,2137|019592' SET NOCOUNT ON; DECLARE @CurrentLineItem as nvarchar(255) CREATE TABLE #ParsePassOne ( LineItem nvarchar(255) ) CREATE TABLE #tmpExceptions ( AccountNumber int

2021-09-25 03:26:08    分类:问答    sql   sql-server   tsql   sql-server-2005   parsing

多选与单选语句(Multiple Select vs Single Select Statement)

问题 我只是想知道执行多个选择与使用单个选择之间有什么区别,有什么性能提升吗? 我看到有人这样做了,我觉得它有点失控,如下所示; select @a = cBranchName from mstores where nBranchCode = 1 select @b = cBranchAddress from mstores where nBranchCode = 1 select @c = dCreateDate from mstores where nBranchCode = 1 select @d = AssignedCompanyId from mstores where nBranchCode = 1 select @e = StoreFormat from mstores where nBranchCode = 1 对比 select @a = cBranchName ,@b = cBranchAddress ,@c = dCreateDate ,@d = AssignedCompanyId ,@e = StoreFormat from mstores where nBranchCode = 1 我还假设 5 个 select 对数据库进行了 5 次访问,我如何证明单个 select 语句比 5 个 select 语句更快, 即使仅通过查看它,我也已经认为单个

2021-09-25 02:20:26    分类:技术分享    sql   tsql   select

如何从枢轴聚合值?(how to aggregate values from a pivot?)

问题 我的查询返回一个如下所示的数据集: +-----------+--------+-----------+-------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+--------+-----------+---------+---------+------------+--------+---------+------------+---------+---------+------------+--------+ | CLIENT_ID | count1 | TestFreq1 | stdv1 | count2 | TestFreq2 | stdv2 | count3 | TestFreq3 | stdv3 | count4 | TestFreq4 | stdv4 | count5 | TestFreq5 | stdv5 | count6 | TestFreq6 | stdv6 |

2021-09-24 23:28:12    分类:技术分享    sql   sql-server   sql-server-2008   tsql

在预期条件的上下文中指定的非布尔类型的表达式,靠近 ')'(An expression of non-boolean type specified in a context where a condition is expected, near ')')

问题 如果 T_Referral 表上发生任何更新,我在触发器中有一个脚本来插入审计表,并且我正在使用 Decrypt 函数来解密其中一列中的数据。 我的触发器代码是: DECLARE @Sql_Insert nvarchar(max) SET @Sql_Insert = '' SET @Sql_Insert='INSERT INTO [Logg].AuditLogData(TableName, ColumnName, OldValue, OldValue_Decode, NewValue, NewValue_Decode, AuditSubCategoryID,[GuID]) select TableName, ColumnName, OldValue_Decode, case when ColumnName = ''BookingUserReferenceValue'' then utl.sfDecrypt(NewValue,0) Else NewValue end, NewValue_Decode, AuditSubCategoryID,[GuID] from #AuditLogData where ISNULL(OldValue,'') != ISNULL([NewValue],'')' exec utl.uspOpenOrCloseEncryptionKey 'open'

2021-09-24 23:19:03    分类:技术分享    sql   sql-server-2008   tsql

如何在 SQL 2000 中尝试解析?(How do I TryParse in SQL 2000?)

问题 我在旧的 SQL 2000 数据库中有一个存储过程,它采用格式为 varchar 的注释列并将其作为货币对象导出。 在设置此表结构时,假定这将是进入该字段的唯一数据。 当前过程的功能只是这样: SELECT CAST(dbo.member_category_assign.coment AS money) FROM dbo.member_category_assign WHERE member_id = @intMemberId AND dbo.member_category_assign.eff_date <= @dtmEndDate AND ( dbo.member_category_assign.term_date >= @dtmBeginDate OR dbo.member_category_assign.term_date Is Null ) 但是,现在正在将数据插入到此列中,该列无法解析为货币对象并导致程序崩溃。 我无法删除“坏”数据(因为这是第三方产品),但需要更新存储过程以测试货币可解析条目并返回该条目。 如何更新此过程,使其仅返回可解析为货币对象的值? 我是创建一个临时表并遍历每个项目,还是有更聪明的方法来做到这一点? 我坚持使用旧版 SQL 2000(6.0 版),因此遗憾的是无法使用任何较新的函数。 回答1 检查 IsNumeric 可能对您有所帮助 -

2021-09-24 22:30:31    分类:技术分享    tsql   parsing   sql-server-2000