سلام،
این جواب میده؟
if object_id( 'dbo.MusicByKhadem' ) is not null
drop table dbo.MusicByKhadem;
go
create table dbo.MusicByKhadem
(
ID int identity(1,1) primary key,
PersonName nvarchar(100) not null,
StyleMusic nvarchar(100) not null,
LanguageMusic nvarchar(100) not null
);
go
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('None', 'None', 'None');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('DJ Khandem', 'None', 'None');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('None', 'Classical Metal', 'None');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('DJ Khandem', 'Classical Metal', 'None');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('None', 'None', 'Bolivian Lori');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('DJ Khandem', 'None', 'Bolivian Lori');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('None', 'Classical Metal', 'Bolivian Lori');
insert dbo.MusicByKhadem (PersonName, StyleMusic, LanguageMusic) values ('DJ Khandem', 'Classical Metal', 'Bolivian Lori');
declare @person nvarchar(100) = 'DJ Khandem';
declare @style nvarchar(100) = 'Classical Metal';
declare @language nvarchar(100) = 'Bolivian Lori';
select top 12
x.PersonMatch + x.StyleMatch + x.LanguageMatch as Score,
x.*
from
(
select
k.*,
case when k.PersonName = @person then 1 else 0 end as PersonMatch,
case when k.StyleMusic = @style then 1 else 0 end as StyleMatch,
case when k.LanguageMusic = @language then 1 else 0 end as LanguageMatch
from
dbo.MusicByKhadem k
where
k.PersonName = @person
or k.StyleMusic = @style
or k.LanguageMusic = @language
) x
order by
Score desc;