McNets's questions

我需要使用与此类似的模式来挖掘日志表：

CREATE TABLE t (
  id int PRIMARY KEY,
  data varchar(max)
);

Columndata以这种格式存储从 Web 服务接收到的 XML 文本：

_{这是精简版}

<?xml version="1.0" encoding="UTF-8"?>
<PARAM>
  <TAB DIM="30" ID="ZC3D2_1" SIZE="5">
    <LIN NUM = "1">
      <FLD NAME = "ZDOC" TYPE = "Char">Ferran López</FLD>
    </LIN>
  </TAB>
</PARAM>

当我尝试将此文本转换为 XML 时，出现下一个错误：

XML解析：第xx行，字符48，非法xml字符

可以通过删除<xml>标签或至少删除encoding属性来解决。

注意：如果没有特殊字符ó，即使我不删除<xml>标签，它也可以正常工作。

问题

有没有办法在不替换或删除<xml>标签的情况下将其转换为 XML？

CAST(REPLACE(data, 'encoding="UTF-8"', '') as XML)

db<>在这里摆弄

更新

服务器排序规则为：Latin1_General_BIN

但即使我尝试将排序规则更改为我通常的服务器排序规则，它也不起作用。

SELECT
  id, 
  CAST((data COLLATE Latin1_General_CI_AS) as XML)
FROM
  t;

我正在开发一个调用存储过程的.Net 应用程序，该存储过程具有声明为 TYPE READONLY 的表值参数。

我第一次尝试调用 SP 时收到下一个错误：

消息 229，级别 14，状态 5，第 1 行
对象“TYPE_OBJ”、数据库“MY_DB”、模式“dbo”的执行权限被拒绝。

向用户授予 EXECUTE 权限后，它工作正常。

但是我在 MS-DOCS 中的CREATE TYPE和使用表值参数中都找不到对将其用作参数所需的权限的任何引用。

在哪里可以找到有关使用 TYPE 作为参数的必要权限的信息？

我正在尝试（学习）使用 Visual Studio 2017 发布 SQLCLR 函数。（这是一个发送电子邮件的简单函数。）

作为参考，我在CodeProject和MSSQLTips上使用了这篇文章：

使用 Visual Studio 2013 数据库项目创建、运行、调试和部署 SQL CLR 函数

使用 CLR 存储过程从 SQL Server Express 发送电子邮件

在项目属性-> SQLCLR 我已经设置：

Permission level : UNSAFE

根据文章，我更新了目标数据库：

sp_configure 'clr enabled', 1;  
GO  
RECONFIGURE;  
GO  

在我得到错误之后，我在 MSSQLTips 文章中遵循了这个建议：

如果在尝试编译代码时收到错误消息，您可能需要使用以下命令更改数据库，然后再次尝试创建程序集和存储过程。

ALTER DATABASE msdb SET trustworthy ON

我尝试过使用两个不同的目标数据库：

• SQL-Server 2017 本地数据库
• SQL-Server 2017 速成版

我可以毫无错误地构建项目，但是当我发布项目时，我在执行下一个命令时收到一个错误：

CREATE ASSEMBLY [dbSysmac]
    AUTHORIZATION [dbo]
    FROM 0x5F8A900003000000...
    WITH PERMISSION_SET = UNSAFE;

(47,1)：SQL72014：.Net SqlClient 数据提供程序：消息 10327，级别 14，状态 1，第 1 行为程序集“dbSysmac”创建程序集失败，因为程序集“dbSysmac”不受信任。当以下任一情况为真时，程序集是受信任的：程序集使用证书或非对称密钥签名，该密钥具有相应的登录名并具有 UNSAFE ASSEMBLY 权限，或者程序集使用 sp_add_trusted_assembly 受信任。

我做错了什么？

给出下一个例子：

IF OBJECT_ID('dbo.my_table') IS NOT NULL
    DROP TABLE [dbo].[my_table];
GO

CREATE TABLE [dbo].[my_table]
(
    [id]    int IDENTITY (1,1)  NOT NULL PRIMARY KEY,
    [foo]   int                 NULL,
    [bar]   int                 NULL,
    [nki]   int                 NOT NULL
);
GO

