java - SQL 请求和循环结果最佳模式

Question

我真的不知道一个好的标题，但我问自己对于以下场景什么会被认为是“好的”

Table Place:id | name | address | etc

Table Tag:id | tag_name | score

Table Place_Tagid | place_id | tag_id | numberX

For example, my application allow a user to search a place based on a tag. the result should contain the name of the place as well as the top 3 tag for that place (ordered by numberX)

My question is what is the best way to achieve this in Java using SQL ?

1) Select p.name, t.tag_name FROM Place p 
LEFT JOIN ... GROUP BY p.name,t.tag_name ORDER BY p.id,pt.numberX DESC

然后在 Java 中，我循环遍历结果(注意 ORDER BY p.id)，创建一些逻辑以仅获取前 3 个标记，当我检测到 n+1 id 不同时，这意味着我正在处理下一个位置。

2) places = SELECT DISTINCT p.name FROM..... ORDER BY p.id
   tags = SELECT ... FROM Place_Tag pt WHERE pt.place_id IN (places.ids)

我进行了两个单独的 SQL 调用，一个是为了获取位置，一个是为了获取标签，然后我找到某种逻辑在 Java 中将两者链接在一起

3) (this is the easier but I think the worst)  
   places = Select p.name FROM Place...

然后我循环遍历各个位置并调用数据库以获取其标签(这意味着如果我循环 100 个位置，我每次都会进行 100 个额外调用..)

4) ? any other pattern ?

有更多经验的人可以告诉我这件事吗？

谢谢!

Answer 1

在 SQL Server 端执行尽可能多的操作将意味着需要通过网络从数据库服务器传输到应用程序服务器的数据更少。

这应该足够接近您的场景，尽管我为每个地方带来了前 3 个标签。要根据标签搜索获取前 3 名，只需添加 Where。

这是一个摆弄它:http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1/54

首先，我将创建表格并为示例添加数据:

DECLARE @Place TABLE (
    id int
    , name varchar(10)
    , address varchar(20)
)
DECLARE @Tag TABLE (
    id int
    , tag_name varchar(10)
    , score int
)
DECLARE @Place_Tag TABLE (
    id int IDENTITY (1,1)
    , place_id int
    , tag_id int
    , numberX int
)

INSERT INTO @Place VALUES (1,'place1','add1')
INSERT INTO @Place VALUES (2,'place2','add2')
INSERT INTO @Place VALUES (3,'place3','add3')
INSERT INTO @Place VALUES (4,'place4','add4')

INSERT INTO @Tag VALUES (1,'tag1',3)
INSERT INTO @Tag VALUES (2,'tag2',2)
INSERT INTO @Tag VALUES (3,'tag3',5)
INSERT INTO @Tag VALUES (4,'tag4',4)
INSERT INTO @Tag VALUES (5,'tag5',1)
INSERT INTO @Tag VALUES (6,'tag6',5)
INSERT INTO @Tag VALUES (7,'tag7',3)
INSERT INTO @Tag VALUES (8,'tag8',2)
INSERT INTO @Tag VALUES (9,'tag9',3)
INSERT INTO @Tag VALUES (10,'tag10',4)
INSERT INTO @Tag VALUES (11,'tag11',1)
INSERT INTO @Tag VALUES (12,'tag12',4)

INSERT INTO @Place_Tag VALUES (1,1,14)
INSERT INTO @Place_Tag VALUES (1,2,19)
INSERT INTO @Place_Tag VALUES (1,3,7)
INSERT INTO @Place_Tag VALUES (1,4,12)
INSERT INTO @Place_Tag VALUES (2,5,1)
INSERT INTO @Place_Tag VALUES (2,6,15)
INSERT INTO @Place_Tag VALUES (3,7,7)
INSERT INTO @Place_Tag VALUES (3,8,6)
INSERT INTO @Place_Tag VALUES (3,9,2)
INSERT INTO @Place_Tag VALUES (3,10,9)
INSERT INTO @Place_Tag VALUES (3,11,11)
INSERT INTO @Place_Tag VALUES (3,12,3)

还有查询。有一个按地点分区的子查询，因为它按 numberX 降序排序，所以外部查询将允许外部查询基于 numberX 获取每个地点的前 n 个标签:

Select Sub.name, Sub.tag_name
FROM (
    SELECT p.name
        , t.tag_name
        , ROW_NUMBER() OVER (PARTITION BY p.id ORDER BY pt.numberX DESC) rowNum
    FROM @Place p
    LEFT JOIN @Place_Tag pt ON p.id = pt.place_id
    LEFT JOIN @Tag t ON pt.tag_id = t.id
) Sub
WHERE Sub.rowNum <= 3