这是一个简单的表格
用户身份 | 角色ID |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
1 | 4 |
2 | 1 |
2 | 2 |
2 | 3 |
2 | 4 |
3 | 1 |
3 | 2 |
3 | 3 |
我试图在不同用户的 RoleId 列中找到不同的组合。在此示例中,我可以看到有 3 个用户,但有两个不同的角色组合。
用户 1 和 2 具有 [1,2,3,4] 角色,用户 3 具有 [1,2,3] 角色。我无法理解 T-Sql 中是否可以使用 PARTITION 函数返回这些不同的组合。
这是一个简单的表格
用户身份 | 角色ID |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
1 | 4 |
2 | 1 |
2 | 2 |
2 | 3 |
2 | 4 |
3 | 1 |
3 | 2 |
3 | 3 |
我试图在不同用户的 RoleId 列中找到不同的组合。在此示例中,我可以看到有 3 个用户,但有两个不同的角色组合。
用户 1 和 2 具有 [1,2,3,4] 角色,用户 3 具有 [1,2,3] 角色。我无法理解 T-Sql 中是否可以使用 PARTITION 函数返回这些不同的组合。
我最近读完 《SQL Server 2008 中的计划缓存》 ,我感到很困惑。看起来,除了完全刷新计划缓存或明确要求重新编译存储过程之外,从 SQL Server 2008 开始,存储过程的重新编译都是在语句级别而不是存储过程级别完成的。
那么,除了显式刷新缓存或要求重新编译(例如WITH RECOMPILE
)之外,什么可以在 SQL Server 2019 中重新编译完整的存储过程,而不仅仅是重新编译单个语句呢?
举一个我感到困惑的例子,请考虑以下过程。
CREATE PROCEDURE FOO AS
BEGIN
SELECT * INTO #temp1 FROM table1
INSERT BAR1 SELECT * FROM #temp1
INSERT BAR2 SELECT * FROM #temp1
END
我可以想到很多可能导致SELECT * INTO #temp1 FROM table1
重新编译的事情,但是如果没有下一行也重新编译,那么重新编译会很奇怪。这让我觉得 SQL Server 中一定有一些东西会导致整个存储过程重新编译。
我想让旧的后续查询在并行运行时免受数据竞争的影响。该查询使用特定条件检查表中是否存在一行,如果不存在该行,则会插入包含新数据的新行。旧查询粘贴如下:
BEGIN
DECLARE @txtPer VARCHAR(MAX) = @nro;
DECLARE @txtCmin VARCHAR(MAX) = @min;
DECLARE @txtCmax VARCHAR(MAX) = @max;
IF NOT EXISTS (SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
BEGIN
----Time of check is not time of use
----Someone could possibly do another insert before this == data race
INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
SELECT MAX(SNDID)+10,1,1,(@txtPer + @txtCmin + ' ' + @txtCmax),0,0,' ',getdate(),getdate() FROM SEND
END
END
我想出了一个稍微干净一点的新版本,并使用独占表锁:
BEGIN TRANSACTION;
DECLARE @txtPer VARCHAR(MAX) = @nro;
DECLARE @txtCmin VARCHAR(MAX) = @min;
DECLARE @txtCmax VARCHAR(MAX) = @max;
DECLARE @IdMax INT
--Get MAX and simultaneously acquire lock for the table to prevent modifications during this transaction?
SELECT @IdMax=MAX(SNDID) FROM SEND WITH(TABLOCKX)
IF NOT EXISTS(SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
BEGIN
INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
VALUES((@IdMax + 10),1,1,(@txtPer + @txtCmin + ' ' + @txtCmax),0,0,' ',getdate(),getdate())
END
COMMIT; --writes potential change and releases table lock?
我的理解是否正确,即在操作期间获取的表锁SELECT MAX()
(可能还有同一事务中的其他表锁)一直保留到整个事务完成该COMMIT
语句?DB 是旧的 MS SQL Server 2005。
我想创建交易,它将:
想要创建带有参数的脚本,所以我将使用动态 SQL。主意:
/* OPS parameters */
DECLARE @schemaName sysname = 'dbo';
DECLARE @tableName sysname = 'TABLE';
DECLARE @partition INT = 90;
/* DEV parameters */
DECLARE @tableNameSRP sysname = CONCAT(@tableName, '_SRP');
DECLARE @tableNameWithSchema sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableName));
DECLARE @tableNameWithSchemaSRP sysname = CONCAT(QUOTENAME(@schemaName), '.', QUOTENAME(@tableNameSRP));
DECLARE @isCtReEnabled BIT = 0;
DECLARE @isDebug BIT = 1;
SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
SET XACT_ABORT ON;
BEGIN TRAN;
BEGIN TRY
IF EXISTS (
SELECT
1
FROM sys.change_tracking_tables
WHERE object_id = OBJECT_ID(@tableNameWithSchema)
)
BEGIN
SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' DISABLE CHANGE_TRACKING;';
IF (@isDebug = 0)
BEGIN
EXEC sp_executesql @stmt = @statement;
END;
IF (@isDebug = 1)
BEGIN
RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
END;
SET @isCtReEnabled = 1;
END;
SET @statement
= N'TRUNCATE TABLE ' + @tableNameWithSchema + N' WITH (PARTITIONS (' + CAST(@partition AS NVARCHAR(5)) + N'))
ALTER TABLE ' + @tableNameWithSchemaSRP + N' SWITCH PARTITION ' + CAST(@partition AS NVARCHAR(5)) + N' TO ' + @tableNameWithSchema + N' PARTITION '
+ CAST(@partition AS NVARCHAR(5));
IF (@isDebug = 0)
BEGIN
EXEC sp_executesql @stmt = @statement;
END;
IF (@isDebug = 1)
BEGIN
RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
END;
IF (@isCtReEnabled = 1)
BEGIN
SET @statement = N'ALTER TABLE ' + @tableNameWithSchema + N' ENABLE CHANGE_TRACKING;';
IF (@isDebug = 0)
BEGIN
EXEC sp_executesql @stmt = @statement;
END;
IF (@isDebug = 1)
BEGIN
RAISERROR('[INFO] SQL: %s', 0, 1, @statement) WITH NOWAIT;
END;
END;
COMMIT;
END TRY
BEGIN CATCH
SET @errorMessage = ERROR_MESSAGE();
RAISERROR('ERROR MESSAGE: %s', 0, 1, @errorMessage) WITH NOWAIT;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
我的问题:
我尝试使用数字表按月间隔计算 2 个不同的日期列:
数字表:
编号 |
---|
0 |
1 |
2 |
3 |
原始表(示例):
性别 | 订阅_日期 | 取消日期 |
---|---|---|
男性 | 2023-01-01 | 2023-05-01 |
男性 | 2023-01-05 | 2023-01-08 |
男性 | 2023-01-05 | 2023-03-09 |
男性 | 2023-02-01 | 2023-04-08 |
女性 | 2023-01-05 | 2023-04-08 |
女性 | 2023-01-07 | 无效的 |
最终输出应该是这样的:
报告月 | 性别 | 订阅 | 取消 |
---|---|---|---|
2023-01-01 | 男性 | 3 | 1 |
2023-01-01 | 女性 | 2 | 0 |
2023-02-01 | 男性 | 1 | 0 |
2023-02-01 | 女性 | 0 | 0 |
2023-03-01 | 男性 | 0 | 1 |
2023-03-01 | 女性 | 0 | 0 |
2023-04-01 | 男性 | 0 | 1 |
2023-04-01 | 女性 | 0 | 1 |
2023-05-01 | 男性 | 0 | 0 |
2023-05-01 | 女性 | 1 | 0 |
第一次尝试:
-- first day of the current month, one year ago
DECLARE @StartDate date = DATEADD(year,-1,DATEADD(month,datediff(month,0,getdate()),0))
-- Start from StartDate, showing results for Subscriptions for each month
SELECT
@StartDate AS StartDate, Numbers.Num, DATEADD(month, Numbers.Num, @StartDate) AS ReportMonth
,S.Gender
,COUNT(S.SubscriptionDate) AS Subscriptions
FROM Numbers
JOIN
OriginatingTable AS S ON DATEADD(month, Numbers.Num, @StartDate) = DATEADD(MONTH, DATEDIFF(MONTH, 0, S.SubscriptionDate), 0)
WHERE (Numbers.Num <= DATEDIFF(month, @StartDate, GETDATE()))
GROUP BY Numbers.Num, DATEADD(month, Numbers.Num, @StartDate), DATEADD(MONTH, DATEDIFF(MONTH, 0, S.Subscription_Date), 0), S.Gender
ORDER BY Numbers.Num
这可行,但我无法整合第二列“取消”。
感谢您的帮助,
拉尔夫.
我有一个查询,通过使用枚举 的分组(分区)中的行来返回Price
a 的最低值,按升序排序(MIN 函数在将列添加到 SELECT 后返回多行)。ProductVariation
ROW_NUMBER()
ProductId
Price
SELECT
pv.Id AS VariationId,
pv.ProductId,
pv.[Description],
pv.Price,
pv.Stock,
ROW_NUMBER() OVER (PARTITION BY pv.ProductId ORDER BY pv.Price, pv.Stock DESC) AS SortId
FROM ProductVariation pv
INNER JOIN Product p ON p.Id = pv.ProductId
WHERE p.CompanyId = @CompanyId
一种Product
可以有多种变体。下面是我的ProductVariation
桌子。
该查询运行完美并返回以下结果:
变体ID | 产品编号 | 描述 | 价格 | 库存 | 排序编号 |
---|---|---|---|---|---|
149 | 97 | 黄色水果 | 139.99 | 0.00 | 1 |
150 | 97 | 红色水果 | 139.99 | 5.00 | 2 |
151 | 97 | 青苹果 | 139.99 | 20:00 | 3 |
152 | 98 | 桃芒果 | 219.99 | 0.00 | 1 |
153 | 99 | 疯狂柠檬 | 169.99 | 1.00 | 1 |
154 | 99 | 北极冰 | 169.99 | 3.00 | 2 |
155 | 99 | 天堂冲床 | 169.99 | 3.00 | 3 |
然而,现在我们有另一个表,ProductValueConsultor
顾问可以在ProductVariation
其中单独更新价格。
在里面ProductValueConsultor
我们有一行将ProductVariation
id 149 的价格更新为 149.00 美元。
商店ID | 价格 | 产品变体 ID |
---|---|---|
13 | 149.90 | 149 |
所以现在,我更新了查询并添加了OUTER APPLY
运算符来识别ProductValueConsultor
表中可能的值并显示更新的价格。
SELECT
pv.Id AS VariationId,
pv.ProductId,
pv.[Description],
ISNULL(pValue.Price, pv.Price) Price,
pv.Stock,
ROW_NUMBER() OVER (PARTITION BY pv.ProductId ORDER BY pv.Price) AS SortId
FROM ProductVariation pv
OUTER APPLY (
SELECT TOP 1 * FROM ProductValueConsultor pvc
WHERE pv.Id = pvc.ProductVariationId AND pvc.StoreId = @StoreId
) pValue
INNER JOIN Product p ON p.Id = pv.ProductId
WHERE p.CompanyId = @CompanyId
操作OUTER APPLY
员工作完美,显示了 id 149 的更新价格。ProductVariation
但是,该ROW_NUMBER()
函数出现故障,显示原始价格的排序顺序。
变体ID | 产品编号 | 描述 | 价格 | 库存 | 排序编号 |
---|---|---|---|---|---|
149 | 97 | 黄色水果 | 149.99 | 0.00 | 1 |
150 | 97 | 红色水果 | 139.99 | 5.00 | 2 |
151 | 97 | 青苹果 | 139.99 | 20:00 | 3 |
152 | 98 | 桃芒果 | 219.99 | 0.00 | 1 |
153 | 99 | 疯狂柠檬 | 169.99 | 1.00 | 1 |
154 | 99 | 北极冰 | 169.99 | 3.00 | 2 |
155 | 99 | 天堂冲床 | 169.99 | 3.00 | 3 |
如何修复此查询以将ROW_NUMBER()
函数应用于更新的值OUTER APPLY
?
我有两个查询,它们大多是同一个 where 表达式。它们联合在一起以获得完整的结果集。查询中没有分组,也没有排序,也没有区别。但是,直接在最终连接/选择运算符之前和任何连接之后有两种不同的类型。鉴于上述信息,我对这些不同的类型感到有点困惑。
任何人都可以深入了解为什么这个运营商在这个计划中吗?
FWIW,我重写了这个查询,只是想填补知识空白/纠正误解。
谢谢
我有一个 SQL CmdExec 作业设置来运行查询,但是输出数据包含标题、作业信息和返回行的总数作为输出文件的一部分,我试图弄清楚如何排除它,这个项目只需要要返回的原始数据。
根据其他建议,我尝试在查询之前添加“sqlcmd -Q set nocount on;”,并在查询末尾添加“-S localhost -D master -o” ,但额外的数据仍保留在结果中。
非常感谢任何建议。
sqlcmd -Q set nocount on;
SELECT
cast(coalesce(ARMASTER.CUSTNO, ' ') as char(20)) as [CUSTNO],
cast(coalesce(ARMASTER.INVOICE, ' ') as char(20)) as [INVNO],
cast(coalesce(ITEM.PARTNO, ' ') as char(16)) as [ITEM],
cast(coalesce(ITEM.DESCRIPT, ' ') as char(65)) as [DESCRIP],
cast(coalesce(ITEM.DISRATE, ' ') as char(7)) as [DISC],
cast(coalesce(ITEM.PRICE, ' ') as char(20)) as [PRICE],
cast(coalesce(ITEM.SHIPQTY, ' ') as char(12)) as [QTYSHP],
cast(coalesce(ITEM.TOTPRICE, ' ') as char(20)) as [EXTPRICE],
cast(coalesce(ITEM.CUSERLINE, ' ') as char(10)) as [SEQNUM]
FROM ITEM
JOIN
armaster ON ITEM.invoice = ARMASTER.invoice
ORDER BY ARMASTER.INVOICE
-S localhost -D master -o
我正在尝试使用 TSQL 自动执行以下步骤(目前使用 SQLServer 的 Manual -GUI 界面工作正常),在SQLServer 2022上工作
创建一个表,其中包含用于存储 DBName、AGName、Database_onAG、Database_Not_onAG 的列。(完全的)
编写存储过程脚本以获取所有 AvailabilityGroups 的列表,AG 上的 DB,然后将结果加载到步骤 1 中创建的表中。(完成)
脚本应从 AG (PROBLEM) TSQL 中删除任何名称以 logdb% 开头的 DBS 以删除:
更改可用性组@AGName 删除数据库@DBName
我需要有关上述 TSql 命令的帮助,它执行时没有任何错误,但 DB 不会从 AG 中删除。还是一直在AG群里出现。请问如何使用 TSql 执行此步骤?
我正在构建应用程序,该应用程序必须具有配置(禁用/启用)特定数据库表上的更改跟踪的选项。我正在通过 SQL 用户连接到数据库。需要为 SQL 用户添加哪些数据库权限以禁用/启用对表的更改跟踪和运行以下查询的选项:
SELECT DISTINCT
sct1.name AS CT_schema
, sot1.name AS CT_table
, ps1.row_count AS CT_rows
, sct2.name AS tracked_schema
, sot2.name AS tracked_name
, CHANGE_TRACKING_MIN_VALID_VERSION(sot2.object_id) AS min_valid_version
, itt.create_date AS change_tracking_table_creation_date
, CAST(ps1.reserved_page_count * 8. / 1024 AS BIGINT) AS CT_reserved_MB
, CAST(ps2.reserved_page_count * 8. / 1024 AS BIGINT) AS tracked_base_table_MB
, ps2.row_count AS tracked_rows
FROM sys.internal_tables it
JOIN sys.objects sot1
ON it.object_id = sot1.object_id
JOIN sys.schemas AS sct1
ON sot1.schema_id = sct1.schema_id
JOIN sys.dm_db_partition_stats ps1
ON it.object_id = ps1.object_id
AND ps1.index_id IN (0, 1)
LEFT JOIN sys.objects sot2
ON it.parent_object_id = sot2.object_id
LEFT JOIN sys.schemas AS sct2
ON sot2.schema_id = sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2
ON sot2.object_id = ps2.object_id
AND ps2.index_id IN (0, 1)
INNER JOIN sys.internal_tables itt
ON itt.name = sot1.name
WHERE it.internal_type IN (209, 210);