/* Insert some random data */
INSERT INTO [dbo].[my_table] (foo, bar, nki)
SELECT TOP (100000)
    ABS(CHECKSUM(NewId())) % 14,
    ABS(CHECKSUM(NewId())) % 20,
    n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
FROM 
    sys.all_objects AS s1 
CROSS JOIN 
    sys.all_objects AS s2
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC);
GO

如果我获取按[nki]（非聚集索引）排序的所有记录：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 266 ms, elapsed time = 493 ms

优化器选择聚簇索引，然后应用排序算法。

Execution plan

但是如果我强制它使用非聚集索引：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table WITH(INDEX(IX_my_TABLE));
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 311 ms, elapsed time = 188 ms

然后它使用带有键查找的非聚集索引：

Execution plan

显然如果将非聚集索引转化为覆盖索引：

CREATE UNIQUE NONCLUSTERED INDEX [IX_my_table]
    ON [dbo].[my_table] ([nki] ASC)
    INCLUDE (id, foo, bar);
GO

然后它只使用这个索引：

SET STATISTICS TIME ON;
SELECT id, foo, bar, nki FROM my_table ORDER BY nki;
SET STATISTICS TIME OFF;

SQL Server Execution Times: CPU time = 32 ms, elapsed time = 106 ms

Execution plan

问题

• 为什么 SQL Server 使用聚集索引加排序算法而不是使用非聚集索引，即使在后一种情况下执行时间快 38%？

根据 MS-DOCS about System information schema views，架构列定义有一条警告说明：

**重要** 不要使用 INFORMATION_SCHEMA 视图来确定对象的架构。查找对象架构的唯一可靠方法是查询 sys.objects 目录视图。

为什么不能使用 INFORMATION_SCHEMA 视图来确定对象的模式？

这个信息有误吗？

我们公司从我们的一位客户那里收到一份每日文件。它是一个翻译成 XML 并从 SFTP 服务器下载的 EDIFACT 文件。

我们开发了一个（c# 控制台）应用程序：

1. 下载文件。
2. 使用存储过程将其上传到一台 SQL Server (2005)。
3. 从网络共享中删除此文件。
4. 运行另一个存储过程以将其与我们的销售订单表集成。

SQL-Server 和 SQL-Server 代理服务作为LocalSystem帐户启动。（它很快就会被替换，我宁愿让它保持原样，我知道它没有正确配置。）

将文件存储在我们的 SQL-Server 中的过程与此类似：

CREATE PROCEDURE [dbo].[sp_ProviderName_Download]
(
    -- FQN of XML file
    @file_name nvarchar(260)
)
AS
BEGIN

    DECLARE @xml_data XML,
            @cmd nvarchar(max);

    BEGIN TRY

        SET @cmd = 'SELECT @xmlText = BulkColumn FROM OPENROWSET(BULK '
                    + '''' + @file_name + ''''
                    + ', SINGLE_BLOB) x;';

        EXEC sp_executesql @cmd, N'@xmlText XML OUTPUT', @xmlText = @xml_data OUTPUT;

        INSERT INTO [dbo].[ProviderXML_Register] (..., xml_data)
        VALUES (..., @xml_data);

        RETURN 0;

    END TRY
    BEGIN CATCH

        EXEC [dbo].[vsp_error_handler];
        RETURN -1;

    END CATCH

END

问题：

存储过程的工作方式取决于我们在 C# 应用程序中设置 ConnectionString 的方式。

如果它使用 IntegratedSecurity=false：

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "xxx.xxx.xxx.xxx";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = false;
this.scsb.Password = "*******";
this.scsb.UserID = "SqlServerLogin";

它可以正常工作而没有错误。

但如果它使用 IntegratedSecurity=true 连接（AD 用户）

this.scsb = new SqlConnectionStringBuilder();
this.scsb.ApplicationName = "ProviderXML_Download";
this.scsb.WorkstationID = Environment.MachineName;
this.scsb.DataSource = "SQL_INSTANCE_NAME";
this.scsb.InitialCatalog = "my_db";
this.scsb.IntegratedSecurity = true;

