如何在TSQL中缩短长UNION ALL查询

I need to shorten this query and while I'm pretty good at SQL, I'm still learning.

我需要缩短这个查询,虽然我非常擅长SQL,但我还在学习。

    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
        LEFT([Recipient-Address], 6) IN ('doejoh') 
UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
        LEFT([Recipient-Address], 10) IN ('john.doe@g') 
UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
            LEFT([Sender-Address], 6) IN ('doejoh') 
    UNION ALL 
    SELECT
        'doejoh',
        DATETIME,
        [Recipient-Address], [Message-Subject], [Sender-Address] 
    FROM
        dbo.Logs 
    WHERE
            LEFT([Sender-Address], 10) IN ('john.doe@g')
    ORDER BY
        DateTime

I have to use this union, because in the same table, there are 4 different possibilities for each user and their email address. That being said, I have 30 users, so 30x4 would be 120 groups in this entire query. The reason the first column has to be the username is because I'm using that column in a Crystal Report.

我必须使用这个联合,因为在同一个表中,每个用户及其电子邮件地址有4种不同的可能性。话虽这么说,我有30个用户,所以在整个查询中30x4将是120个组。第一列必须是用户名的原因是因为我在Crystal Report中使用该列。

I'm just looking to create some logic for my query that will shorten it down, while at the same time, "assigning" each user to their appropriate first column.

我只是想为我的查询创建一些逻辑,将其缩短,同时将每个用户“分配”到适当的第一列。

Edited to add

编辑添加

While this will shorten my query, I'll still have to have 30 unions:

虽然这会缩短我的查询,但我仍然需要有30个工会:

SELECT
   'doejoh',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejoh') OR
   LEFT([Recipient-Address], 10) IN ('john.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejoh') OR
   LEFT([Sender-Address], 10) IN ('john.doe@g')
ORDER BY
   DateTime

Because the next user would be unioned to the previous one:

因为下一个用户将与前一个用户联合:

UNION ALL 
SELECT
   'doejan',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejan') OR
   LEFT([Recipient-Address], 10) IN ('jane.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejan') OR
   LEFT([Sender-Address], 10) IN ('jan.doe@g')

And so on and so forth... any shorter way?

依此类推......任何更短的方式?

5 个解决方案

#1

Is there a reason something like this won't work?

有这样的事情是行不通的吗?

CREATE TABLE #TempNames
(
    shortname nvarchar(6),
    longname nvarchar(10)
)

INSERT INTO #TempNames (shortname, longname) VALUES('doejoh', 'john.doe@g')
INSERT INTO #TempNames (shortname, longname) VALUES('doejan', 'jan.doe@g')
INSERT INTO #TempNames (shortname, longname) VALUES('smibob', 'bob.smith@g')

SELECT
    #TempName.shortname,
    DATETIME,
    [Recipient-Address], [Message-Subject], [Sender-Address]
FROM
    dbo.Logs
INNER JOIN
    #TempNames
ON
    LEFT([Recipient-Address], 6) = #TempNames.shortname
OR
    LEFT([Recipient-Address], 10) = #TempNames.longname
OR
    LEFT([Sender-Address], 6) = #TempNames.shortname
OR
    LEFT([Sender-Address], 10) = #TempNames.longname

#2

You should rewrite your query as:

您应该将查询重写为:

SELECT
   'doejoh',
   DATETIME,
   [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
   dbo.Logs 
WHERE
   LEFT([Recipient-Address], 6) IN ('doejoh') OR
   LEFT([Recipient-Address], 10) IN ('john.doe@g') OR
   LEFT([Sender-Address], 6) IN ('doejoh') OR
   LEFT([Sender-Address], 10) IN ('john.doe@g')
ORDER BY
   DateTime

SHould be the same in terms of selection, just a bit faster and easier to understand, I think.

我认为,在选择方面应该是相同的,只是更快,更容易理解。

Marc

#3

create a mapping table and join to it.

创建一个映射表并加入它。

eg. something like

例如。就像是

select user_name, DateTime .... 
from Logs
join Users on 
   LEFT([Recipient-Address], 6) IN (user_name) OR
   LEFT([Recipient-Address], 10) IN (user_email) OR
   LEFT([Sender-Address], 6) IN (user_name) OR
   LEFT([Sender-Address], 10) IN (user_email)

#4

Can't you use just...

你不能只用...

SELECT
    'doejoh',
    DATETIME,
    [Recipient-Address], [Message-Subject], [Sender-Address] 
FROM
    dbo.Logs 
 WHERE
    (LEFT([Recipient-Address], 10) IN ('john.doe@g'))
or  (LEFT([Recipient-Address], 6) IN ('doejoh') )
or  ( LEFT([Sender-Address], 10) IN ('john.doe@g'))
or  (LEFT([Sender-Address], 6) IN ('doejoh') )

#5

Create a table with the email address of the 30 people. Table: Emails Columns: short6, long10, email

创建一个包含30个人的电子邮件地址的表。表:电子邮件列:short6,long10,电子邮件

then using only 1 union all

然后只使用1个联合

Select Emails.short6, Logs.DateTime, Logs.[Recipient-Address], Logs.[Message-Subject], Logs.[Sender-Address]
From Emails JOIN Log on Emails.email = Log.[Recipient-Address]
Where LEFT([Recipient-Address], 6) = Emails.short6 
or LEFT([Recipient-Address], 10) = Emails.long10

union all

Select Emails.short6, Logs.DateTime, Logs.[Recipient-Address], Logs.[Message-Subject], Logs.[Sender-Address]
From Emails JOIN Log on Emails.email = Log.[Sender-Address]
Where LEFT([Sender-Address], 6) = Emails.short6 
or LEFT([Sender-Address], 10) = Emails.long10

#1