SELECT ... IN to duplicate records in resultset?

Do you know how to write a query like:

SELECT * FROM customers WHERE id IN ( 1, 3, 1, 1 )

to return also the records that are duplicate (ie: 3 customers with id 1)?

another 开发者_Python百科question - is there a way I can get the records sorted by the order of the ids in the IN clause?

Edit: The ID is PK, there are no multiple records with the same ID. I want the resultset to contain the record with ID 1, multiplied 3 times

Thanks

Can you insert the list entries into a (temporary) table? if so

select c.* from customers c join #tmp t on c.id = t.id

To solve the ordering you'd need insert them in order with an ascending numeric value e.g. 'rank'

select c.* from customers c join #tmp t on c.id = t.id order by t.rank

for your first question this should work: SELECT * FROM customers WHERE id IN (1, 3); it will return all customer that have id=1 or id=3 no matter how many are there share same id.

for second question:

SELECT * FROM customers WHERE id IN ( 1, 3) Order By Field(id,1,3);

Values in Field should be same as in IN clause.

If you have 3 customers with the same id, you have bigger problems (assuming id is the PK). You will run into some data integrity issues at some point.

You could build an inline table, and join it to the base table:

SELECT  cust.* 
FROM    (
        select  1 as id
        union all
        select  3
        union all
        select  1
        union all
        select  1
        ) as ids
LEFT JOIN
        Customers cust
ON      ids.id = cust.id

HI, if your id in table is primary key then there is no way to have more customers with id, but in case its not than it will return all customers with id=1