如何根据一个表中一列的唯一性值从两个表中进行选择
本文关键字:两个 选择 行选 何根 一个 一列 唯一性 | 更新日期: 2023-09-27 18:22:41
我有两个表:第一个(案例)第二(评论)
一对多关系;我将每个案例的评论存储在评论表中。
我需要从评论表中显示的案例表中选择案例信息,但我希望每个案例都显示一次,按评论添加日期(cDate)排序
我试过了:
SELECT TOP 10
Cases.*,
comments.cDate
FROM
Cases
INNER JOIN comments
ON Cases.Case_ID = comments.Case_ID
WHERE comments.Case_ID IN
(
SELECT DISTINCT
Case_ID
FROM
comments
)
ORDER BY cDate DESC
但是如果它有许多评论,它会多次检索案例。我需要它只出现一次
谢谢大家,你们帮了很多忙,,我刚刚添加了
Cases.Case_ID IN (SELECT Case_ID FROM comments)
而且效果很好。
Select语句现在是这样的:
SELECT top 10 Cases.*,
(SELECT MAX(comments.cDate)
FROM comments
WHERE Cases.Case_ID = comments.Case_ID ) AS cDate
FROM Cases
WHERE Cases.Case_ID
IN (SELECT Case_ID FROM comments)
ORDER BY cDate DESC
再次感谢:)
在这种情况下,您似乎想要使用子查询:
SELECT top 10 Cases.*,
(SELECT MAX(comments.cDate) FROM comments
WHERE Cases.Case_ID = comments.Case_ID ) AS cDate
FROM Cases
ORDER BY cDate DESC
这样做:
SELECT TOP 10
A.* ,
(SELECT MAX(C.cDate) FROM comments C WHERE C.Case_ID = A.Case_ID) commDate
FROM Cases A
INNER JOIN comments B ON A.Case_ID = B.Case_ID
ORDER BY commDate
(@yhw42查询将返回没有注释的案例。)
第一部分测试数据:
DECLARE @tblCases TABLE(Case_ID INT)
DECLARE @comments TABLE(Case_ID INT,cDate DATETIME)
INSERT INTO @tblCases
SELECT 1 UNION ALL SELECT 2
INSERT INTO @comments
SELECT 1,GETDATE() UNION ALL
SELECT 1,GETDATE()-1 UNION ALL
SELECT 2,GETDATE()-2 UNION ALL
SELECT 2,GETDATE()-3
然后我会这样做。
;WITH CTE AS
(
SELECT
RANK() OVER(PARTITION BY Case_ID ORDER BY cDate DESC) AS iRank,
tbl.cDate,
tbl.Case_ID
FROM
@comments AS tbl
)
SELECT TOP 10
Cases.*,
CTE.cDate
FROM
CTE
JOIN @tblCases AS Cases
ON Cases.Case_ID=CTE.Case_ID
WHERE
CTE.iRank=1
谢谢大家,你们帮了我很多,我刚刚添加了
Cases.Case_ID IN (SELECT Case_ID FROM comments)
而且效果很好。
Select语句现在是这样的:
SELECT top 10 Cases.*,
(SELECT MAX(comments.cDate)
FROM comments
WHERE Cases.Case_ID = comments.Case_ID ) AS cDate
FROM Cases
WHERE Cases.Case_ID
IN (SELECT Case_ID FROM comments)
ORDER BY cDate DESC
再次感谢:)