SELECT ... IN 复制结果集中的记录？

发布于 2024-11-08 13:56:38 字数 249 浏览 1 评论 0原文

您知道如何编写这样的查询：

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

还返回重复的记录（即： id 为 1 的 3 个客户）吗？

另一个问题 - 有没有办法让记录按 IN 子句中 id 的顺序排序？

编辑： ID为PK，不存在多个具有相同ID的记录。我希望结果集包含 ID 为 1 的记录，乘以 3 次

谢谢

原文

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 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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

我一直都在从未离去 2024-11-15 13:56:38

您可以将列表条目插入（临时）表中吗？如果是这样，

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

要解决排序问题，您需要按升序插入它们，例如“排名”

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

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

回复收藏 0 原文

作妖 2024-11-15 13:56:38

对于你的第一个问题，这应该有效： SELECT * FROMcustomers WHERE id IN (1, 3); 它将返回所有具有 id=1 或 id=3 的客户，无论有多少人共享相同的信息ID。

对于第二个问题：

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

字段中的值应与 IN 子句中的值相同。

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.

回复收藏 0 原文

提笔落墨 2024-11-15 13:56:38

如果你有 3 个客户具有相同的 id，你就会遇到更大的问题（假设 id 是 PK）。在某些时候您会遇到一些数据完整性问题。

回复收藏 0 原文

旧故 2024-11-15 13:56:38

您可以构建一个内联表，并将其连接到基表：

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

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

回复收藏 0 原文