SQL 选择第一个匹配行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9504840/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select the first matching row
提问by wobblycogs
I have a table "tbl" which looks like this:
我有一个表“tbl”，它看起来像这样：
prod | cust | qty
p1 | c1 | 5
p1 | c2 | 10
p2 | c1 | 2
p3 | c2 | 8
What I need is a distinct list of product and customer pairs but only the first customer if the product is sold to more than one customer. In order words the results need to look like this:
我需要的是产品和客户对的不同列表，但如果产品销售给多个客户，则只需要第一个客户。换句话说，结果需要如下所示：
prod | cust
p1 | c1
p2 | c1
p3 | c2
I've tried this every which way I can think of but I can't quite get the correct result. Clearly neither distinct nor group by will work (on their own) since they will both return the p1, c2 row.
我已经尝试了所有我能想到的方法，但我无法得到正确的结果。显然，distinct 和 group by 都不会（单独）工作，因为它们都将返回 p1, c2 行。
I found this questionwhich is a very close match but I can't figure out how to re-write it to get it to do what I need.
我发现这个问题非常接近，但我不知道如何重新编写它以使其满足我的需要。
To top it all this currently needs to work in Access 2007 or later but at some future point it'll need to work in MySQL as well.
最重要的是，这一切目前需要在 Access 2007 或更高版本中运行，但在未来某个时候，它也需要在 MySQL 中运行。
Extra credit to anyone who also joins the results to the customer table so that I can look up the human readable name from the customer code e.g. c1 => Fred Bloggs Spanners
对将结果加入客户表的任何人都表示额外的感谢，以便我可以从客户代码中查找人类可读的名称，例如 c1 => Fred Bloggs Spanners
回答by J Cooper
Core Question:
核心问题：
SELECT prod, MIN(cust)
FROM yourTable
GROUP BY prod
For the "Bonus":
对于“奖金”：
SELECT T.prod,
T.cust,
YC.SomeCustomerAttribute1,
YC.SomeCustomerAttribute2
FROM (
SELECT prod, MIN(cust) AS first_cust
FROM yourProducts
GROUP BY prod
) AS T
JOIN yourCustomers AS YC ON YC.cust = T.first_cust
回答by Mike Ryan
I'm going to have to assume that you have some kind of identifier that indicates who is "first". A date column or an identity column or something.
我将不得不假设您有某种标识符来指示谁是“第一”。日期列或标识列或其他东西。
In my example, I've done it with an order_id identify column.
在我的示例中，我使用 order_id 标识列完成了它。
CREATE TABLE products (
order_id MEDIUMINT NOT NULL AUTO_INCREMENT,
prod char(2),
cust char(2),
qty int,
PRIMARY KEY (order_id)
);
INSERT INTO products (prod, cust, qty) VALUES
('p1', 'c1', 5),
('p1', 'c2', 10),
('p2', 'c1', 2),
('p3', 'c2', 8);
And then to get your values run:
然后让你的价值观运行：
select p1.prod, p1.cust, p1.qty
from products p1
where not exists (select * from products p2
where p1.prod = p2.prod
and p2.order_id < p1.order_id)
where on each line you check to see if there are any other customers that ordered it earlier than you did. If there is an earlier order, then don't list this row. (Thus the not exists)
在每一行的哪个位置，您检查是否有其他客户比您更早订购。如果有更早的订单，则不要列出该行。（因此not exists）
This is mysql syntax, btw, which you say you're migrating to. (Access experts would have to edit this appropriately.)
这是 mysql 语法，顺便说一句，你说你要迁移到它。（访问专家必须适当地编辑它。）
Now, if you don't have a column identifying what designates the sequence of when orders were entered, you need one. Any schemes that rely upon implicit row_numbering based upon order of insertion will fall apart eventually, since the "first row" is not guaranteed to remain the same.
现在，如果您没有一列标识什么指定订单输入的顺序，那么您需要一个。任何依赖于基于插入顺序的隐式 row_numbering 的方案最终都会崩溃，因为不能保证“第一行”保持不变。
回答by JKirchartz
if you only want the first result add LIMIT 1to your query, or use SELECT DISTINCTto get unique results.
如果您只想将第一个结果添加LIMIT 1到您的查询中，或者用于SELECT DISTINCT获取唯一结果。
as for your join check this out:
至于您的加入，请查看：
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
this will get all the rows with a matching namefrom tableA and tableB.
这name将从 tableA 和 tableB 中获取所有匹配的行。
EDITJ Cooper is right, ms-access doesn't have an equivalent to LIMIT. the closest is TOPbut I don't think that will help. sorry, haven't used access since college.
编辑J Cooper 是对的，ms-access 没有相当于LIMIT. 最接近的是，TOP但我认为这不会有帮助。抱歉，自从大学以来就没有使用过访问权限。
回答by David Peden
"First" and min() are not the same. If you truly want first, try this:
"First" 和 min() 不一样。如果你真的想要先，试试这个：
declare @source table
(
prod varchar(10),
cust varchar(10),
qty int
)
insert into @source (prod, cust, qty) values ('p1', 'c1', 5)
insert into @source (prod, cust, qty) values ('p1', 'c2', 10)
insert into @source (prod, cust, qty) values ('p2', 'c1', 2)
insert into @source (prod, cust, qty) values ('p3', 'c2', 8)
select * from @source
declare @target table
(
prod varchar(10),
cust varchar(10),
qty int
)
insert into @target (prod)
select distinct prod from @source
update @target
set
cust = s.cust,
qty = s.qty
from @source s
join @target t on t.prod = s.prod
select * from @target
