April 19, 2014 at 3:13 am
Create table T
(
id INT primary key identity(1,1),
A0 varchar(10),
A1 varchar(10),
A2 varchar(10),
b0 varchar(10),
b1 varchar(10),
b2 varchar(10),
C0 bit default 0,
C1 bit DEFAULT 0,
C2 bit default 0
)
insert into T
select '00','01','02','00','00','00',1,0,0 union
select '01','01','01','00','00','00',1,0,0 union
select '00','01','02','00','01','02',1,1,1 union
select '00','01','02','00','00','00',1,1,0 union
select '00','01','02','00','00','00',1,0,1 union
select '01','01','01','01','00','01',1,0,1 union
select '00','01','02','00','00','00',0,1,1 union
select '00','01','02','00','01','02',0,1,1
search rule is: when c0=1 and a0=b0 then print.
Where any C0 C1 C2 value is 1, the corresponding A = B, then print out
the last result is:
'00','01','02','00','00','00',1,0,0 --C0=1 A0=B0
'00','01','02','00','01','02',1,1,1 -- c1,c2=1 a1=b1 a2=b2
'01','01','01','01','00','01',1,0,1 -- c1,c3=1 a1=b1 a3=b3
'00','01','02','00','01','02',0,1,1 -- c2,c3=1 a2=b2,a3=b3
April 19, 2014 at 3:24 am
redcn2004 (4/19/2014)
search rule is: when c0=1 and a0=b0 then print.
the last result is:
'00','01','02','00','00','00',1,0,0 --C0=1 A0=B0
not sure I completely understand....for the first search rule if I run
SELECT id, A0, A1, A2, b0, b1, b2, C0, C1, C2
FROM T
WHERE (C0 = 1) AND (A0 = b0)
then I return 5 rows NOT the single row you show
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
April 19, 2014 at 3:32 am
when c0=1 and a0=b0 then print
when c0=1 and c1=1 and a0=b0 and a1=b1 then print
when c1=1 and a1=b1 then print
when c1=1 and c2=1 and a1=b1 and a2=b2 then print
....
when C Prefix colunm =1 and A Prefix = B Prefix then print
And my english is not very good, so :hehe:
April 19, 2014 at 4:27 am
redcn2004 (4/19/2014)
when c0=1 and a0=b0 then printwhen c0=1 and c1=1 and a0=b0 and a1=b1 then print
when c1=1 and a1=b1 then print
when c1=1 and c2=1 and a1=b1 and a2=b2 then print
....
when C Prefix colunm =1 and A Prefix = B Prefix then print
And my english is not very good, so :hehe:
Look at these code samples using the CASE statement:
SELECT * FROM (
SELECT
TT.id ,TT.A0 ,TT.A1 ,TT.A2 ,TT.b0
,TT.b1 ,TT.b2 ,TT.C0 ,TT.C1 ,TT.C2
,CASE
WHEN TT.C0 = 1 AND (TT.A0 = TT.b0) THEN 1
WHEN TT.C1 = 1 AND (TT.A1 = TT.b1) THEN 1
WHEN TT.C2 = 1 AND (TT.A2 = TT.b2) THEN 1
ELSE 0
END AS SHOW_FLAG
FROM T TT
) AS X WHERE X.SHOW_FLAG = 1;
GO
SELECT * FROM (
SELECT
TT.id ,TT.A0 ,TT.A1 ,TT.A2 ,TT.b0
,TT.b1 ,TT.b2 ,TT.C0 ,TT.C1 ,TT.C2
,CASE
WHEN TT.C0 = 1 AND (TT.A0 <> TT.b0) THEN 0
WHEN TT.C1 = 1 AND (TT.A1 <> TT.b1) THEN 0
WHEN TT.C2 = 1 AND (TT.A2 <> TT.b2) THEN 0
ELSE 1
END AS SHOW_FLAG
FROM T TT
) AS X WHERE X.SHOW_FLAG = 1;
GO
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply