主页 / user-136525

JOSEFtw's questions

Martin Hope
JOSEFtw
Asked: 2020-03-11 03:47:51 +0800 CST
  • 1

下面的 SQL

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', 'test'), 2) as 'From String'

产生以下哈希：

EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF

如果我然后用'test'[Key] 中的列值替换，而不是像这样：

SELECT CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2) as 'From Column' FROM MyTable
WHERE [Key] = 'test'

产生以下哈希：

9F7D8627E02F97CC5A52DCB2BA96038FE12F2A34B0FAC50E041359AE13D5EDE8A8A50562DA58BA7916DA378E7343EF91E85EFBD6A0A70AB237ADA4C2274DF13D

现在我们的数据库中有几行我想散列，所以我想运行以下代码：

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', [Key]), 2)

但问题是产生的散列不正确，只有当我[Key]用实际的字符串值替换时它才是正确的。

我发现了这个问题：使用 HASHBYTES() 为 nvarchar 和一个变量产生不同的结果，并且他们通过在字符串前面加上前缀来“解决它” N。但我想反过来做，我希望输出是 EE26B0DD4AF7E749AA1A8EE3C10AE9923F618980772E473F8819A5D4940E0DB27AC185F8A0E1D5F84F88BC887FD67B143732C304CC5FA9AD8E6F57F50028A8FF.

我怎样才能做到这一点？

我的表如下所示：

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [nvarchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
sql-server hashing
Martin Hope
JOSEFtw
Asked: 2019-06-26 00:20:42 +0800 CST
  • 3

我有下表：

CREATE TABLE [dbo].[Journals](
    [Id] [nvarchar](450) NOT NULL,
    [CorrelationId] [nvarchar](450) NOT NULL,
    [Version] [int] NOT NULL,
    [Type] [nvarchar](max) NOT NULL,
    [Data] [nvarchar](max) NOT NULL,
    [Created] [datetime2](7) NOT NULL,
    [Updated] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Journals] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[Journals] ADD  DEFAULT (getutcdate()) FOR [Updated]
GO

一行可能如下所示：

Id: 1833eac9-3ea7-4613-be26-af3f7589d6f0
CorrelationId: 0HLNKM700ILBN:00000001
Version: 1
Type: Notification
Data: [{"type": "Event_Received","timeStamp": "2019-06-24T12:47:54.3960942Z","data": {"version": 1,"notificationName": "My Notification","receiverName": "John Doe","receiverUserIdentifier": "123456789","receiverEmail": "[email protected]","senderName": "Some Sender","senderEmail": "[email protected]","contentSubject": "This is my subject","contentBody": "And this is my body","attachmentNames": []},"sensitive": true}, {"type": "Event_ValidationSucceded","timeStamp": "2019-06-24T12:47:54.4167034Z","data": "{}","sensitive": false}, {"type": "Event_PublishStart","timeStamp": "2019-06-24T12:47:54.4691455Z","data": "{}","sensitive": false}, {"type": "Event_PublishSucceeded","timeStamp": "2019-06-24T12:47:54.4740542Z","data": "{}","sensitive": true}]
Created: 2019-06-19 12:29:20.8100000
Updated: 2019-06-19 12:30:51.1123311

我想从包含 Sensitive = true 的数组条目的行中获取所有 Id

所以我尝试了这样的事情：

SELECT Id
FROM Journals journals
CROSS APPLY OPENJSON(journals.Data)
WITH (JournalType nvarchar(255) '$.type', Sensitive bit '$.sensitive') as jsonValues
WHERE Sensitive = 1

这可行，但我得到了重复的 id（因为数组包含多个 Sensitive = true 的项目）。

如何限制查询，以便当数组包含多个 Sensitive = true 的项目时，我不会收到重复的 Id？

想要的输出：

1833eac9-3ea7-4613-be26-af3f7589d6f0

实际输出：

1833eac9-3ea7-4613-be26-af3f7589d6f0
1833eac9-3ea7-4613-be26-af3f7589d6f0

我想我可以在 SELECT DISTINCT Id 中添加一个 DISTINCT 但这感觉像是在作弊，或者？:D

sql-server json
Martin Hope
JOSEFtw
Asked: 2018-06-19 23:30:33 +0800 CST
  • 5

我有以下表结构

AccountID   Property            Value
123456      Status              Active
123456      City                Los Angeles
123456      RegistrationDate    2018-05-11 11:30:14.000
543210      Status              Active
543210      City                Las Vegas
543210      RegistrationDate    2018-05-11 11:13:14.000
888888      Status              Inactive
888888      City                Toronto
888888      RegistrationDate    2015-05-12 11:13:14.000

我希望能够选择 Property == Status 和 Value == Active And Property == RegistrationDate 和 Value == 2018-05-11 11:30:14.000 的所有行

我想我需要做某种 GROUP BY 然后选择那个聚合但我无法理解它（我的 sql 真的生锈了）。

我想要上述“查询”的以下输出

AccountID    Status    RegistrationDate
123456       Active    2018-05-11 11:30:14.000
543210       Active    2018-05-11 11:30:14.000

如果所有的列都存在于同一行，我会写这样的：

SELECT AccountID FROM Property
WHERE Status = 'Active'
AND RegistrationDate = '2018-05-11 11:30:14.000'
sql-server sql-server-2016
Martin Hope
JOSEFtw
Asked: 2017-10-13 11:53:40 +0800 CST
  • 2

给定以下表结构：

Id  OldUrl  Target
1    /a      /b
2    /b      /c
3    /c      /d
4    /x      /y

我想做一个“跟随”的选择Target Column，例如：

如果我传入 ID 1，我想返回第 3 行，因为如果您选择目标然后选择下一行，A 将解析为 C，OldUrl = Target 如下所示：

a -> b
b -> c
c -> d

建表SQL：

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OldUrl] [nvarchar](450) NOT NULL,
    [Target] [nvarchar](max) NOT NULL,
    [Wildcard] [bit] NOT NULL,
    [Disabled] [bit] NOT NULL,
    [Permanent] [bit] NOT NULL,
 CONSTRAINT [PK_dbo.MyTable] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

我已经通过使用下面的代码设法做到了这一点，但是我的 SQL 技能非常有限所以我只想知道是否有更好/更清洁的方法来做到这一点？

DECLARE
@Id INT = 1,
@Target nvarchar(MAX) = '',
@Result nvarchar(MAX) = ''

WHILE (1=1)
    BEGIN
        SELECT @Result=(SELECT [Target] FROM MyDatabase.MyTable WHERE Id = @Id)
        if(@Result = @Target)
            break
        SELECT @Id = [Id] FROM MyDatabase.MyTable WHERE OldUrl = @Result

        SET @Target = @Result
    END

SELECT * FROM MyDatabase.MyTable WHERE Id = @Id

传入 Id = 1 时需要输出

3   /c  /d
sql-server