如何将基于分区的所有列值与ID进行比较

I have a case where there are many rows for the same ID, I just wanted to know if there is any way to find out whether the given ID includes some specific value or not.

我有一个例子，同一个ID有很多行，我只是想知道是否有任何方法可以找出给定的ID是否包含某些特定的值。

Table 1

ID      Dept      Salary       Flag    Date 
1        IT        5000         N      2017-01-01
1        IT        5000         N      2017-01-02
1        IT        5000         N      2017-01-03
1        IT        5000         N      2017-01-04
1        IT        5000         N      2017-01-05
2        HR        4000         N      2017-01-01
2        HR        4000         N      2017-01-02
2        HR        4000         Y      2017-01-03
2        HR        4000         N      2017-01-04
2        HR        4000         N      2017-01-05
3        Fin       4500         N      2017-01-08
3        Fin       4500         N      2017-01-09
3        Fin       4500         N      2017-01-10
3        Fin       4500         N      2017-01-11
3        Fin       4500         N      2017-01-12
4        Edu       4800         N      2017-02-10
4        Edu       4800         N      2017-02-11
4        Edu       4800         N      2017-02-12
4        Edu       4800         Y      2017-02-13
4        Edu       4800         N      2017-02-14
4        Edu       4800         N      2017-02-15

Expected Result:

ID      Dept      Salary       Flag    Date 
1        IT        5000         N      2017-01-01
1        IT        5000         N      2017-01-02
1        IT        5000         N      2017-01-03
1        IT        5000         N      2017-01-04
1        IT        5000         N      2017-01-05

3        Fin       4500         N      2017-01-08
3        Fin       4500         N      2017-01-09
3        Fin       4500         N      2017-01-10
3        Fin       4500         N      2017-01-11
3        Fin       4500         N      2017-01-12

As IT and Fin don't have Y flag, in any row, I just want this result to be displayed. Is there any way I can find out this information.

因为它和Fin没有Y标志，在任何一行中，我只想显示这个结果。有什么办法我能查到这些信息吗?

5 个解决方案

#1

try this

试试这个

select * from @mytable
where id in (
select 
id from @mytable
group by id
having SUM(case when flag='N' then 1 else 0 end) =COUNT(*))

#2

I think it sample like this:

我认为它的样本是这样的:

SELECT * FROM table WHERE ID NOT IN
(
    SELECT ID FROM table WHERE FLAG ='Y'
);

#3

You could use DENSE_RANK() function to allow ranking based on column Dept, Flag

可以使用DENSE_RANK()函数根据列Dept、Flag进行排序

SELECT * FROM <table> WHERE ID IN
(
       SELECT A.ID FROM 
       (
          SELECT *, DENSE_RANK() OVER (order by Dept, Flag) [RN] FROM <table>
       ) A GROUP BY A.ID HAVING COUNT(DISTINCT RN) = 1)

EDIT : If don't want to use of DENSE_RANK() function you could also use of simple case expression condition

编辑:如果不想使用DENSE_RANK()函数，也可以使用简单的大小写表达条件

SELECT * FROM <table> WHERE ID IN
(
    SELECT ID FROM <table> GROUP BY ID
    HAVING COUNT(DISTINCT CASE WHEN Flag = 'N' THEN 1 ELSE 0 END) = 1
);

Other simple way :

其他简单的方法:

SELECT * FROM <table> WHERE ID NOT IN
(
    SELECT DISTINCT ID FROM <table> WHERE FLAG ='Y'
);

Result :

结果:

ID  Dept    Salary  Flag        Date
1   IT      5000    N           2017-01-01
1   IT      5000    N           2017-01-02
1   IT      5000    N           2017-01-03
1   IT      5000    N           2017-01-04
1   IT      5000    N           2017-01-05
3   Fin     4500    N           2017-01-08
3   Fin     4500    N           2017-01-09
3   Fin     4500    N           2017-01-10
3   Fin     4500    N           2017-01-11
3   Fin     4500    N           2017-01-12

#4

WITH Flaggen AS 
(
select distinct ID
from Table1
where Flag <> 'Y'
)

select * 
from Table1 
join Flaggen 
on Flaggen.Id = Table1.ID

#5

-1

Hey @Avi as you only want to display the records from the table just use where clause with id then u will get the output as expected

嘿，@Avi，因为您只想显示来自表的记录，只需使用where子句和id，那么您将得到预期的输出

select id,Dept,Salary,Flag,Date 
from mytable
where id in (1,3)

Hope tis might help.

希望这可以帮助。

#1