faster way to do this select query

Say Employee has three columns FirstName, LastName and ID.

The query should first search for a name in firstname, only if its not found search for last name.

so select *from Employee where FirstName = 'test%' or lastname='test'%'. wont' work.

The query below will work.

select FirstName, LastName, ID
from EMPLOYEE
WHERE 
    LastName = 'Test%'
    AND
    (COUNT(SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%')=0)
    OR
    SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%'

I need to map this back to NHibernate, is there a faster efficient way of doing this instead of making two database calls?

---

试试这个：

SELECT FirstName, LastName, ID FROM EMPLOYEE WHERE FirstName = 'Test%'
OR (FirstName <> 'Test%' AND LastName = 'Test%')

---

FROM Employee
WHERE (FirstName LIKE 'Test%' AND LastName NOT LIKE 'Test%')
OR (LastName LIKE 'Test%' AND firstName NOT LIKE 'Test%')

当然你并不在意他们回来的顺序是什么。如果记录必须返回名字匹配的记录，后面跟着名字匹配的名字，那么这将不起作用。

---

"Premature optimization is the root of all evil".

Why would you care of how the search is done as the optimizer sees it, instead of declaring what you want?
It all boils down to a boolean truth table: when FirstName matches, you want the record (whatever is in LastName, match or nomatch) and if FirstName does not match, you want the record when LastName matches:

F match,   L match   => yes
F match,   L nomatch => yes
F nomatch, L match   => yes
F nomatch, L nomatch => no

That is exactly the OR condition: (FirstName matching) OR (LastName matching) ; the only discarded records are when both FirstName and LastName do not match.

The boolean optimization will ensure that the 2nd condition is not even evaluated when the 1st one is true.

So your query is:

SELECT FirstName, LastName, ID
FROM Employee
WHERE (FirstName LIKE 'Test%')
   OR (LastName LIKE 'Test%')

UPDATE : I may have misunderstood the goal if it is indeed not to return any LastName match if records were found with only the FirstName...
Anyway, the stance on premature optimization is still valid...
You need somehow a 2 pass query as you cannot tell if LastName is to be considered until you're sure you don't have any match on FirstName. But with proper indexes and statistics, the optimizer will make it very efficient.

The query:

SELECT FirstName, LastName, ID
FROM   Employee 
WHERE  (FirstName LIKE 'Test%')
    OR (LastName LIKE 'Test%'
        AND (SELECT Count(ID)
             FROM   Employee
             WHERE  FirstName LIKE 'Test%') = 0) 

is only marginally more expensive than the previous.

链接地址: http://www.djcxy.com/p/37090.html

上一篇: 希望在一个hibernae标准中添加两个不同的表（类）

下一篇: 更快的方式来做这个选择查询