SQL Server的查询性能查询表
我有一个数据库,我需要查询从一个表中获取记录,然后在另一个表上查找该值是否存在。该表可能会返回多个记录,我想要最近的日期。SQL Server的查询性能查询表
所以表1基本上是:
ID (Primary Key)
Name
Test_Score
和表2是
Test_Id (Primary)
Student_ID (Foreign Key)
Test_Score
Test_Date
,因为我经历的记录,如果在表2中存在的student_id数据的没有测试,我想要使用表1中的分数,否则我想使用表2中最近日期的分数。我将这一切全部用C#代码工作,但客户端需要在存储过程中进行报告,而且由于表格非常大,所以我看到了一些性能问题。此外,这个基本的例子实际上发生在多个表上。
我敢肯定有一个优雅的方式来做到这一点,快速和高效,但我似乎无法拿出任何东西,但使用游标。
有人知道直接的解决方案吗?
没有100%地肯定百分之约语法细节,但是这样的事情:
select table1.Name, ISNULL(table2.Test_Score, table1.Test_Score)
from
table1
left outer join table2
on table1.id = table2.Student_ID
AND table2.Test_Date = (
select max(x.Test_Date)
from table2 x
where table1.id = x.Student_ID
group by x.Student_ID)
如果不允许子查询的地方是,它移动到WHERE子句。 (对不起,我现在无法尝试它。)
该查询只在Test_Date是唯一的时才起作用。如果没有,你会得到重复的结果。那么你应该使用一组
select table1.Name, min(ISNULL(table2.Test_Score, table1.Test_Score))
from
table1
left outer join table2
on table1.id = table2.Student_ID
AND table2.Test_Date = (
select max(x.Test_Date)
from table2 x
where table1.id = x.Student_ID
group by x.Student_ID)
group by table1.id, table1.Name
谢谢,这是看我在找什么。现在我必须弄清楚如何从那里的第一列中找到名字。 – 2009-04-21 22:45:29
这很简单:-) – 2009-04-21 22:46:49
斯特凡斯泰内格尔基本上有正确的答案。
通过功能分解使自己更容易:写一个视图,为每个学生提供最近日期的行。
然后将外连接到学生表(表1中的问题),并在表1中使用isnull或coalesce在视图中没有行的情况下获得测试分数。
如果您使用的是Sql Server 2005,那么Common Table Expressions(CTE)提供了一个优雅的解决方案。您为每个学生创建一个CTE,并带有最新的考试分数,然后将其加入到学生表中。如果在使用的测试表中存在结果,则使用学生表中的分数。
我假定你的表分别被称为学生和TestResult中,也假定为test_id是一个自动递增的ID
WITH RecentResults as (
SELECT student_id,
test_score
FROM TestResult tr
WHERE tr.test_id = (SELECT MAX(test_id) FROM TestResult WHERE student_id = tr.student_id)
)
SELECT s.ID as 'Student ID',
isnull(rr.test_score, s.test_score)
FROM Students s LEFT JOIN RecentResults rr ON s.ID = rr.student_id
我无法测试这台机器上的代码 - 如果你提供数据库模式将更容易细化。
在这里你去,在2005年SQL测试的解决方案,认为是可以避免的,但我认为,它提高了清晰度
create table students (id int primary key, name nvarchar(50), score int)
create table scores (test_id int, student_id int, score int, date datetime)
insert students values (1, 'bob', 1)
insert students values (2,'bill', 55)
insert scores values (22,1,88,getdate())
insert scores values (23,1,88,getdate() + 1)
insert scores values (23,1,89,getdate() + 2)
go
create view latest_scores
as
select scores.student_id, scores.date, score
from scores
join
(
select student_id, date = max(date) from scores
group by student_id
) maxDates on maxDates.student_id = scores.student_id and maxDates.date = scores.date
go
select Id, isnull(l.score, s.score) from students s
left join latest_scores l on student_id = id
请提供完整的示例代码,所以它更容易为我们写一个解决方案(创建表语句和一些虚拟数据) – 2009-04-21 22:30:57
表名至少会很好,我感觉你的数据库模式是有点开始。但是,如果不知道更多,我们无法分辨出来 – Louis 2009-04-21 22:33:27