女士对SQL Server的访问查询-DistinctRow [英] Ms Access query to SQL Server - DistinctRow

问题描述

将MS Access查询转换为可在SQL Server中运行的语法是什么，因为它没有DistinctRow关键字

What would the syntax be to convert this MS Access query to run in SQL Server as it doesn't have a DistinctRow keyword

UPDATE DISTINCTROW [MyTable] 
INNER JOIN [AnotherTable] ON ([MyTable].J5BINB = [AnotherTable].GKBINB) 
                          AND ([MyTable].J5BHNB = [AnotherTable].GKBHNB) 
                          AND ([MyTable].J5BDCD = [AnotherTable].GKBDCD) 
SET [AnotherTable].TessereCorso = [MyTable].[J5F7NR];

推荐答案

DISTINCTROW [MyTable]从结果中删除重复的MyTable条目.示例:

DISTINCTROW [MyTable] removes duplicate MyTable entries from the results. Example:

select distinctrow items 
  items.item_number, items.name
from items
join orders on orders.item_id = items.id;

尽管联接为一个以上的订单提供了相同的item_number和名称多次，但DISTINCTROW却将其减少为每个项目一行.因此，整个联接仅用于确保您仅选择存在至少一个订单的项目.据我所知，您在其他任何DBMS中都找不到DISTINCTROW.可能是因为不需要.在检查是否存在时，我们当然使用EXISTS(或者使用IN).

In spite of the join getting you the same item_number and name multiple times when there is more than one order for it, DISTINCTROW reduces this to one row per item. So the whole join is merely for assuring that you only select items for which exist at least one order. You don't find DISTINCTROW in any other DBMS as far as I know. Probably because it is not needed. When checking for existence, we use EXISTS of course (or IN for that matter).

您正在加入MyTable和AnotherTable，并由于某种原因希望获得相同的MyTable记录为一个AnotherTable记录的倍数，因此您使用DISTINCTROW仅获得一次.如果您为一条AnotherTable记录获得了两个不同 MyTable记录，则查询将(希望)失败.

You are joining MyTable and AnotherTable and expect for some reason to get the same MyTable record multifold for one AnotherTable record, so you use DISTINCTROW to only get it once. Your query would (hopefully) fail if you got two different MyTable records for one AnotherTable record.

此更新的作用是:

update anothertable
set tesserecorso = (select top 1 j5f7nr from mytable where mytable.j5binb = anothertable.gkbinb and ...)
where exists       (select *            from mytable where mytable.j5binb = anothertable.gkbinb and ...)

但这两次使用大约相同的子查询.因此，我们想从查询中进行更新.

But this uses about the same subquery twice. So we'd want to update from a query instead.

每<一些列>中获得一个结果记录的最简单方法标准SQL查询中的数据聚合:

The easiest way to get one result record per <some columns> in a standard SQL query is to aggregate data:

select *
from anothertable a
join 
(
  select j5binb, j5bhnb, j5bdcd, max(j5f7nr) as j5f7nr
  from mytable
  group by j5binb, j5bhnb, j5bdcd
) m on  m.j5binb = a.gkbinb and m.j5bhnb = a.gkbhnb and m.j5bdcd = a.gkbdcd;

如何编写可更新查询的方法与一个DBMS不同.这是SQL-Server的最终更新语句:

How to write an updateble query is different from one DBMS to another. Here is the final update statement for SQL-Server:

update a
set a.tesserecorso = m.j5f7nr
from anothertable a
join 
(
  select j5binb, j5bhnb, j5bdcd, max(j5f7nr) as j5f7nr
  from mytable
  group by j5binb, j5bhnb, j5bdcd
) m on m.j5binb = a.gkbinb and m.j5bhnb = a.gkbhnb and m.j5bdcd = a.gkbdcd;

这篇关于女士对SQL Server的访问查询-DistinctRow的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！