它失败并出现错误：

操作系统错误 5 访问被拒绝。

AD 用户对网络共享拥有 MODIFY 权限。据我所知，“LocalSystem”帐户不应该能够访问网络共享。

• 为什么当 IntegratedSecurity=true 时出现此错误？

有时我将对象名称（标识符）存储在我们的某些数据库中，例如某些参数表中。因为我使用 '=' 或 'LIKE' 比较运算符从这些表中选择记录，所以我必须注意始终使用或不使用括号来存储这些名称。

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = '[TABLE_NAME]';

或者

IF EXISTS (SELECT 1 FROM MYTABLE WHERE OBJ_NAME = 'TABLE_NAME';

但是，MS-SQL 有一些函数，您可以在其中使用带或不带括号的对象名称，例如 OBJECT_ID() 函数。我在dbfiddle.uk上设置了一个最小示例。

CREATE TABLE TEST
(
    ID     INT IDENTITY(1,1) PRIMARY KEY,
    OBJECT sysname NOT NULL
);
GO

INSERT INTO TEST VALUES ('[obj1]'),('obj2'),('obj3'),('[obj4]');
GO

现在我可以使用 OBJECT_ID() 来检查表 TEST 是否以这种方式存在：

IF OBJECT_ID('TEST') IS NOT NULL
BEGIN
    SELECT 'TEST EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID    |
| :----------- |
| TEST EXISTS. |

IF OBJECT_ID('[TEST]') IS NOT NULL
BEGIN
    SELECT '[TEST] EXISTS.' OBJECT_ID;
END
GO

| OBJECT_ID      |
| :------------- |
| [TEST] EXISTS. |

我是否通过带有或不带有括号的标识符 TEST 都没有关系，解析器足够聪明，可以删除括号。

好吧，我可以通过添加一个从一个字符串中删除括号的标量函数来模拟这一点：

CREATE FUNCTION UNQUOTENAME(@TXT NVARCHAR(MAX)) 
RETURNS NVARCHAR(MAX)
AS
    BEGIN
        RETURN IIF(LEFT(@TXT, 1) = N'[' AND RIGHT(@TXT, 1) = N']', 
                   SUBSTRING(@TXT, 2, LEN(@TXT) -  2), 
                   @TXT);
    END;
GO

然后以这种方式使用它：

SELECT dbo.UNQUOTENAME (N'[FIELD]') NAME1, N'FIELD' NAME2;
GO

NAME1 | NAME2
:---- | :----
FIELD | FIELD

SELECT ID, OBJECT 
FROM   TEST 
WHERE OBJECT LIKE 'obj%';
GO

ID | OBJECT
-: | :-----
 2 | obj2  
 3 | obj3  

SELECT ID, dbo.UNQUOTENAME(OBJECT) 
FROM   TEST 
WHERE  dbo.UNQUOTENAME(OBJECT) LIKE 'obj%';
GO

ID | (No column name)
-: | :---------------
 1 | obj1
 2 | obj2
 3 | obj3
 4 | obj4  

但我的问题是：

• 是否有任何隐藏的内置函数可以使用 T-SQL 删除括号？

dbfiddle在这里

我通常按​​照以下规则设计我的数据库：

• 除了 db_owner 和 sysadmin 之外没有其他人可以访问数据库表。
• 用户角色在应用层控制。我通常使用一个 db 角色来授予对视图、存储过程和函数的访问权限，但在某些情况下，我会添加第二条规则来保护某些存储过程。
• 我使用触发器来初步验证关键信息。

CREATE TRIGGER <TriggerName>
ON <MyTable>
[BEFORE | AFTER] INSERT
AS
    IF EXISTS (SELECT 1 
               FROM   inserted
               WHERE  Field1 <> <some_initial_value>
               OR     Field2 <> <other_initial_value>)
    BEGIN
        UPDATE MyTable
        SET    Field1 = <some_initial_value>,  
               Field2 = <other_initial_value>  
        ...  
    END

• DML 使用存储过程执行：

sp_MyTable_Insert(@Field1, @Field2, @Field3, ...);
sp_MyTable_Delete(@Key1, @Key2, ...);
sp_MyTable_Update(@Key1, @Key2, @Field3, ...);

您是否认为在这种情况下值得使用默认约束，或者我正在向数据库服务器添加额外且不必要的工作？

更新

我知道通过使用 DEFAULT 约束，我正在向必须管理数据库的其他人提供更多信息。但我最感兴趣的是性能。

我假设数据库总是检查默认值，即使我提供了正确的值，因此我做了两次相同的工作。

例如，有没有办法在触发器执行中避免 DEFAULT 约束？

我需要将每日 XML 文件集成到我的系统中。

XML 文件的架构类似于：

create table txml(id int identity, data xml);
insert into txml (data) values ('
<order>
    <trans DTrans="20170102" HTrans="10:52"></trans>
    <head Id="552665566"></head>
    <lin headId="552665566">
        <dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" />
        <acum DCalAcm="20170101" DIniAcm="20161115"><qtyAcm Qty="1101163.00" /></acum>
        <lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="20000.00"/><dIni Date="20170109"/><dEnd Date="20170112"/></pLin>
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170113"/><dEnd Date="20170116"/></pLin>
    </lin>
    <lin headId="552665566">
        <dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" />
        <acum DCalAcm="20170101" DIniAcm="20161101"><qtyAcm Qty="552652.00" /></acum>
       <lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="2200.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="3000.00"/><dIni Date="20170109"/><dEnd Date="20170116"/></pLin>
    </lin>
</order>');

正如您所看到的，对于每个<lin>部分，您都可以找到未知数量的<pLin>行。到目前为止，我正在使用 C# 和 .Net 控制台应用程序完成这项工作，但我想使用 SQL 来完成这项工作。

到目前为止，我可以<lin>使用下一个查询获得一组标签：

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]',  'decimal(18,2)') QtyLastOrd
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin);

