SQL按条件祛重语句

[好好学习29]

各位大神：
--假如有这样一个结果集，
--如果NewId相同，且其中一个state等于5，
--就不取这两条数据，
--如果两个不包含5，就把取isChildInstance等于0的数据取出来
请问怎么取呀。

[二月十六]

是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([NewId] int,[state] int,[isChildInstance] int)
Insert #T
select 1,4,0 union all
select 1,5,1 union all
select 9,4,0 union all
select 9,2,1
Go
--测试数据结束
SELECT
    *
FROM
    #T b
WHERE
    NOT EXISTS
    (
        SELECT
            *
        FROM
            #T a
        WHERE
            a.[NewId] = b.[NewId]
            AND a.state = 5
    )
    AND b.isChildInstance = 0;

[吉普赛的歌]

楼主的意思有点模糊， 最好是贴一下 实际的数据和结果， 数据最好是文本， 不要截图。 这个是猜的：

select * from t as a where not exists (
    select * from t as b where a.newid=b.newid and ( a.state=5 or b.state=5 )
)
and isChildInstance=0
and exists(
    select * from t as c where 
               a.主键!=b.主键 
        and a.newid=b.newid 
        and a.newid like '%5%'
        and b.newid like '%5%'
)

[好好学习29]

引用 2 楼 yenange 的回复:

楼主的意思有点模糊， 最好是贴一下 实际的数据和结果， 数据最好是文本， 不要截图。 这个是猜的：

select * from t as a where not exists (
    select * from t as b where a.newid=b.newid and ( a.state=5 or b.state=5 )
)
and isChildInstance=0
and exists(
    select * from t as c where 
               a.主键!=b.主键 
        and a.newid=b.newid 
        and a.newid like '%5%'
        and b.newid like '%5%'
)

我结合了您上次的回帖和这次的新问题的回答，写了个SQL,不知道不对不对

;
WITH cte
AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid,
           *
    FROM
    (
        SELECT Sort,
               Color,
               EJLX,
               SJLX,
               CreatedTime,
               NewsName,
               IsRead,
               State,
               NewsID,
               IsChildInstance
        FROM
        (
            SELECT t3.Sort AS Sort,
                   t1.ObjectID AS NewsID,
                   t2.Color AS Color,
                   t1.Title AS NewsName,
                   t1.EJLX AS EJLX,
                   t1.SJLX AS SJLX,
                   t1.XXNR,
                   t1.CreatedTime AS CreatedTime,
                   ROW_NUMBER() OVER (ORDER BY t1.CreatedTime DESC) AS ROWID,
                   (CASE
                        WHEN t4.ObjectID IS NULL THEN
                            0
                        ELSE
                            1
                    END
                   ) IsRead,
                   Instance.State,
                   Instance.IsChildInstance
            FROM I_XXFBLC AS t1
                LEFT JOIN I_SJLXBD AS t2
                    ON t1.YJLX = t2.YJLXSJMX
                       AND t1.EJLX = t2.EJLXSJ
                       AND t1.SJLX = t2.SJLXSJ
                LEFT JOIN OT_InstanceContext AS Instance
                    ON t1.ObjectID = Instance.BizObjectId
                LEFT JOIN I_YJLEBD AS t3
                    ON t1.YJLX = t3.YJLXSJ
                LEFT JOIN B_Portal_NoticeReadLog AS t4
                    ON t1.ObjectID = t4.NewsID
                       AND t4.UserID = '94c9dafd-59a9-407d-9f15-4863db5d8bc2'
            WHERE t3.Sort = 3
                  AND t1.EJLX = '鸿坤地产集团'
                  AND t1.IsDisable = '否'
        ) T
    ) K )
SELECT TOP 10
       *
FROM cte b
WHERE NOT EXISTS
(
    SELECT * FROM cte a WHERE a.NewsID = b.NewsID AND (a.State = 5 OR b.State=5)
)
      AND b.IsChildInstance = 0
      AND b.State = 4
ORDER BY b.CreatedTime DESC;