如何比较sql server中列的多个值 [英] How to compare multiple values of column in sql server
问题描述
我有专栏...
prid isroi
12 y
12 N
13 n
14 y
15 n
15 n
如果isroi至少一个y为prid然后它必须显示'y'或'N'
输出将是
prid isroi new_roi
12 yy
12 N y
13 nn
14 yy
15 nn
15 nn
试试这个.. :)
; samp as (
< span class =code-keyword> select count(*) as CountPrid,prid from TableName 其中 isroi = ' y'group by prid
)
SELECT [prid]
,[isroi]
, case ( select CountPrid 来自 samp 其中 samp.prid = TableName.prid)当 1 然后 ' Y' ELSE ' N' END as prids
FROM TableName
这是SQL Server吗?
如果是这样,我认为它可以使用Common Table Expression(CTE)整齐地解决...
我们用你的s创建一个临时表充足的数据:
创建 表# tmp(prid int ,isroi varchar ( 1 ));
插入 进入 #tmp 选择 12 ,' y'跨度>;
插入 进入 #tmp 选择 12 ,' N';
插入 进入 #tmp 选择 13 ,' n';
插入 进入 #tmp 选择 14 ,' y';
插入 进入 #tmp 选择 15 ,' n';
插入 进入 #tmp 选择 15 ,' n';
选择 * 来自 #tmp;
我们知道我们想要一个记录集来收集所有prid,其中isroi的'y'值存在:
< span class =code-keyword>选择 distinct prid,isroi 来自 #tmp < span class =code-keyword>其中 isroi = ' y'
我们将此查询放在CommonTableExpression中,并将LEFT OUTER JOIN连接到SELECT * FROM原始表,使用ISNULL(,)将缺少的值替换为'n'< pre lang =SQL> CTEYesValues as ( select distinct prid,isroi 来自 #tmp 其中 isroi = ' y')
选择 #tmp。*,isnull(CTEYesValues.isroi,' n') as new_roi 来自 #tmp left outer join CTEYesValues on CTEYesValues.prid =#tmp.prid;
输出:
prid isroi new_roi
12 y y
12 N
13 n n
14 y y
15 n n
15 n n
好的,这是我的解决方案>
选择 prid,isroi,max(isroi) over ( partition by prid) as new_roi
来回m tmp
如果你在isroi栏中只有y和n值
那么我的解决方案将非常适合你/>
我的小提琴解决方案
>
I have to columns ...
prid isroi
12 y
12 N
13 n
14 y
15 n
15 n
if isroi has atleast one y for prid then it has to display 'y' or 'N'
Output will be
prid isroi new_roi
12 y y
12 N y
13 n n
14 y y
15 n n
15 n n
try this.. :)
;with samp as( select count(*)as CountPrid,prid from TableName where isroi='y' group by prid ) SELECT [prid] ,[isroi] ,case (select CountPrid from samp where samp.prid=TableName.prid) when 1 then 'Y' ELSE 'N' END as prids FROM TableName
Is this SQL Server?
If so, I think it can be solved neatly using a Common Table Expression (CTE)...
we create a temporary table with your sample data:
create table #tmp (prid int, isroi varchar(1)); insert into #tmp select 12,'y'; insert into #tmp select 12,'N'; insert into #tmp select 13,'n'; insert into #tmp select 14,'y'; insert into #tmp select 15,'n'; insert into #tmp select 15,'n'; select * from #tmp;
we know we want a recordset which collects all the "prid" where a 'y' value for "isroi" is present:
select distinct prid,isroi from #tmp where isroi = 'y'
We put this query in a CommonTableExpression, and LEFT OUTER JOIN it to a SELECT * FROM the original table, using ISNULL(,) to replace the missing values with an 'n'with CTEYesValues as (select distinct prid,isroi from #tmp where isroi = 'y') select #tmp.*, isnull(CTEYesValues.isroi, 'n') as new_roi from #tmp left outer join CTEYesValues on CTEYesValues.prid = #tmp.prid;
The output:
prid isroi new_roi 12 y y 12 N y 13 n n 14 y y 15 n n 15 n n
ok here it is my solution
select prid,isroi, max(isroi) over(partition by prid) as new_roi from tmp
if you have only y and n values in isroi column
then my solution will perfectly work for you
My Fiddle solution
这篇关于如何比较sql server中列的多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!