这会产生下一个结果：

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd
-: | :------ | :-------- | :---------- | :---------
 1 | 1111111 | 05220001  | 95767       | 12000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00   

<pLin>但我应该添加与标签对应的嵌套行并产生下一个结果：

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd | DIni       | DEnd       | NextQty  
-: | :------ | :-------- | :---------- | :--------- | ---------- | ---------- | ---------
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 04/01/2017 | 08/01/2017 | 24000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 09/01/2017 | 12/01/2017 | 20000.00  
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 13/01/2017 | 16/01/2017 | 24000.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 04/01/2017 | 08/01/2017 | 2200.00  
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 09/01/2017 | 16/01/2017 | 3000.00  

我在这里设置了一个dbfiddle

给定这样的模式：

CREATE TABLE Foo
(
    Id int PRIMARY KEY,
    Position int NOT NULL,
    Title varchar(10) NOT NULL
);

INSERT INTO Foo VALUES
(1, 3,  'Title3'),
(2, 10, 'Title10'),
(3, 1,  'Title1'),
(4, 12, 'Title12'),
(5, 2,  'Title2');

我需要生成一个逗号分隔的字符串，按以下顺序排列Position：

'M' + Id + ' AS [' + Title + ']'

期望的结果：

M1 AS [Title1], M2 AS [Title10], M3 AS [Title3], M4 AS [Title10], M5 AS [Title12]

我试过了：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

但它构建了按以下顺序排序的结果Id：

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

如果我添加ORDER BY Id到STUFF表达式：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM Foo
                   ORDER BY Position
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

产生下一个错误：

如果指定了 SELECT DISTINCT，则消息 145 级别 15 状态 1 第 2 行
ORDER BY 项必须出现在选择列表中。+

我可以使用按以下顺序排序的子查询Position：

DECLARE @rows nvarchar(max);
SET @rows = STUFF((SELECT DISTINCT ', ' + ('M' + CAST(Id as varchar(10)) + ' AS ' + QUOTENAME(Title)) 
                   FROM (SELECT TOP 100 PERCENT Id, Position, Title FROM Foo ORDER BY Position) X
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)') 
           ,1,1,'');

