英文:
I need to simplify my query in SQL Server database
问题
我正在尝试从SQL Server数据库中提取一些行，以使用一些员工电子邮件作为筛选条件，并且我需要安排结果以填充Excel需求文件中的列空间。
SELECT
[FullName], [EmpleMail], [Manager]
FROM
Table1
WHERE
[EmpleMail] IN ('email1', 'email2', '...emailN')
ORDER BY
CASE EmpleMail
WHEN 'email1' THEN 1
WHEN 'email2' THEN 2
WHEN '...emailN' THEN N
这些电子邮件有数千个。
我期望我的记录保持它们的位置，以便我可以按正确的顺序复制它们并粘贴到Excel的列空间中。
但是我收到以下错误消息：
Msg 8621, Level 17, State 1
查询处理器在查询优化过程中耗尽了堆栈空间。请简化查询。
英文:
I'm trying to pull some rows from a SQL Server database using some EMP email as the filter and I need to arrange the result to fill the column space of the requirement file in Excel.
SELECT
[FullName], [EmpleMail], [Manager]
FROM
Table1
WHERE
[EmpleMail] IN ('email1', 'email2', '...emailN')
ORDER BY
CASE EmpleMail
WHEN 'email1' THEN 1
WHEN 'email2' THEN 2
WHEN '...emailN' THEN N
The emails are in thousands.
I was expecting my records to maintain their positions so I can copy them in correct order and paste in the excel column space.
But I'm getting this error:
> Msg 8621, Level 17, State 1
> The query processor ran out of stack space during query optimization. Please simplify the query.
答案1
得分: 3
如果您知道电子邮件列表及其所需顺序，请将它们放入一个表格中。最好是一个_永久_表，如果您经常这样做，但一个#temp表也可以：
CREATE TABLE #e
(
email nvarchar(320) not null primary key,
ordering int
);
INSERT #e(email, ordering) VALUES
('email1',1),
('email2',2),
...
('email1000',1000);
INSERT #e(email, ordering) VALUES
('email1001',1001),
('email1002',1002),
...
('email2000',2000);
...
然后，您可以在最终查询中避免在代码中重复编写这个混乱部分：
```sql
SELECT t.[FullName], t.[EmpleMail], t.[Manager]
FROM dbo.Table1 AS t
INNER JOIN #e AS e
ON t.[EmpleMail] = e.email
ORDER BY e.ordering;
英文:
If you know the list of e-mails and their desired order, stuff them in a table. Preferably, a permanent table, if you are doing this often, but a #temp table will do:
CREATE TABLE #e
(
email nvarchar(320) not null primary key,
ordering int
);
INSERT #e(email, ordering) VALUES
('email1',1),
('email2',2),
...
('email1000',1000);
INSERT #e(email, ordering) VALUES
('email1001',1001),
('email1002',1002),
...
('email2000',2000);
...
Then you can avoid hard-coding any of that mess (twice) in the final query:
Select t.[FullName], t.[EmpleMail], t.[Manager]
From dbo.Table1 AS t
INNER JOIN #e AS e
ON t.[EmpleMail] = e.email
ORDER BY e.ordering;
答案2
得分: 2
可以使用临时表来存储搜索到的电子邮件。例如：
create table #tmp_emails (email varchar(50), seq int);
insert into #tmp_emails (email, seq) values ('email1', 1);
insert into #tmp_emails (email, seq) values ('email2', 2);
insert into #tmp_emails (email, seq) values ('email5000', 5000);
select t.*
from table1 t
join #tmp_emails e on e.email = t.emplemail
order by e.seq
可以将所有的插入操作压缩成一个，但如果插入操作太多，可能会达到某些 SQL 语句的限制。
英文:
You can use a temporary table to store the searched emails. For example:
create table #tmp_emails (email varchar(50), seq int);
insert into #tmp_emails (email, seq) values ('email1', 1);
insert into #tmp_emails (email, seq) values ('email2', 2);
insert into #tmp_emails (email, seq) values ('email5000', 5000);
select t.*
from table1 t
join #tmp_emails e on e.email = t.emplemail
order by e.seq
It's possible to compress all inserts into a single one, but if you have too many of them you could reach some SQL statement limits doing so.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论