为什么查询在更改select语句时显示不同的结果（要显示的列） [英] Why query showing different result while changing just select statement (Columns to show)

问题描述

有一个名为'NetworkSettings'的表格

There is a table called 'NetworkSettings'

CREATE TABLE [dbo].[NetworkSettings](
    [MpnMappingId] [bigint] IDENTITY(1,1) NOT NULL,
    [PartNumber] [nvarchar](50) NULL,
    [MPN] [nvarchar](50) NOT NULL,
    [Network] [int] NOT NULL,
    [Description] [nvarchar](500) NULL,
    [System] [nvarchar](100) NULL,
    [Damaged] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [MpnMappingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

现在，如果我们执行以下两个quires：

Now, if we execute below two quires:

SELECT Network FROM
  (
   SELECT
    ROW_NUMBER() OVER (PARTITION BY MPN ORDER BY MPN) AS RowNum,
    MpnMappingId, PartNumber, MPN, Network, [Description], [System], Damaged
   FROM
    NetworkSettings
  ) AS t
  WHERE
   t.RowNum = 1

和下面的查询

and query below

SELECT * FROM
  (
   SELECT
    ROW_NUMBER() OVER (PARTITION BY MPN ORDER BY MPN) AS RowNum,
    MpnMappingId, PartNumber, MPN, Network, [Description], [System], Damaged
   FROM
    NetworkSettings
  ) AS t
  WHERE
   t.RowNum = 1

两者在网络列中显示不同的结果。但没有数量相同。



任何人都可以说出理由吗？此外，在两种情况下SQL查询如何运行并显示不同的结果？

Both shows different results in Network column. but no of count are same.

Can any one tell reason for it? Also, in both case how SQL query runs and shows differnt results?

推荐答案

我相信您依赖于两个查询以相同顺序呈现的行，但你只是要求MPN订购，这不是唯一的。



在你的内部查询订单中，由MpnMappingId提供。您可以随时修改外部查询以按MPN排序。

I believe that you are relying on the rows being presented in the same order for both queries, but you are only demanding order by MPN which is not unique.

In your inner query order by MpnMappingId. You can always amend the outer query to order by MPN.

这篇关于为什么查询在更改select语句时显示不同的结果（要显示的列）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！