SELECT @rows;

M1 AS [Title3], M2 AS [Title10], M3 AS [Title1], M4 AS [Title12], M5 AS [Title2]

但我想知道是否有另一种方法可以在不使用子查询的情况下对结果进行排序。没有重复的标题。

dbfiddle在这里

鉴于此架构：

CREATE TABLE #TEST_COALESCE
(
    Id int NOT NULL,
    DateTest datetime NOT NULL,
    PRIMARY KEY (Id, DateTest)
);

INSERT INTO #TEST_COALESCE VALUES
(1, '20170201'),
(1, '20170202'),
(1, '20170203'),
(2, '20170204'),
(2, '20170205'),
(2, '20170206');

如果我在子查询中使用 COALESCE，它会返回 NULL。

SELECT  t1.Id, t1.DateTest,
        (SELECT TOP 1 COALESCE(t2.DateTest, t1.DateTest)
         FROM         #TEST_COALESCE t2
         WHERE        t2.Id = t1.Id
         AND          t2.DateTest > t1.DateTest
         ORDER BY     t2.Id, t2.DateTest) NextDate
FROM    #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest            | NextDate            |
+----+---------------------+---------------------+
| 1  | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1  | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1  | 03.02.2017 00:00:00 | NULL                |
| 2  | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2  | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2  | 06.02.2017 00:00:00 | NULL                |
+----+---------------------+---------------------+

但是，如果它放在子查询之外：

SELECT  t1.Id, t1.DateTest,
        COALESCE((SELECT TOP 1 t2.DateTest
                 FROM         #TEST_COALESCE t2
                 WHERE        t2.Id = t1.Id
                 AND          t2.DateTest > t1.DateTest
                 ORDER BY     t2.Id, t2.DateTest), t1.DateTest) NextDate
FROM    #TEST_COALESCE t1;

+----+---------------------+---------------------+
| Id | DateTest            | NextDate            |
+----+---------------------+---------------------+
| 1  | 01.02.2017 00:00:00 | 02.02.2017 00:00:00 |
| 1  | 02.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 1  | 03.02.2017 00:00:00 | 03.02.2017 00:00:00 |
| 2  | 04.02.2017 00:00:00 | 05.02.2017 00:00:00 |
| 2  | 05.02.2017 00:00:00 | 06.02.2017 00:00:00 |
| 2  | 06.02.2017 00:00:00 | 06.02.2017 00:00:00 |
+----+---------------------+---------------------+

为什么第一个子查询不返回：t1.DateTest？

http://rextester.com/CNDOO40877

设置

我已经在rextester和dbfiddle上设置了一个示例。

设想

Price list : 产品价格表，一个产品可以有多个有效价格，甚至是未来价格。

+---------+-------+------------+--------+--------+
| product | price | date_price |  base  | active |
+---------+-------+------------+--------+--------+
|   0125  |    90 | 01.01.2017 |  1200  |    0   |
|   0125  |   100 | 25.01.2017 |  1000  |    1   |
|   0125  |   110 | 27.02.2017 |   500  |    1   |
+---------+-------+------------+--------+--------+
|   1200  |   140 | 01.01.2017 |  2000  |    0   |
|   1200  |   150 | 01.02.2017 |  1500  |    1   |
|   1200  |   160 | 27.02.2017 |  1000  |    1   |
+---------+-------+------------+--------+--------+

订单挂单有价格和订单日期

+---------+------------+-------+--------+
| product | order_date | price |  base  |
+---------+------------+-------+--------+
|   0125  | 19.02.2017 |  100  |  1000  |
|   0125  | 20.02.2017 |  100  |  1000  |
|   0125  | 21.02.2017 |  100  |  1000  |
|   0125  | 22.02.2017 |  100  |  1000  |
|   0125  | 23.02.2017 |  100  |  1000  |
|   0125  | 28.02.2017 |  110  |   500  |
+---------+------------+-------+--------+
|   1200  | 19.02.2017 |  150  |  1500  |
|   1200  | 20.02.2017 |  150  |  1500  |
|   1200  | 21.02.2017 |  150  |  1500  |
|   1200  | 22.02.2017 |  150  |  1500  |
|   1200  | 23.02.2017 |  150  |  1500  |
|   1200  | 28.02.2017 |  160  |  1000  |
+---------+------------+-------+--------+

