显示的记录只有

问题描述：

我有我使用T-SQL（光标&环）。（SQL服务器2005）已解决的问题显示的记录只有

但是我正在寻找解决方案使用SQL。

我有一个列主表说MasterRecord（全部是独一无二的，VARCHAR类型和PK）

MasterRecord 
------------ 
MRecord1 
MRecord2 
MRecord3 
MRecord4 
................ 
.................. 
MRecord[n]

现在主从表有两列MasterRecord（VARCHAR & FK）和DetailRecord（VARCHAR ）

MasterRecord   DetailRecord 
--------------------------------------------- 
MRecord1    MRecord1_DetailRecord1 
MRecord1    MRecord1_DetailRecord2 
MRecord1    MRecord1_DetailRecord3 
MRecord1    MRecord1_DetailRecord4 
MRecord2    MRecord2_DetailRecord1 
MRecord2    MRecord2_DetailRecord2 
MRecord2    MRecord2_DetailRecord3 
MRecord2    MRecord2_DetailRecord4 
............................................... 
................................................ 
MRecord[n]   MRecord[n] _DetailRecord1 
MRecord[n]   MRecord[n] _DetailRecord2 
MRecord[n]   MRecord[n] _DetailRecord3 
MRecord[n]   MRecord[n] _DetailRecord4

其中[N]可以是任何数目

的问题是，对于每个唯一的主记录，我应该取前2详细记录

O/P：

MasterRecord   DetailRecord 
--------------------------------------------- 
MRecord1    MRecord1_DetailRecord1 
MRecord1    MRecord1_DetailRecord2 
MRecord2    MRecord2_DetailRecord1 
MRecord2    MRecord2_DetailRecord2 
MRecord3    MRecord3_DetailRecord1 
MRecord3    MRecord3_DetailRecord2 
............................................... 
.............................................. 
MRecord[n]   MRecord[n] _DetailRecord1 
MRecord[n]   MRecord[n] _DetailRecord2

希望我解释清楚我的问题。

请让我知道了进一步澄清。

，你在哪里要在主详细的格式来显示呢？什么语言/框架？ .NET/JAVA/VB6？的WinForms/asp.net/Silverlight的？ – shahkalpesh 2009-07-06 03:37:42

这是SQL Server 2005.我想在datagrid中显示它。它是Asp.net应用程序。但是这个问题只能在后端解决。我不应该在后面的代码中处理它（根据上级指令）。 – 2009-07-06 03:42:32

答

不知道，如果你想只是两个记录或记录有一，两个。

看看这里，让我知道。

DECLARE @Master TABLE(
     MasterRecordID VARCHAR(20) 
) 

INSERT INTO @Master (MasterRecordID) VALUES ('MASTER1') 
INSERT INTO @Master (MasterRecordID) VALUES ('MASTER2') 
INSERT INTO @Master (MasterRecordID) VALUES ('MASTER3') 
INSERT INTO @Master (MasterRecordID) VALUES ('MASTER4') 

DECLARE @MasterDetail TABLE(
     MasterRecordID VARCHAR(20), 
     MasterDetailRecord VARCHAR(50) 
) 

INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER4','MASTERDETAIL10') 

INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL09') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL08') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER3','MASTERDETAIL07') 

INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL06') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL05') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER2','MASTERDETAIL04') 

INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL03') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL02') 
INSERT INTO @MasterDetail (MasterRecordID,MasterDetailRecord) VALUES ('MASTER1','MASTERDETAIL01') 

DECLARE @MaxRecords INT 
SELECT @MaxRecords = 2 

SELECT md.MasterRecordID, 
     md.MasterDetailRecord 
FROM @MasterDetail md INNER JOIN 
     --this section ensures that we only return master records with at least MaxRecords as specified (2 in your case) 
     --if you wish to display al master records, with 1, 2 or MaxRecords, romove this section or see below 
     (
      SELECT MasterRecordID 
      FROM @MasterDetail 
      GROUP BY MasterRecordID 
      HAVING COUNT(MasterRecordID) >= @MaxRecords 
     ) NumberOfRecords ON md.MasterRecordID = NumberOfRecords.MasterRecordID INNER JOIN 
     @MasterDetail mdSmaller ON md.MasterRecordID = mdSmaller.MasterRecordID 
WHERE mdSmaller.MasterDetailRecord <= md.MasterDetailRecord 
GROUP BY md.MasterRecordID, 
      md.MasterDetailRecord 
HAVING COUNT(mdSmaller.MasterDetailRecord) <= @MaxRecords 
ORDER BY md.MasterRecordID, 
      md.MasterDetailRecord 



SELECT md.MasterRecordID, 
     md.MasterDetailRecord 
FROM @MasterDetail md INNER JOIN 
     --this will ensure that all master records will return with 1, 2 or MaxRecords 
     @MasterDetail mdSmaller ON md.MasterRecordID = mdSmaller.MasterRecordID 
WHERE mdSmaller.MasterDetailRecord <= md.MasterDetailRecord 
GROUP BY md.MasterRecordID, 
      md.MasterDetailRecord 
HAVING COUNT(mdSmaller.MasterDetailRecord) <= @MaxRecords 
ORDER BY md.MasterRecordID, 
      md.MasterDetailRecord

希望帮助

答

我没有时间现在写出完整的查询，但是您所做的是以主表开始并在详细表中加入两次。第一连接应匹配上面记录（其中的那些较小= 0的计数，为你的“较小”特定的定义）为每个主记录，并且第二联接应该匹配第二记录（其中那些的计数较小= 1 ）。

更新
当我仔细想想，你就必须做一个工会，让您的附加记录（仍然写相同的加入，但在一个完全独立的选择查询您在通过联合的结果包括：）。否则，您必须在同一条记录中返回第一个和第二个详细键的输出。

答

试试这个：

WITH cteCount as 
(
    Select 
     ROW_NUMBER() OVER(PARTITION BY MRecord ORDER BY MR_DETAIL_COLUMN) as TopCnt, 
     MR_DETAIL_COLUMN 
    FROM MASTER_DETAIL_TABLE 
) 
SELECT 
     * 
FROM MASTER_TABLE as MT 
JOIN cteCount as MDT ON MDT.MRecord = MT.MRecord 
WHERE TopCnt <= 2

编辑：正确拼写错字

编辑：纠正非常愚蠢的错误

相关推荐