Select TB_ICKaBiao.ID,TB_ICKaBiao.ICKaHao,TB_ICKaBiao.ICKaLeiXing,TB_ICKaBiao.ICKaZhiKaRiQi,
ICKaZhuangTai,TB_ICKaBiao.Remark ,dbo.TB_SheDingShuJuBiao.Shedingshuju as ickl,TB_SheDingShuJuBiao.Shedingshuju as zhuangt
From TB_ICKaBiao,TB_SheDingShuJuBiao
Where TB_ICKaBiao.ICKaLeiXing=TB_SheDingShuJuBiao.ID or TB_ICKaBiao.ICKaZhuangTai=TB_SheDingShuJuBiao.ID
查询结果是两条,求合并成一条!
1 B001 252 2012-01-01 00:00:00.000 254 NULL 固定卡 固定卡
1 B001 252 2012-01-01 00:00:00.000 254 NULL 空置 空置
5 个解决方案
#1
SELECT
a.ID ,
a.ICKaHao ,
a.ICKaLeiXing ,
a.ICKaZhiKaRiQi ,
a.ICKaZhuangTai ,
a.Remark ,
b.Shedingshuju AS ickl ,
b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a
CROSS APPLY
(
SELECT TOP(1) * FROM TB_SheDingShuJuBiao m
WHERE m.ID = a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b
#2
你好,查询出结果是一条,但是ickl和zhuangt相同了, ickl是上面的252 在TB_SheDingShuJuBiao中对应表ID zhuangt是上面的254对应TB_SheDingShuJuBiao中的id
#3
请问LZ想要的合并成一条的结果是什么?
这样的吗?
1 B001 252 2012-01-01 00:00:00.000 254 NULL 固定卡,固定卡 空置,空置
这样的吗?
1 B001 252 2012-01-01 00:00:00.000 254 NULL 固定卡,固定卡 空置,空置
#4
给数据和你想要的结果。
#5
上面那位大侠代码改进下就可以了
SELECT a.ID,a.ICKaHao,a.ICKaLeiXing,a.ICKaZhiKaRiQi,a.ICKaZhuangTai,a.Remark , b.Shedingshuju AS ickl ,b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a CROSS APPLY (
SELECT TOP(1) *
FROM TB_SheDingShuJuBiao m
WHERE (m.ID =a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai) and m.Shedingshuju<>a.Shedingshuju and m.Shedingshuju<>a.Shedingshuju
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b
SELECT a.ID,a.ICKaHao,a.ICKaLeiXing,a.ICKaZhiKaRiQi,a.ICKaZhuangTai,a.Remark , b.Shedingshuju AS ickl ,b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a CROSS APPLY (
SELECT TOP(1) *
FROM TB_SheDingShuJuBiao m
WHERE (m.ID =a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai) and m.Shedingshuju<>a.Shedingshuju and m.Shedingshuju<>a.Shedingshuju
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b
#1
SELECT
a.ID ,
a.ICKaHao ,
a.ICKaLeiXing ,
a.ICKaZhiKaRiQi ,
a.ICKaZhuangTai ,
a.Remark ,
b.Shedingshuju AS ickl ,
b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a
CROSS APPLY
(
SELECT TOP(1) * FROM TB_SheDingShuJuBiao m
WHERE m.ID = a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b
#2
你好,查询出结果是一条,但是ickl和zhuangt相同了, ickl是上面的252 在TB_SheDingShuJuBiao中对应表ID zhuangt是上面的254对应TB_SheDingShuJuBiao中的id
#3
请问LZ想要的合并成一条的结果是什么?
这样的吗?
1 B001 252 2012-01-01 00:00:00.000 254 NULL 固定卡,固定卡 空置,空置
这样的吗?
1 B001 252 2012-01-01 00:00:00.000 254 NULL 固定卡,固定卡 空置,空置
#4
给数据和你想要的结果。
#5
上面那位大侠代码改进下就可以了
SELECT a.ID,a.ICKaHao,a.ICKaLeiXing,a.ICKaZhiKaRiQi,a.ICKaZhuangTai,a.Remark , b.Shedingshuju AS ickl ,b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a CROSS APPLY (
SELECT TOP(1) *
FROM TB_SheDingShuJuBiao m
WHERE (m.ID =a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai) and m.Shedingshuju<>a.Shedingshuju and m.Shedingshuju<>a.Shedingshuju
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b
SELECT a.ID,a.ICKaHao,a.ICKaLeiXing,a.ICKaZhiKaRiQi,a.ICKaZhuangTai,a.Remark , b.Shedingshuju AS ickl ,b.Shedingshuju AS zhuangt
FROM TB_ICKaBiao a CROSS APPLY (
SELECT TOP(1) *
FROM TB_SheDingShuJuBiao m
WHERE (m.ID =a.ICKaLeiXing OR m.ID = a.ICKaZhuangTai) and m.Shedingshuju<>a.Shedingshuju and m.Shedingshuju<>a.Shedingshuju
ORDER BY (CASE WHEN m.ID = a.ICKaLeiXing THEN 0 ELSE 1 END)
) b