天道酬勤,学无止境

tsql

在 T-SQL 中将文本格式的日期转换为日期时间格式(Convert date in text format to datetime format in T-SQL)

问题 我有一个客户端提供的文件加载到我们的 SQL Server 数据库中。 该文件包含基于文本的日期值,即 (05102010),我需要从 db 列中读取它们并将它们转换为正常的日期时间值 = '2010-05-10 00:00:00.000' 作为清理的一部分过程。 任何指导将不胜感激。 回答1 一种方法是使用 CONVERT(datetime,RIGHT(Column,4) + left(Column,4)) 例子 declare @s char(8) select @s = '05102010' select CONVERT(datetime,RIGHT(@s,4) + left(@s,4)) 回答2 尝试: SELECT CONVERT(datetime, RIGHT(YourColumn,4) +LEFT(YourColumn,4) ) AS ProperDateTime FROM... 工作示例: DECLARE @YourTable table (StringDate char(8)) INSERT @YourTable VALUES ('05102010') INSERT @YourTable VALUES ('03182010') SELECT CONVERT(datetime, RIGHT(StringDate,4) +LEFT(StringDate,4) )

2022-01-25 05:28:01    分类:技术分享    sql-server   tsql   datetime   date-parsing

SQL - 从条件开始添加顺序计数器列,具有分组和非递增 ID(SQL - add sequential counter column starting at condition, with group by and non incrementing IDs)

