MasterTable
ID Name Npid
1 aaa 12345
2 bbb 12111
3 ccc 26666
DealtailTable
Npid Standard PAY point
12345 B 88 D1121
12111 B 89 r4445
14444 B 80 p9998
26666 A 0 k4444
27777 B 70 g6666select M.* ,D.point FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='B' AND D.PAY>90
需求是:如果上面的sql找不到point值,就要把where的条件换为 D.Standard='A' (也就是根据 D.Standard='B'优先去找,找不到需要的值时,就根据另外的一个条件去找)
我的想法是:
select M.* FROM MasterTable M,DealtailTable D on M.ID=D.MID
WHERE CASE(D.Standard)
when D.Standard='B'
then (D.Standard='B' AND D.PAY>90)
else D.Standard='A' 这句sql是错的,但这是我想要表达的意思。
ID Name Npid
1 aaa 12345
2 bbb 12111
3 ccc 26666
DealtailTable
Npid Standard PAY point
12345 B 88 D1121
12111 B 89 r4445
14444 B 80 p9998
26666 A 0 k4444
27777 B 70 g6666select M.* ,D.point FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='B' AND D.PAY>90
需求是:如果上面的sql找不到point值,就要把where的条件换为 D.Standard='A' (也就是根据 D.Standard='B'优先去找,找不到需要的值时,就根据另外的一个条件去找)
我的想法是:
select M.* FROM MasterTable M,DealtailTable D on M.ID=D.MID
WHERE CASE(D.Standard)
when D.Standard='B'
then (D.Standard='B' AND D.PAY>90)
else D.Standard='A' 这句sql是错的,但这是我想要表达的意思。
WHERE D.Standard='B' AND D.PAY>90
union all
select M.* ,D.point
FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='A' and not exists(select 1 FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='B' AND D.PAY>90)
但 好像不是我要的结果。你的sql是说:select M.* ,D.point FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='B' AND D.PAY>90找不到记录时,才根据D.Standard='A'去找。我想要的是:
join DealtailTable 时 :
Standard='B'时,where条件就是(D.Standard='B' AND D.PAY>90)Standard='A'时,where条件就是Standard='A'
這一段是處理結果1沒有結果集時的判斷
and not exists(select 1 FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE D.Standard='B' AND D.PAY>90)
如果无,则根据标准2计算
and D.Standard='B' AND D.PAY>90
union all
select M.* ,D.point
FROM #MasterTable M,#DealtailTable D where M.Npid=D.npid
and D.Standard='A'
and not exists(select 1 FROM #MasterTable M,#DealtailTable D where M.Npid=D.npid
and D.Standard='B' AND D.PAY>90)
select M.* ,D.point
FROM MasterTable M,DealtailTable D on M.Npid=D.npid
WHERE case when exists(select 1 FROM MasterTable M,DealtailTable D on M.Npid=D.npid WHERE D.Standard='B' AND D.PAY>90) then D.Standard='B' else D.Standard='A' end
--如果不行的话,得得用if else