Question

Hi All:
I am rourke to SQL and here is my doubt is using OR operator. I have an employee table with 2 records, employeeid column of 1st row value is 1 and 2nd row column value is *. My query has to fetch the row with employeeid column value as 1 otherwise it has to fetch the rows with employeeid as *. Finally the query has to fetch either one set of records i.e. set of records with employeeid as 1 or set of records with employeeid as *.

I have used "Select * from Employee where emp_id = 1 or emp_id = * ;" but the resultset is fetching all rows where employeeid value as 1 and *. I want either one set of records only.

How to do it in SQL? Thanks in advance

Answer 1

试试这个：

SELECT * 
    FROM Employee 
 WHERE emp_id =  1  
 UNION ALL
SELECT * 
    FROM Employee 
 WHERE emp_id =  *  
        AND NOT EXISTS 
        (
            SELECT 1 
                FROM    Employee 
             WHERE emp_id =  1  
        )

Answer 2

Select * 
from Employee 
where emp_id = case 
        when exists (select * from Employee where emp_id =  1 ) 
        then  1  
        else  *  
    end

Answer 3

select top 1 * from Employee where emp_id =  1  or emp_id =  *  order by emp_id desc

Answer 4

select * from Employee where emp_id =  1   OR emp_id IS NOT NULL

友情链接