问题 这个问题的更棘手的版本:​​SQL - 从条件开始添加顺序计数器列 我有一张桌子: create table t (id int, category varchar(4), time1 datetime, market varchar(100)); insert into t (id,category,time1, market) values (1000, 'cat1', '20120618 10:14:09 AM', 'mkt1'), (1002, 'cat1', '20120618 10:14:11 AM', 'mkt2'), (1001, 'cat2', '20120618 10:14:14 AM', 'mkt1'), (1004, 'cat2', '20120618 10:14:15 AM', 'special'), (1003, 'cat2', '20120618 10:14:17 AM', 'mkt2'), (1006, 'cat1', '20120618 10:14:54 AM', 'special'), (1007, 'cat3', '20120618 10:14:55 AM', 'special'), (1054, 'cat1', '20120618 10:14:58 AM', 'mkt1'), (1023, 'cat1', '20120618 10:14

2022-01-25 04:53:51    分类:技术分享    tsql

父 ID 不在列表中时的递归 CTE(Recursive CTE while Parent Id not in a List)

问题 我有以下嵌套集 这导致了这棵树 1 - |---- 2 - | |---- 4 - | |---- 7 | |---- 8 |----10 - |---- 9 3 - |---- 5 |---- 6 13- |---- 11 |---- 12 我有一个产品列表 SELECT Id, Name ... FROM Products 与类别的多对多关系。 所有类别都可以有促销。 好的,现在的问题。 假设我在类别 7、8、6 中有一个 ProductX。 以及类别 1、2、3 中的促销活动。 我需要找到最近的父母,每个类别都有促销活动,或者直到没有更多父母为止。 最终结果应该是 CategoryId PromotionPrice 2 price... 3 price... 我有的 WITH Promotions (CategoryId, PromotionPrice) { SELECT CategoryId, PromotionPrice FROM Promotions } SELECT CategoryId, PromotionPrice FROM NestedSet s1 LEFT JOIN NestedSet s2 ON s1.ParentId = s2.Id LEFT JOIN Promotions p ON s1.CategoryId = p.CategoryId 然后获得

2022-01-25 04:53:28    分类:技术分享    sql-server   tsql   nested-sets   recursive-cte

在 T-SQL 上计算项目频率的问题(Problem counting item frequency on T-SQL)

问题 我正在尝试计算表格不同字段上从 1 到 100 的数字的频率。 假设我有包含以下数据的“结果”表: LottoId Winner Second Third --------- --------- --------- --------- 1 1 2 3 2 1 2 3 我希望能够获得每个数字的频率。 为此,我使用以下代码: --Creating numbers temp table CREATE TABLE #Numbers( Number int) --Inserting the numbers into the temp table declare @counter int set @counter = 0 while @counter < 100 begin set @counter = @counter + 1 INSERT INTO #Numbers(Number) VALUES(@counter) end -- SELECT #Numbers.Number, Count(Results.Winner) as Winner,Count(Results.Second) as Second, Count(Results.Third) as Third FROM #Numbers LEFT JOIN Results ON #Numbers.Number = Results

2022-01-25 04:38:49    分类:技术分享    tsql   sql-server-2008   count   frequency

Check Constraint to Confirm Exactly One is not NULL

I have a table with the columns CompaniesId and HotelsId. I want to write a check constraint that confirms that one of these columns is null and other one is not. I tried the following expression: (CompaniesId is null) <> (HotelsId is null) When I attempt to save this in the Check Constraints dialog box in SSMS, I get the helpful message: Error validating constraint 'CK_CostingLineItemVendors'. Could someone help me with my syntax? EDIT: According to the article IS [NOT] NULL (Transact-SQL), the result of IS NULL or IS NOT NULL is Boolean. That's why I would assume I could compare these

2022-01-25 04:25:17    分类:问答    sql-server   tsql   check-constraints

Export SQL Server database to XML Optimization

I have SQL Server database for articles. Articale table: CREATE TABLE [dbo].[T_Articale] ( [id] [int] IDENTITY(1,1) NOT NULL, [Title] [nvarchar](100) NULL, [Text] [nvarchar](max) NULL, [LocationID] [int] NULL, [TribeID] [int] NULL, [ArticaleText] [nvarchar](max) NULL, [ArticaleDate] [date] NULL, [SearchID] [int] NULL, [ClassificationID] [int] NULL, [CountryID] [int] NULL, [isLocal] [int] NULL, [Note] [nvarchar](150) NULL, CONSTRAINT [PK_T_Articale] PRIMARY KEY CLUSTERED ) Where ArticaleText is the content of the article with formatting (HTML tags) Files table: CREATE TABLE [dbo].[T_Articale

2022-01-25 04:22:25    分类:问答    c#   sql-server   xml   sql-server-2008   tsql

Get the first joined result based on business logic

My table T1 looks like this: ID VALUE ---------- 1 1 2 32 3 6 4 42 5 8 6 62 7 43 8 34 My second T2 looks like this: ID DEFINITION --------------- 1 A|B|C 2 er|All|All|B 3 All|All|All 4 All|bela|All 5 All|All|All|G 6 A|All|All 7 All|B|All 8 Av|All|All|G The business logic behind that table is that the most specific DEFINITION should be applied and the respective value should be retrieved when passing a string. 'All' can be interpreted as '%'. The '|' is just a separator which defines how many values you are actually passing. Each of the strings could be anything... E.g. if i have a definition

2022-01-25 04:19:01    分类:问答    sql   sql-server   database   tsql

HierarchyID:获取父母列表的所有后代 - 不工作(HierarchyID: Get all descendants for a list of parents — Not Working)

问题 我正在处理这个线程: HierarchyID: HierarchyID: Get all descendants for a list of parents 我有一个使用 HierarchyID 的表,并且我需要一个查询,它可以在一个集合中为我提供指定父级的所有后代。 这是我的表格,已填充: DECLARE @Ph TABLE (ProductHierarchyNode HIERARCHYID, ProductHierarchyId INT) INSERT INTO @Ph (ProductHierarchyNode, ProductHierarchyId) VALUES (hierarchyid::Parse('/1/'), 1), (hierarchyid::Parse('/1/1/'), 2), (hierarchyid::Parse('/1/1/2/'), 3), (hierarchyid::Parse('/1/1/2/1/'), 4) 此查询非常适用于 SINGLE id: 4。它返回了该项目,以及它的所有后代。 SELECT * FROM @Ph WHERE (SELECT ProductHierarchyNode FROM @Ph WHERE ProductHierarchyId = 4).IsDescendantOf(ProductHierarchyNode)

2022-01-25 03:15:45    分类:技术分享    sql   sql-server   tsql

如何动态透视每个月的数据?(How to pivot data for every month dynamically?)

问题 我在查询一家公司的加盟店或自营店的订单数。 它们被放入日期差异类别。 这是查询和结果作为解释: SELECT [Order-Delivery Difference], [Type], COUNT(DISTINCT OrderId) AS [Number of Orders] FROM (SELECT DDIFF AS [Order-Delivery Difference], Franchise AS [Type], OrderId, [Net Value] FROM joined GROUP BY DDIFF, FRANCHISE, OrderId, [Net Value] ) AS subquery GROUP BY [Order-Delivery Difference], [Type] HAVING SUM([Net Value]) > 0 ORDER BY [Order-Delivery Difference] ; 我的表中有一个Date类型的列,我们称之为DateColumn 。 理论上,我可以在任何给定的特定时间过滤这个结果。 ... (SELECT DDIFF AS [Order-Delivery Difference], Franchise AS [Type], OrderId, [Net value] FROM joined WHERE DATEPART

2022-01-25 02:57:06    分类:技术分享    sql   sql-server   tsql

Create calculated value based on calculated value inside previous row

I'm trying to find a way to apply monthly percentage changes to forecast pricing. I set my problem up in excel to make it a bit more clear. I'm using SQL Server 2017. We'll say all months before 9/1/18 are historical and 9/1/18 and beyond are forecasts. I need to calculate the forecast price (shaded in yellow on the sample data) using... Forecast Price = (Previous Row Forecast Price * Pct Change) + Previous Row Forecast Price Just to be clear, the yellow shaded prices do not exist in my data yet. That is what I am trying to have my query calculate. Since this is monthly percentage change, each

2022-01-25 02:01:02    分类:问答    sql   sql-server   tsql   forecast