Bir ustundagi qiymatdan kelib chiqqan holda, guruhning boshqa ustunidagi qiymatni o'zgartiring

SQL Server uchun yangi.

Menda stol bor:

Group   id     DEPT     marks         
--------------------------------------
 A      001      CS      P                  
 A      002     ECE      P  
 A      003     MC       P  
 B      561     CS       F  
 B      781     IT       F  
 C      789     CS       F  
 C      003     CS       F

Agar bo'lim muvaffaqiyatsiz bo'lsa, "yangi ustun" bo'limi o'z ichiga olishi mumkin. Failures "F" qiymatiga ega bo'lishi kerak Bu yerda CS CS ni o'z ichiga oladi, shuning uchun butun dept o'z ichiga "F" qiymatiga ega bo'ladi boshqa bo'limlar kabi  Masalan: - ECE tarkibiga kiradi, shuning uchun "P" ddepcontainfailures Ushbu tanlov bayonotini bajarishga harakat qilaman

SELECT group,  
      id,  
    dept,  
      marks,  
CASE WHEN marks='P' THEN  'P'   
            ELSE 'F'  
            END Ddeptcontainfailure  
 FROM Depttable

Men chiqadigan chiqim


Group   id     DEPT     marks            Ddeptcontainfailures
-----------------------------------------------------
 A      001     CS       P                  p   
 A      002     ECE      P                  P  
 A      003     MC       P                  P  
 B      561     CS       F                  F  
 B      781     IT       F                  F  
 C      789     CS       P                  p  
 C      003     CS       P                  p  

Bunga qanday erishish mumkin?

Kerakli chiqish


Group   id     DEPT     marks      Ddeptcontainfailures
-----------------------------------------------------
 A      001     CS       P          F       
 A      002     ECE      P          P  
 A      003     MC       P          P  
 B      561     CS       F          F  
 B      781     IT       F          F  
 C      789     CS       P          F  
 C      003     CS       P          F  
2

6 javoblar

Buni bajarish uchun siz MIN bilan foydalanishingiz mumkin. Bu sizning ishoralaringiz uchun faqat ikkita qiymatga ega bo'lgani uchun ishlaydi. Haqiqiy ma'lumotlaringiz boshqa funktsiyalarga ega bo'lsa, siz MIN funktsiyasi ichida iboralarni ishlatishingiz mumkin.

declare @Something table
(
    MyGroup char(1)
    , id char(3)
    , DEPT varchar(3)
    , marks char(1)
)

insert @Something values
('A', '001', 'CS', 'P')
, ('A', '002', 'ECE', 'P')
, ('A', '003', 'MC', 'P')
, ('B', '561', 'CS', 'F')
, ('B', '781', 'IT', 'F')
, ('C', '789', 'CS', 'F')
, ('C', '003', 'CS', 'F')

select s.*
    , min(marks) over(partition by DEPT)
from @Something s
order by s.MyGroup
    , s.id
    , s.DEPT
5
qo'shib qo'ydi
ThankS, u ishladi
qo'shib qo'ydi muallif PEL1, manba

cte tuzilishi bilan boshqa muqobil echim:

create table #temp
(
    MyGroup char(1)
    , id char(3)
    , DEPT varchar(3)
    , marks char(1)
)

insert #temp values
('A', '001', 'CS', 'P')
, ('A', '002', 'ECE', 'P')
, ('A', '003', 'MC', 'P')
, ('B', '561', 'CS', 'F')
, ('B', '781', 'IT', 'F')
, ('C', '789', 'CS', 'F')
, ('C', '003', 'CS', 'F')

;with cte as (
select distinct t.DEPT
from #temp t
where t.marks = 'F'
)
select t.*, (case when t.DEPT in (select DEPT from cte) then 'F' else 'T' end)
from #temp t
0
qo'shib qo'ydi

Shu bilan bir qatorda:

SELECT [group],  
      id,  
    Depttable.dept,  
      marks,  
CASE WHEN isnull(failures, 0) = 0 THEN  'P'   
            ELSE 'F'  
            END Ddeptcontainfailure  
 FROM Depttable
 LEFT OUTER JOIN (
    select count(*) as failures, alias.dept
    from Depttable alias
    where marks='f'
    group by alias.dept
 ) X on x.dept = Depttable.dept
0
qo'shib qo'ydi

Do'stim buni qilishning bir necha yo'li bor

Eng oson usullardan biri pastki so'rovlarni qo'llashdir

SELECT GROUP
      ,ID
      ,DEPT
      ,MARKS
      ,CASE WHEN DP.DEPT IS NULL THEN 'P' ELSE 'F' END AS DDEPTCONTAINFAILURE
FROM DEPTTABLE DT
LEFT JOIN (SELECT DISTINCT DEPT 
            FROM DEPTTABLE
            WHERE MARKS LIKE 'F') DP 
ON (DT.DEPT = DP.DEPT) 

Agar biror bo'limni bilishni istasangiz, unda siz qaerdan bilasiz. U holda siz bayroqni ishlatishingiz mumkin, mening holimda DP.DEPT NULL-ni bilish uchun JAVOBni ishlataman

Omad

0
qo'shib qo'ydi
SELECT group,  
      id,  
    dept,  
      marks,  
CASE WHEN 'F' in (select marks from Depttable di where di.marks=do.marks)    THEN  'F'   
            ELSE 'P'  
            END Ddeptcontainfailure  
 FROM Depttable do

yoki

SELECT group,  
      id,  
    dept,  
      marks,  
(select min(marks) from Depttable di where di.marks=do.marks) Ddeptcontainfailure  
 FROM Depttable do
0
qo'shib qo'ydi

Sizning ustuningiz Ddeptcontain bajarilmasa, sizda faqat 2 ta qiymat bo'ladi, shuning uchun siz bu so'rovdan foydalanishingiz mumkin

    declare @temp table(Grou varchar(10), id varchar(10), Dept varchar(10), marks varchar(10))

    insert into @temp (Grou, id, dept, marks)
    select 'A', '001' ,'CS' , 'P'
    union
    select 'A', '002' ,'ECE', 'P'
     union
    select 'A', '003' ,'MC', 'P'
     union
    select 'B', '561' ,'CS', 'F'
     union
    select 'B', '781' ,'IT', 'F'
     union
    select 'C', '789' ,'CS', 'F'
     union
    select 'C', '003' ,'CS', 'F'


    select t.Grou, t.id, t.Dept, t.marks, (select top 1 B.marks from @temp B where B.Dept = t.Dept order by B.marks) as Ddeptcontainfailures

    from @temp t

B.marks tomonidan saralashda "F" har doim natijada birinchi bo'lib keladi

0
qo'shib qo'ydi