每次我们在列表中添加新价格时，我们都必须更新受影响的挂单行。

例如，如果我们添加：

+---------+-------+------------+--------+--------+
| product | price | date_price |  base  | active |
+---------+-------+------------+--------+--------+
|   0125  |   105 | 21.02.2017 |  1300  |    1   |
|   1200  |   155 | 21.02.2017 |  1400  |    1   |
+---------+-------+------------+--------+--------+

新价目表必须是：

+---------+------------+-------+--------+
| product | order_date | price |  base  |
+---------+------------+-------+--------+
|   0125  | 19.02.2017 |  100  |  1000  |
|   0125  | 20.02.2017 |  100  |  1000  |
|   0125  | 21.02.2017 |  105  |  1300  | *
|   0125  | 22.02.2017 |  105  |  1300  | * Affected rows
|   0125  | 23.02.2017 |  105  |  1300  | *
|   0125  | 28.02.2017 |  110  |   500  | 
+---------+------------+-------+--------+
|   1200  | 19.02.2017 |  150  |  1500  |
|   1200  | 20.02.2017 |  150  |  1500  |
|   1200  | 21.02.2017 |  150  |  1500  | *
|   1200  | 22.02.2017 |  150  |  1500  | * Affectd rows between 21.02.2017 and 27.02.2017
|   1200  | 23.02.2017 |  150  |  1500  | *
|   1200  | 28.02.2017 |  160  |  1000  |
+---------+------------+-------+--------+

我想使用单个查询更新受影响的记录。

由于还有另一个价格从 27.02.2017 开始，因此 28.02.2017 的订单不受插入价格的影响。

实际过程

到目前为止，我使用子查询来搜索价格列表中匹配的第一个日期，但现在我也需要更新base字段。（以及两个或三个以上的字段）并且我想避免使用两个或多个子查询。

update @orders
set    price = (select   top 1 pl.price
                from     @price_list pl
                where    pl.product = o.product
                and      pl.date_price <= o.order_date
                and      active = 1
                order by pl.date_price desc),
       base  = (select   top 1 pl.base
                from     @price_list pl
                where    pl.product = o.product
                and      pl.date_price <= o.order_date
                and      active = 1
                order by pl.date_price desc)
from   @orders o
where  o.product in ('0125', '1200');  --<<< select distinct product from inserted

请随时纠正我的文字，我知道我的英语语法不够好。

然而：

从 SQL Server 2008 开始的日期字段类型

给定一张桌子：

CREATE TABLE dbo.MyTable
(
    Code int NOT NULL,
    DateCode datetime NOT NULL
)
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
[Code] ASC, [DateCode] DESC
);

约束 DateCode 只是一个日期的最佳方法是什么？

它必须允许没有时间部分的日期。

2017-01-02
2017-01-02 00:00:00.000
20170102

现在我正在使用：

ALTER TABLE dbo.MyTable ADD CONSTRAINT [CHK_MyTable_DateCode]
    CHECK ([DateCode] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, [DateCode])));

但是，我想知道是否有最好/最短的方法来完成它。

对于这样的给定结构：

+----+----------+
| ID |   Name   |
+----+----------+
| a  | ZZZZZZZZ |
| b  | YYYYYYYY |
| c  | WWWWWWWW |
| d  | ZZZZZZZZ |
| e  | YYYYYYYY |
| f  | WWWWWWWW |
+----+----------+

获取具有相同名称的（ID，ID2）对列表，不包括（ID2，ID）具有对应（ID，ID2）的所有记录。

