怎么把下面两条sql写条一条sql
1.
select * from Table_Teacher as c where c.state between 2 and 4 and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and c.ets like '%et1%' order by c.state asc, c.addtime desc
结果显示3条2.
select * from Table_Teacher as c where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and (c.ets is null or c.ets not like '%es1%') order by c.state asc, c.addtime desc
结果显示124条3.我要怎么把这两个sql合并,写成一条sql语句
1.
select * from Table_Teacher as c where c.state between 2 and 4 and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and c.ets like '%et1%' order by c.state asc, c.addtime desc
结果显示3条2.
select * from Table_Teacher as c where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and (c.ets is null or c.ets not like '%es1%') order by c.state asc, c.addtime desc
结果显示124条3.我要怎么把这两个sql合并,写成一条sql语句
select * from Table_Teacher as c where c.state between 2 and 4 and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and c.ets like '%et1%' order by c.state asc, c.addtime desc
union all
select * from Table_Teacher as c where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and (c.ets is null or c.ets not like '%es1%') order by c.state asc, c.addtime desc
select * from Table_Teacher as c
where c.addby='chris' and
c.location in'Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and ((c.state between 2 and 4 and c.ets like '%et1%')
or (c.state in(1,2,10) and (c.ets is null or c.ets not like '%es1%')))
order by c.state asc, c.addtime desc
第一条找c.ets like '%et1%' 的记录
第二条又找c.ets not like '%et1%' 的记录。最后两个语句合起来等于条件,而且你的c.ets is null 也在c.ets not like '%et1%',这个已经是c.ets not like '%et1%'的子集,最后分析后等于直接可以用
select * from Table_Teacher as c where c.state between 2 and 4 and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
order by c.state asc, c.addtime desc
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and c.ets like '%et1%' order by c.state asc, c.addtime desc)
union all
(select * from Table_Teacher as c where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and (c.ets is null or c.ets not like '%es1%') order by c.state asc, c.addtime desc)
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and c.ets like '%et1%'
union all
select * from Table_Teacher as c where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and (c.ets is null or c.ets not like '%es1%')
order by c.state asc, c.addtime desc
select * from Table_Teacher c
where c.state in(1,2,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and ((c.ets is null or c.ets not like '%es1%') or c.ets like '%et1%')
order by c.state asc, c.addtime desc
where c.state in(1,2,3,4,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and ((c.ets is null or c.ets not like '%es1%') or c.ets like '%et1%')
order by c.state asc, c.addtime desc
借8楼的花,献个佛
select * from Table_Teacher c
where c.state in(1,2,3,4,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
--and ((c.ets is null or c.ets not like '%es1%') or c.ets like '%--et1%')去掉此处的条件 这个and的逻辑结果肯定是1 不是0
order by c.state asc, c.addtime desc
where c.state in(1,2,3,4,10) and c.addby='chris' and c.location in
('Chongqing','Guangdong','Guangxi','Hubei','Jiangxi','Ningxia','Qinghai','Sichuan','Yunnan',' ')
and ((c.ets is null or c.ets not like '%es1%') or c.ets like '%et1%')
order by c.state asc, c.addtime desc