如果记录是重复的，则跳过除1行匹配特定条件之外的所有其他记录 [英] If Record is Duplicate Skip All The Other Record except 1 Row Matching Specific Condition

问题描述

我需要将每个控件的权限设置为特定用户或用户组......



我使用此查询查询SQL SERVER

I've Requirement Where I Set Permissions of Each Control to Particular USER OR GROUP of USERS....

I'm Querying SQL SERVER with This Query

SELECT type,
       pagecontrolmaster.controlnameid,
       pagecontrolmaster.controltype,
       pagecontroltransaction.visible,
       pagecontroltransaction.enabled,
       pagecolumnmaster.columnno
FROM   dbo.pagecontrolmaster
       INNER JOIN dbo.pagedetails
               ON dbo.pagecontrolmaster.pageid = dbo.pagedetails.pageid
       INNER JOIN dbo.pagecontroltransaction
               ON dbo.pagecontrolmaster.controlid =
                  dbo.pagecontroltransaction.controlid
       INNER JOIN dbo.userlogindetails
               ON CASE
                    WHEN pagecontroltransaction.type = 'G'
                         AND pagecontroltransaction.forusergroupid =
                             userlogindetails.usergroupid
                  THEN 1
                    WHEN pagecontroltransaction.type = 'U'
                         AND pagecontroltransaction.forusergroupid =
                             userlogindetails.loginid THEN
                    1
                  END = 1
       LEFT OUTER JOIN dbo.pagecolumnmaster
                    ON dbo.pagecontroltransaction.columnid =
                       dbo.pagecolumnmaster.columnid
WHERE  ( dbo.userlogindetails.loginid = 130 )
       AND ( dbo.pagedetails.pagename = 'Lease Contract Master' )
GROUP  BY dbo.pagecontrolmaster.controlnameid,
          dbo.pagedetails.pagename,
          dbo.pagecontrolmaster.controltype,
          dbo.userlogindetails.loginid,
          visible,
          enabled,
          columnno,
          type
--HAVING    (Case When Count(ControlnameID)>1 And Type = 'U' Then 1
--				When Count(ControlnameID)=1 And Type = 'U'OR Type = 'G' Then 1 End =1)
ORDER  BY Max(dbo.pagecontroltransaction.type),
          dbo.pagecontrolmaster.controltype,
          dbo.pagecontrolmaster.controlnameid

哪位给我这样的记录

Which Gave Me Records Like This

---------------------------------------------------------------
TYPE    ControlNameID   Control Type  VISIBLE  ENABLE  ColumnNo
---------------------------------------------------------------
G	txtNameLeaseCo	TextBox	        1	0	NULL
U	tcMain	        TabContainer	0	1	1
U	txtNameLeaseCo	TextBox	        0	1	NULL
G	txtAddress	TextBox	        1	0	NULL

我想要结果如下

I want Results Like Below

---------------------------------------------------------------
TYPE    ControlNameID   Control Type  VISIBLE  ENABLE  ColumnNo
---------------------------------------------------------------
G   txtAddress	        TextBox	        1	  0	NULL
U   tcMain              TabContainer    0         1      1
U   txtNameLeaseCo      TextBox         0         1     NULL

差异在这两个结果之间是如果ControlNameID的计数> 1然后为那个特定的ControlNameID选择记录TYPE ='U'



你可以在我的查询中看到我试过使用案例（在评论部分中）有条款，但我遇到的问题是当我使用有计数（ControlNameID）> 1然后它返回无结果...

可以任何人解释我为什么有Count（ControlNameID）> 1在此查询中表现不佳以及如何获得我想要的结果.... :)

Difference Between This Two Results is that If Count of ControlNameID>1 Then Select Records for That particular ControlNameID who's TYPE='U'

You Can See in My Query That I Tried Using Case (in Commented Part)in Having Clause but the Problem I faced was When I'm using Having Count(ControlNameID)>1 Then It returns No Result...
Can Any One Explain me why Having Count(ControlNameID)>1 is not performing well In This Query and How Can I get My Desired Results.... :)

推荐答案

您可以尝试类似这个：

You could try something like this:

with aa as(
--put the original query here
	select 'G' Type, 'txtNameLeaseCo' ControlNameID, 'TextBox' [Control Type], 1 Visible, 0 Enable, NULL ColumnNo
	union all
	select 'U' Type, 'tcMain' ControlNameID, 'TextBox' [Control Type], 0 Visible, 1 Enable, 1 ColumnNo
	union all
	select 'U' Type, 'txtNameLeaseCo' ControlNameID, 'TextBox' [Control Type], 0 Visible, 1 Enable, NULL ColumnNo
	union all
	select 'G' Type, 'txtAddress' ControlNameID, 'TextBox' [Control Type], 1 Visible, 0 Enable, NULL ColumnNo
)
, bb as(
 select *,
 count(*) over (partition by ControlNameID) cnt
 from aa
)
select 
	Type,
	ControlNameID,
	[Control Type],
	Visible,
	Enable,
	ColumnNo
from bb
where cnt =  1
or (cnt = 2 and Type = 'U')
order by
	Type,
	ControlNameID
;

参考 SQL Server查询问题 [ ^ ] - 鼓舞人心的想法来自

Refer to SQL Server Query question[^] - where the inspirational idea came from

这篇关于如果记录是重复的，则跳过除1行匹配特定条件之外的所有其他记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！