时间戳

首页 > 解决方案 > MSSQL：使用 DISTINCT，没有 GROUP BY 子句的 TOP 选择

sql - MSSQL：使用 DISTINCT，没有 GROUP BY 子句的 TOP 选择

问题描述

我想使用没有 GROUP BY 子句的 DISTINCT 和 TOP 选择。

我想从 [locations] 中选择行并按与 [location_photos] 中的每个位置相关联的行数对结果进行排序

我现在有了这个（甚至不使用 TOP）：

SELECT distinct(l.id),count(lp.locationid) OVER(partition by l.id) AS photos
,ISNULL(lp.locpath,'') as thumb
,l.id,l.title
FROM locations l
LEFT JOIN location_photos lp on lp.locationid=l.id
ORDER BY photos desc

然而，这会为 [location_photos] (56,000) 中的每一行返回一行，这应该是 [locations] (12,000) 中的潜在最大行数。

我已经在这里和这里检查过了。

如何使用 DISTINCT 和 TOP 选择不同的行数？

DDL 和数据

CREATE TABLE [dbo].[locations](
    [id] [int] NOT NULL,
    [title] [nvarchar](500) NOT NULL,
    [createdate] [datetime] NULL,
 CONSTRAINT [PK_locs] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[locations] ADD  CONSTRAINT [DF_homes_createdate]  DEFAULT (getdate()) FOR [createdate]
GO

CREATE TABLE [dbo].[location_photos](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [locationid] [int] NOT NULL,
    [locpath] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_location_photos] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[location_photos]  WITH CHECK ADD  CONSTRAINT [FK_locs_photos_homes] FOREIGN KEY([locationid])
REFERENCES [dbo].[locations] ([id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[location_photos] CHECK CONSTRAINT [FK_locs_photos_homes]
GO



INSERT INTO [locations](id,title) VALUES (1,'new york')
INSERT INTO [locations](id,title) VALUES (2,'boston')
INSERT INTO [locations](id,title) VALUES (3,'chicago')
INSERT INTO [locations](id,title) VALUES (4,'los angeles')


INSERT INTO [location_photos](locationid,locpath) VALUES (1,'nyc1')
INSERT INTO [location_photos](locationid,locpath) VALUES (1,'nyc2')
INSERT INTO [location_photos](locationid,locpath) VALUES (1,'nyc3')
INSERT INTO [location_photos](locationid,locpath) VALUES (1,'nyc4')
INSERT INTO [location_photos](locationid,locpath) VALUES (1,'nyc5')

INSERT INTO [location_photos](locationid,locpath) VALUES (2,'boston1')
INSERT INTO [location_photos](locationid,locpath) VALUES (2,'boston2')
INSERT INTO [location_photos](locationid,locpath) VALUES (2,'boston3')
INSERT INTO [location_photos](locationid,locpath) VALUES (2,'boston4')

--there are not photos for chicago on purpose

INSERT INTO [location_photos](locationid,locpath) VALUES (4,'la1')
INSERT INTO [location_photos](locationid,locpath) VALUES (4,'la2')

标签： sqlsql-servercountsubqueryleft-join

解决方案

我想从 [locations] 中选择行并按与 [location_photos] 中的每个位置相关联的行数对结果进行排序

您可以在子句中使用相关子查询ORDER BY：

select l.*
from locations l
order by (select count(*) 
          from location_photos lp
          where lp.location_id = l.id
         ) desc;

如果您想要查询中的计数以及排序标准，只需将子查询移动到select：

select l.*,
       (select count(*) 
        from location_photos lp
        where lp.location_id = l.id
       ) as num_photos
from locations l
order by num_photos desc;

推荐阅读