select * from kf_getrr a left join conchild b on a.id=b.kf_id left join xs_client c on a.kf_id=c.kh_id
好像可以用一個iif的函數吧
我想过用CASE来实现。 但我实际要用的字段要多得多。这里只是例举几个。
select kf_id = (case when b.roomcode=(select min(roomcode) from conchild where kf_id=b.kf_id) then a.kf_id else null end), a.kh_name, a.kh_sex, b.roomcode, b.kf_time, b.end_time, b.roomsum, c.kh_id, kh_name = (case when c.kh_id is null then a.kh_name else c.kh_name end), kh_sex = (case when c.kh_id is null then a.kh_sex else c.kh_sex end), c.kh_addr from kf_getrr a left join conchild b on a.id=b.kf_id left join xs_client c on a.kf_id=c.kh_id
可以实现一部分要求。Select a.kf_id, b.roomcode, b.kf_time, b.end_time, b.roomsum, c.kh_id, IsNull(c.kh_name,a.kh_name) As kh_name, IsNull(c.kh_sex,a.kh_sex) As kh_sex, c.kh_addr from kf_getrr a left join conchild b on a.id=b.kf_id left join xs_client c on a.kf_id=c.kh_id
全部的 Select (Case When Exists(Select 1 from conchild Where kf_id=a.id And roomcode<b.roomcode) Then '' Else a.kf_id End ) As kf_id, b.roomcode, b.kf_time, b.end_time, b.roomsum, c.kh_id, IsNull(c.kh_name,a.kh_name) As kh_name, IsNull(c.kh_sex,a.kh_sex) As kh_sex, c.kh_addr from kf_getrr a left join conchild b on a.id=b.kf_id left join xs_client c on a.kf_id=c.kh_id
from kf_getrr a
left join conchild b on a.id=b.kf_id
left join xs_client c on a.kf_id=c.kh_id
但我实际要用的字段要多得多。这里只是例举几个。
kf_id = (case when b.roomcode=(select min(roomcode) from conchild where kf_id=b.kf_id) then a.kf_id else null end),
a.kh_name,
a.kh_sex,
b.roomcode,
b.kf_time,
b.end_time,
b.roomsum,
c.kh_id,
kh_name = (case when c.kh_id is null then a.kh_name else c.kh_name end),
kh_sex = (case when c.kh_id is null then a.kh_sex else c.kh_sex end),
c.kh_addr
from
kf_getrr a
left join
conchild b
on
a.id=b.kf_id
left join
xs_client c
on
a.kf_id=c.kh_id
a.kf_id,
b.roomcode,
b.kf_time,
b.end_time,
b.roomsum,
c.kh_id,
IsNull(c.kh_name,a.kh_name) As kh_name,
IsNull(c.kh_sex,a.kh_sex) As kh_sex,
c.kh_addr
from kf_getrr a
left join conchild b on a.id=b.kf_id
left join xs_client c on a.kf_id=c.kh_id
Select
(Case When Exists(Select 1 from conchild Where kf_id=a.id And roomcode<b.roomcode) Then '' Else a.kf_id End ) As kf_id,
b.roomcode,
b.kf_time,
b.end_time,
b.roomsum,
c.kh_id,
IsNull(c.kh_name,a.kh_name) As kh_name,
IsNull(c.kh_sex,a.kh_sex) As kh_sex,
c.kh_addr
from kf_getrr a
left join conchild b on a.id=b.kf_id
left join xs_client c on a.kf_id=c.kh_id
在conchild表中同一个kf_id可能同一个房间可能有两次。
现在还剩两个小问题。
1。在conchild表中同一个kf_id可能同一个房间即roomcode可能有两次。
2.在conchild表中同一个kf_id的房间即roomcode并没有排序规律。