选择Total和If Read

Question

我有以下表格：

书

Id Title 
1  Test

BookPage (BookId对应于Book表中的Id )

Id BookId Page
1  1      1
1  1      2

BookUserPage (BookPageId对应BookPage表中的Id )

UserId BookPageId
1      1
2      2
3      1

我的select查询如下：

SELECT B.[Id], B.[Title], BP.[Id], BP.[BookId], BP.[Page], COUNT(BUP.[BookpageId]) AS Total
FROM [Book] B
    LEFT OUTER JOIN [BookPage] BP ON BP.[BookId] = B.[Id]
    LEFT OUTER JOIN [BookUserPage] BUP ON BUP.[BookPageId] = BP.[Id]
WHERE B.[Id] = 1
GROUP BY B.[Id], B.[Title], BP.[Id], BP.[BookId], BP.[Page]

我得到的结果如下：

Id, Title, Id, BookId, Page, Total
1   Test   1,  1,      1,    2
1   Test   2,  1,      1,    1

我正在尝试修改查询，以便它也能告诉我用户读取了2个页面中的哪一个。

我尝试过以下几种方法：

SELECT B.[Id], B.[Title], BP.[Id], BP.[BookId], BP.[Page], COUNT(BUP.[BookpageId]) AS Total,
CASE WHEN EXISTS (
    SELECT BUP2.[UserId] 
    FROM [PollUserAnswer] BUP2
    WHERE BUP2.[UserId] = '98ad813b-cd0e-4a63-b40a-e09ee84f4b96')
THEN 1
ELSE 0
END AS Voted
FROM [Book] B
    LEFT OUTER JOIN [BookPage] BP ON BP.[BookId] = B.[Id]
    LEFT OUTER JOIN [BookUserPage] BUP ON BUP.[BookPageId] = BP.[Id]
WHERE B.[Id] = 1
GROUP BY B.[Id], B.[Title], BP.[Id], BP.[BookId], BP.[Page]

但是上面的代码在我的结果中的两行都是1。我还尝试在Case语句中添加一个条件：

AND BUP2.[BookPageId] = BUP.[PageId]

但由于group by的原因，这不能工作，我不能将其列在group by中，因为它是一个子查询。

我对用户1和3的期望输出是这样的：

Id, Title, Id, BookId, Page, Total, Read
1   Test   1,  1,      1,    2,     1
1   Test   2,  1,      1,    1,     0

我对用户2的期望输出是这样的：

Id, Title, Id, BookId, Page, Total, Read
1   Test   1,  1,      1,    2,     0
1   Test   2,  1,      1,    1,     1

注意:请忽略查询输出中有2个Id列的事实。

Answer 1

我将连接到单独的子查询，以查找阅读给定页面的用户总数。然后，您只需将另一个左连接添加到BookUserPage上，即可生成Read列：

SELECT b.Id, b.Title, bp.Id, bp.BookId, bp.Page, bup1.total,
    CASE WHEN bup2.UserId IS NULL THEN 0 ELSE 1 END AS [Read]
FROM Book b
LEFT JOIN BookPage bp
    ON bp.BookId = b.Id
LEFT JOIN
(
    SELECT BookPageId, COUNT(*) AS total
    FROM BookUserPage
    GROUP BY BookPageId
) bup1
    ON bup1.BookPageId = bp.Id
LEFT JOIN BookUserPage bup2
    ON bup2.BookPageId = bp.Id AND bup2.UserId = 1
WHERE b.Id = 1;

​

Demo

​

这不会生成您期望的结果，但是考虑到您的实际样本数据和连接逻辑，它在逻辑上似乎是正确的。