+----+----------+-----+
| ID |   Name   | ID2 |
+----+----------+-----+
| a  | ZZZZZZZZ |  d  |
| b  | YYYYYYYY |  e  |
| c  | WWWWWWWW |  f  |
+----+----------+-----+

我找到了这个解决方案，但我想知道是否有更“学术”的解决方案。

;WITH Pair AS
(
SELECT
    CASE WHEN p1.Id > p2.Id THEN p2.Id ELSE p1.Id END as ID,
    CASE WHEN p1.Id > p2.Id THEN p1.Id ELSE p2.Id END as ID2,
    p1.Name
FROM Pairs p1
     LEFT JOIN Pairs p2
     ON p1.Name = p2.Name
     AND p1.Id <> p2.Id
)
SELECT DISTINCT ID, ID2, Name
FROM Pair;

复试机

CREATE TABLE Pairs(Id varchar(5), Name varchar(20));
INSERT INTO Pairs VALUES
('a','ZZZZZZZZ'),
('b','YYYYYYYY'),
('c','WWWWWWWW'),
('d','ZZZZZZZZ'),
('e','YYYYYYYY'),
('f','WWWWWWWW');

给定这样的分层表：

CREATE TABLE [dbo].[btree]
(
  id INT PRIMARY KEY
, parent_id INT REFERENCES [dbo].[btree] ([id])
, name NVARCHAR(20)
);

我想获得整个树结构。

例如，使用此数据：

