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
精彩评论