oracle语句如下:
select a.total_tag, a.total_insp_tag,
a.total_insp_times, a.total_pass,
a.total_one_pass, decode(b.criteria, ALL_TYPE, '所有', c.domain_value_name) type_list
from temp_total a,
stat_search_criteria b,
domain_value_info c
where b.criteria = to_char(c.id_domain_value(+))
and b.criteria = a.type_list(+)
and b.id = a.time_stamp(+)
and b.id = v_time_stamp
and a.inspection_type(+) = ASM_INSPECTION
and ((b.criteria2 = stat_and_analyse.OTHER_OIL and c.code_domain = pkg_base_data.TYPE_OIL)
or b.criteria = ALL_TYPE)
order by b.order_field1
现在将它转换为sql server下面的sql该怎么写
select a.total_tag, a.total_insp_tag,
a.total_insp_times,a.total_pass,a.total_one_pass,
(case when b.criteria = '*ALL*' then '所有' else c.domain_value_name end) type_list
from veims.stat_search_criteria b left outer join veims.domain_value_info c on b.criteria = cast(c.id_domain_value as varchar)
left outer join veims.temp_total a on b.criteria = a.type_list and b.id = a.time_stamp and b.id = '888'
right outer join veims.temp_total on a.inspection_type = 'ASM' and
((b.criteria2 = '1' and c.code_domain = 'OIL') or b.criteria = '*ALL*') order by b.order_field1
是不是那样转换呀
可出来的数据好像不对,出来的结果如下:
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL我用
select a.total_tag,a.total_insp_tag,a.total_insp_times,a.total_pass,a.total_one_pass,(case when b.criteria = '*ALL*' then '所有' else c.domain_value_name end) type_list
from veims.stat_search_criteria b
left outer join veims.domain_value_info c on b.criteria = cast(c.id_domain_value as varchar)
left outer join veims.temp_total a on b.criteria = a.type_list and b.id = a.time_stamp and b.id = '888'
/*right outer join veims.temp_total on*/ and a.inspection_type = 'ASM' and
((b.criteria2 = '1' and c.code_domain = 'OIL') or b.criteria = '*ALL*')
order by b.order_field1
出来的结果如下:
NULL NULL NULL NULL NULL 汽油
NULL NULL NULL NULL NULL 柴油
NULL NULL NULL NULL NULL 电动
应该为上面结果但不要柴油才对
select a.total_tag, a.total_insp_tag,
a.total_insp_times, a.total_pass,
a.total_one_pass, decode(b.criteria, ALL_TYPE, '所有', c.domain_value_name) type_list
from temp_total a,
stat_search_criteria b,
domain_value_info c
where b.criteria = to_char(c.id_domain_value(+))
and b.criteria = a.type_list(+)
and b.id = a.time_stamp(+)
and b.id = v_time_stamp
and a.inspection_type(+) = ASM_INSPECTION
and ((b.criteria2 = stat_and_analyse.OTHER_OIL and c.code_domain = pkg_base_data.TYPE_OIL)
or b.criteria = ALL_TYPE)
order by b.order_field1
现在将它转换为sql server下面的sql该怎么写
select a.total_tag, a.total_insp_tag,
a.total_insp_times,a.total_pass,a.total_one_pass,
(case when b.criteria = '*ALL*' then '所有' else c.domain_value_name end) type_list
from veims.stat_search_criteria b left outer join veims.domain_value_info c on b.criteria = cast(c.id_domain_value as varchar)
left outer join veims.temp_total a on b.criteria = a.type_list and b.id = a.time_stamp and b.id = '888'
right outer join veims.temp_total on a.inspection_type = 'ASM' and
((b.criteria2 = '1' and c.code_domain = 'OIL') or b.criteria = '*ALL*') order by b.order_field1
是不是那样转换呀
可出来的数据好像不对,出来的结果如下:
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL我用
select a.total_tag,a.total_insp_tag,a.total_insp_times,a.total_pass,a.total_one_pass,(case when b.criteria = '*ALL*' then '所有' else c.domain_value_name end) type_list
from veims.stat_search_criteria b
left outer join veims.domain_value_info c on b.criteria = cast(c.id_domain_value as varchar)
left outer join veims.temp_total a on b.criteria = a.type_list and b.id = a.time_stamp and b.id = '888'
/*right outer join veims.temp_total on*/ and a.inspection_type = 'ASM' and
((b.criteria2 = '1' and c.code_domain = 'OIL') or b.criteria = '*ALL*')
order by b.order_field1
出来的结果如下:
NULL NULL NULL NULL NULL 汽油
NULL NULL NULL NULL NULL 柴油
NULL NULL NULL NULL NULL 电动
应该为上面结果但不要柴油才对
a.total_insp_times,a.total_pass,
a.total_one_pass,
case b.criteria when ALL_TTYPE then '??' else c.domain_value_name end type_list
from temp_total a
left join stat_search_criteria b on b.criteria=a.type_list and b.id=a.time_stamp
left join domain_value_info c on cast(c.id_domain_value as nvarchar(30)) = b.criteria
where b.id = v_time_stamp and a.inspection_type = ASM_INSPECTION
and ((b.criteria2 = stat_and_analyse.OTHER_OIL and c.code_domain = pkg_base_data.TYPE_OIL)
or b.criteria = ALL_TYPE)
order by b.order_field1
select a.total_tag,a.total_insp_tag,a.total_insp_times,a.total_pass,a.total_one_pass,(case when b.criteria = '*ALL*' then '所有' else c.domain_value_name end) type_list
from veims.stat_search_criteria b
left outer join veims.domain_value_info c on b.criteria = cast(c.id_domain_value as varchar)
left outer join veims.temp_total a on b.criteria = a.type_list and b.id = a.time_stamp and b.id = '888'
/*right outer join veims.temp_total on*/ and a.inspection_type = 'ASM' and
((b.criteria2 = '1' and c.code_domain = 'OIL') or b.criteria = '*ALL*')
order by b.order_field1
效果一样呀