INSERT INTO [btree] VALUES (1, null, '1 Root');
INSERT INTO [btree] VALUES (2,    1, '1.1 Group');
INSERT INTO [btree] VALUES (3,    1, '1.2 Group');
INSERT INTO [btree] VALUES (4,    2, '1.1.1 Group');
INSERT INTO [btree] VALUES (5,    2, '1.1.2 Group');
INSERT INTO [btree] VALUES (6,    3, '1.2.1 Group');
INSERT INTO [btree] VALUES (7,    3, '1.2.2 Group');
INSERT INTO [btree] VALUES (8,    4, '1.1.1.1 Items');
INSERT INTO [btree] VALUES (9,    4, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (10,   5, '1.1.2.1 Items');
INSERT INTO [btree] VALUES (11,   5, '1.1.1.2 Items');
INSERT INTO [btree] VALUES (12,   6, '1.2.1.1 Items');
INSERT INTO [btree] VALUES (13,   6, '1.2.1.2 Items');
INSERT INTO [btree] VALUES (14,   7, '1.2.2.1 Items');

我想获得：

+----+-----------+---------------------+
| id | parent_id | description         |
+----+-----------+---------------------+
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  4 |     2     |     1.1.1 Group     |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.2.2 Items |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
+----+-----------+---------------------+

我正在使用这样的递归查询来获取记录：

;WITH tree AS
(
    SELECT c1.id, c1.parent_id, c1.name, [level] = 1
    FROM dbo.[btree] c1
    WHERE c1.parent_id IS NULL
    UNION ALL
    SELECT c2.id, c2.parent_id, c2.name, [level] = tree.[level] + 1
    FROM dbo.[btree] c2 INNER JOIN tree ON tree.id = c2.parent_id
)
SELECT tree.level, tree.id, parent_id, REPLICATE('  ', tree.level - 1) + tree.name AS description
FROM tree
OPTION (MAXRECURSION 0)
;

这是当前的结果：

+----+-----------+---------------------+
| id | parent_id | description         |
|  1 |    NULL   | 1 Root              |
|  2 |     1     |   1.1 Group         |
|  3 |     1     |   1.2 Group         |
|  6 |     3     |     1.2.1 Group     |
|  7 |     3     |     1.2.2 Group     |
| 14 |     7     |       1.2.2.1 Items |
| 12 |     6     |       1.2.1.1 Items |
| 13 |     6     |       1.2.1.2 Items |
|  4 |     2     |     1.1.1 Group     |
|  5 |     2     |     1.1.2 Group     |
| 10 |     5     |       1.1.2.1 Items |
| 11 |     5     |       1.1.1.2 Items |
|  8 |     4     |       1.1.1.1 Items |
|  9 |     4     |       1.1.1.2 Items |
+----+-----------+---------------------+

我不知道如何按级别排序。

有没有办法为每个子级别设置排名？

我设置了一个Rextester

给定架构：

create table containers (
    id int primary key, 
    name text, 
    product_id int, 
    lot int, 
    qty int, 
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Wherecontainers是一个层数未知的层级表。

containers.parent_id = containers.id OR null

一个容器可以容纳其他容器，这是一个容器树结构：

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   | 
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10] 

对我们来说，一个集装箱被视为一个远征单位，根据客户要求，一个集装箱可以是一个简单的 10 件塑料袋，也可以是一个完整的托盘，有 10 箱，每箱 5000 件。只有最后一层的容器有批次分配，父容器可以容纳不同的批次，但始终属于同一产品。

我们需要按批次列出单个订单中包含的所有产品。

• 不同的产品不能有相同的批次。
• 一个容器只能装同一产品的容器。

样本数据：

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

订单号1有两个详细信息行，容器16& 23，我需要获取这些容器中包含的所有批次。

在此示例中，结果应显示以下行：

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

按批次分组：

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

我用这个值创建了一个rextester示例。

如果您使用以下方法获得某些视图的定义sys.sp_helptext：

exec sys.sp_helptext 'sys.columns'

CREATE VIEW sys.columns
AS
  SELECT c.id                                                             AS object_id,
         c.NAME,
         c.colid                                                          AS column_id,
         c.xtype                                                          AS system_type_id,
         c.utype                                                          AS user_type_id,
         c.length                                                         AS max_length,
         c.prec                                                           AS PRECISION,
         c.scale,
         CONVERT(SYSNAME, CollationPropertyFromId(c.collationid, 'name')) AS collation_name,
         sysconv(bit, 1 - ( c.status & 1 ))                               AS is_nullable,-- CPM_NOTNULL  
         sysconv(bit, c.status & 2)                                       AS is_ansi_padded,-- CPM_NOTRIM  
         sysconv(bit, c.status & 8)                                       AS is_rowguidcol,-- CPM_ROWGUIDCOL  
         sysconv(bit, c.status & 4)                                       AS is_identity,-- CPM_IDENTCOL  
         sysconv(bit, c.status & 16)                                      AS is_computed,-- CPM_COMPUTED  
         sysconv(bit, c.status & 32)                                      AS is_filestream,-- CPM_FILESTREAM  
         sysconv(bit, c.status & 0x020000)                                AS is_replicated,-- CPM_REPLICAT  
         sysconv(bit, c.status & 0x040000)                                AS is_non_sql_subscribed,-- CPM_NONSQSSUB  
         sysconv(bit, c.status & 0x080000)                                AS is_merge_published,-- CPM_MERGEREPL  
         sysconv(bit, c.status & 0x100000)                                AS is_dts_replicated,-- CPM_REPLDTS  
         sysconv(bit, c.status & 2048)                                    AS is_xml_document,-- CPM_XML_DOC   
         c.xmlns                                                          AS xml_collection_id,
         c.dflt                                                           AS default_object_id,
         c.chk                                                            AS rule_object_id,
         sysconv(bit, c.status & 0x1000000)                               AS is_sparse,-- CPM_SPARSE  
         sysconv(bit, c.status & 0x2000000)                               AS is_column_set -- CPM_SPARSECOLUMNSET  
  FROM   sys.syscolpars c
  WHERE  number = 0
         AND has_access('CO', c.id) = 1 

有一个函数叫sysconv.

这些视图使用函数“sysconv”，您可以从此视图中选择值而不会收到任何错误消息。

select * from sys.columns;

但是如果我尝试类似的东西：

select sysconv(bit, 375 & 8);

我收到错误：

sysconv 不是可识别的内置函数名称

正如 Martin Smith 指出的那样，此功能可以替换为convert (bit, 375 & 8)，但我想知道为什么我可以从此视图中选择值，但我不能在我的查询中使用它，即使 SSMS 通过更改前景颜色将其识别为命令？