मैं ग्रुप बाय क्लॉज के बिना DISTINCT और 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] (५६,०००) में प्रत्येक पंक्ति के लिए एक पंक्ति देता है, जहां यह [स्थानों] (१२,०००) में पंक्तियों की संभावित अधिकतम संख्या होनी चाहिए।

मैंने पहले ही यहां और यहां

मैं DISTINCT और TOP का उपयोग करके पंक्तियों की अलग-अलग संख्या का चयन कैसे कर सकता हूं?

डीडीएल और डेटा

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')
-1
Adam 25 अक्टूबर 2020, 16:32

2 जवाब

सबसे बढ़िया उत्तर

मैं [स्थानों] से पंक्तियों का चयन करना चाहता हूं और परिणामों को क्रमबद्ध करना चाहता हूं कि पंक्तियों की संख्या [स्थान_फोटो] में प्रत्येक स्थान से जुड़ी हुई है।

आप 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;
2
Gordon Linoff 25 अक्टूबर 2020, 16:34

मुझे लगता है कि आप एकत्रीकरण चाहते हैं:

SELECT l.id, count(lp.locationid) AS cnt_photos, l.title
FROM locations l
LEFT JOIN location_photos lp on lp.locationid = l.id
GROUP BY l.id, l.title
ORDER BY cnt_photos desc

वैकल्पिक रूप से, आप मेल खाने वाली तस्वीरों को गिनने के लिए एक सबक्वायरी का उपयोग कर सकते हैं। पार्श्व जुड़ाव के साथ इसे करने का एक तरीका यहां दिया गया है:

select l.*, lp.*
from locations l
cross apply (select count(*) cnt_photos from location_photos lp where lp.locationid = l.id) lp
ORDER BY lp.cnt_photos desc
1
GMB 25 अक्टूबर 2020, 16:33