这两sql语句怎么合并?
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0
order by jfybb_zgid,jfybb_monthselect * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0
order by jfybb_zgid,jfybb_month
谢谢了!!!!!
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0
order by jfybb_zgid,jfybb_monthselect * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0
order by jfybb_zgid,jfybb_month
谢谢了!!!!!
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0
order by jfybb_zgid,jfybb_month
union all
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0
order by jfybb_zgid,jfybb_month
???
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7,8,9,10,11,12) and jfybb_cxhj>=0
order by jfybb_zgid,jfybb_month ??
select * from tb_jfybb
where jfybb_year=2007 and (jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0 ) or (jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0)
order by jfybb_zgid,jfybb_month
where jfybb_year=2007 and (jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0 ) or (jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0)
order by jfybb_zgid,jfybb_month这个显示出来的数据有别的年份的
select * from tb_jfybb
where (jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0 ) or (jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0)
order by jfybb_zgid,jfybb_month
1 333 10 20 2007 1 10.5 7 3.5 8
2 333 10 20 2007 2 10.5 7 3.5 8
3 333 10 20 2007 3 10.5 7 3.5 8
4 333 10 20 2007 4 10.5 7 3.5 8
5 333 10 20 2007 5 10.5 7 3.5 8
6 333 10 20 2007 6 10.5 7 3.5 8
7 333 10 20 2007 7 83.3 37.8 45.5 8
8 333 10 20 2007 8 20.9 11.4 9.5 8
9 333 10 20 2007 9 20.9 11.4 9.5 8
10 333 10 20 2007 10 20.9 11.4 9.5 8
11 333 10 20 2007 11 20.9 11.4 9.5 8
12 333 10 20 2007 12 20.9 11.4 9.5 8
21 333 10 20 2007 1 10.5 7 3.5 8
22 333 10 20 2007 2 10.5 7 3.5 8
23 333 10 20 2007 3 10.5 7 3.5 8
24 333 10 20 2007 4 10.5 7 3.5 8
25 333 10 20 2007 5 10.5 7 3.5 8
26 333 10 20 2007 6 10.5 7 3.5 8
27 333 10 20 2007 7 83.3 37.8 45.5 8
28 333 10 20 2007 8 0 0 0 8
29 333 10 20 2007 9 0 0 0 8
30 333 10 20 2007 10 0 0 0 8
31 333 10 20 2007 11 0 0 0 8
22 333 10 20 2007 12 0 0 0 8
------------------------------------------------------------
sql 查询显示出下面的结果
21 333 10 20 2007 1 10.5 7 3.5 8
22 333 10 20 2007 2 10.5 7 3.5 8
23 333 10 20 2007 3 10.5 7 3.5 8
24 333 10 20 2007 4 10.5 7 3.5 8
25 333 10 20 2007 5 10.5 7 3.5 8
26 333 10 20 2007 6 10.5 7 3.5 8
27 333 10 20 2007 7 83.3 37.8 45.5 8
-----------------------------------
以上的sql都不对
21 4 10 20 2007 1 10.5 7 3.5 8
22 4 10 20 2007 2 10.5 7 3.5 8
23 4 10 20 2007 3 10.5 7 3.5 8
24 4 10 20 2007 4 10.5 7 3.5 8
25 4 10 20 2007 5 10.5 7 3.5 8
26 4 10 20 2007 6 10.5 7 3.5 8
27 4 10 20 2007 7 83.3 37.8 45.5 8
显示这样 上面的id写错了
where jfybb_year=2007 and jfybb_month >=1 jfybb_month <=12 and and jfybb_cxhj>0
order by jfybb_zgid,jfybb_month
select * from (
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0
union all
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0
) tab
order by jfybb_zgid,jfybb_month
select * from tb_jfybb
where jfybb_year=2007
and jfybb_month >=1
and jfybb_month <=12
and jfybb_cxhj>0
order by jfybb_zgid,jfybb_month
这样显示的是下面的数据
bb_id zgid gwjb zggl year month cxhj qybc gr dwid
1 333 10 20 2007 1 10.5 7 3.5 8
2 333 10 20 2007 2 10.5 7 3.5 8
3 333 10 20 2007 3 10.5 7 3.5 8
4 333 10 20 2007 4 10.5 7 3.5 8
5 333 10 20 2007 5 10.5 7 3.5 8
6 333 10 20 2007 6 10.5 7 3.5 8
7 333 10 20 2007 7 83.3 37.8 45.5 8 21 333 10 20 2007 1 10.5 7 3.5 8
22 333 10 20 2007 2 10.5 7 3.5 8
23 333 10 20 2007 3 10.5 7 3.5 8
24 333 10 20 2007 4 10.5 7 3.5 8
25 333 10 20 2007 5 10.5 7 3.5 8
26 333 10 20 2007 6 10.5 7 3.5 8
27 333 10 20 2007 7 83.3 37.8 45.5 8 我要得到的是下面的数据 也就是不显示1-12月都有数据的结果 只显示1-7月交了 8-12月没交的所有记录
bb_id zgid gwjb zggl year month cxhj qybc gr dwid
21 333 10 20 2007 1 10.5 7 3.5 8
22 333 10 20 2007 2 10.5 7 3.5 8
23 333 10 20 2007 3 10.5 7 3.5 8
24 333 10 20 2007 4 10.5 7 3.5 8
25 333 10 20 2007 5 10.5 7 3.5 8
26 333 10 20 2007 6 10.5 7 3.5 8
27 333 10 20 2007 7 83.3 37.8 45.5 8
where jfybb_year=2007 and (jfybb_month in (1,2,3,4,5,6,7)or jfybb_month in (8,9,10,11,12) )and jfybb_cxhj>=0
order by jfybb_zgid,jfybb_month ?????
where (jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7)andjfybb_cxhj>0) or (jfybb_year=2007 and jfybb_month in (8,9,10,11,12) )and jfybb_cxhj=0 )
order by jfybb_zgid,jfybb_month 不知道可以不》》》????
declare @t1 table (bb_id int,zgid int,gwjb int,zggl int,year int,month int,cxhj numeric(3,1),qybc numeric(3,1),gr numeric(3,1),dwid int)
insert into @t1
select 1,333,10,20,2007,1,10.5,7,3.5,8 union all
select 2,333,10,20,2007,2,10.5,7,3.5,8 union all
select 3,333,10,20,2007,3,10.5,7,3.5,8 union all
select 4,333,10,20,2007,4,10.5,7,3.5,8 union all
select 5,333,10,20,2007,5,10.5,7,3.5,8 union all
select 6,333,10,20,2007,6,10.5,7,3.5,8 union all
select 7,333,10,20,2007,7,83.3,37.8,45.5,8 union all
select 8,333,10,20,2007,8,0,11.4,9.5,8 union all
select 9,333,10,20,2007,9,20.9,11.4,9.5,8 union all
select 10,333,10,20,2007,10,20.9,11.4,9.5,8 union all
select 11,333,10,20,2007,11,20.9,11.4,9.5,8 union all
select 12,333,10,20,2007,12,0,11.4,9.5,8
--查询
select * from @t1
where year=2007 and (month in (1,2,3,4,5,6,7) and cxhj>0 ) or (year=2007 and month in (8,9,10,11,12) and cxhj=0)
order by zgid,month
--结果
/*
1 333 10 20 2007 1 10.5 7.0 3.5 8
2 333 10 20 2007 2 10.5 7.0 3.5 8
3 333 10 20 2007 3 10.5 7.0 3.5 8
4 333 10 20 2007 4 10.5 7.0 3.5 8
5 333 10 20 2007 5 10.5 7.0 3.5 8
6 333 10 20 2007 6 10.5 7.0 3.5 8
7 333 10 20 2007 7 83.3 37.8 45.5 8
8 333 10 20 2007 8 0.0 11.4 9.5 8
12 333 10 20 2007 12 0.0 11.4 9.5 8
*/
where jfybb_year=2007 and ((jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0 ) or (jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0))
order by jfybb_zgid,jfybb_month
where jfybb_year=2007 and jfybb_month in (1,2,3,4,5,6,7) and jfybb_cxhj>0
order by jfybb_zgid,jfybb_month
union all
select * from tb_jfybb
where jfybb_year=2007 and jfybb_month in (8,9,10,11,12) and jfybb_cxhj=0
order by jfybb_zgid,jfybb_month
-->测试数据
declare @tb table
(
bb_id int,
zgid int,
gwjb int,
zggl int,
[year] int,
[month] int,
cxhj numeric(3,1),
qybc numeric(3,1),
gr numeric(3,1),
dwid int
)
insert into @tb
select 1,333,10,20,2007,1,10.5,7,3.5,8 union all
select 2,333,10,20,2007,2,10.5,7,3.5,8 union all
select 3,333,10,20,2007,3,10.5,7,3.5,8 union all
select 4,333,10,20,2007,4,10.5,7,3.5,8 union all
select 5,333,10,20,2007,5,10.5,7,3.5,8 union all
select 6,333,10,20,2007,6,10.5,7,3.5,8 union all
select 7,333,10,20,2007,7,83.3,37.8,45.5,8 union all
select 8,333,10,20,2007,8,0,11.4,9.5,8 union all
select 9,333,10,20,2007,9,20.9,11.4,9.5,8 union all
select 10,333,10,20,2007,10,20.9,11.4,9.5,8 union all
select 11,333,10,20,2007,11,20.9,11.4,9.5,8 union all
select 12,333,10,20,2007,12,0,11.4,9.5,8 union all
select 21,444,10, 20,2007,1,10.5,7, 3.5, 8 union all
select 22,444,10, 20,2007,2,10.5,7, 3.5, 8 union all
select 23,444,10, 20,2007,3,10.5,7, 3.5, 8 union all
select 24,444,10, 20,2007,4,10.5,7, 3.5, 8 union all
select 25,444,10, 20,2007,5,10.5,7, 3.5, 8 union all
select 26,444,10, 20,2007,6,10.5,7, 3.5, 8 union all
select 27,444,10, 20,2007,7,83.3 , 37.8,45.5,8 union all
select 28,444,10, 20,2007,8,0, 0, 0,8 union all
select 29,444,10, 20,2007,9,0, 0, 0,8 union all
select 30,444,10, 20,2007,10,0, 0, 0,8 union all
select 31,444,10, 20,2007,11,0, 0, 0,8 union all
select 22,444,10, 20,2007,12,0, 0, 0,8 -->查询
select *
from @tb t
where [year]=2007
and [month] between 1 and 7
and cxhj>0
and not exists(select * from @tb where zgid=t.zgid and gwjb=t.gwjb and zggl=t.zggl and [year]=t.[year] and [month] between 8 and 12 and cxhj>0)
-->结果
bb_id zgid gwjb zggl year month cxhj qybc gr dwid
----------- ----------- ----------- ----------- ----------- ----------- ----- ----- ----- -----------
21 444 10 20 2007 1 10.5 7.0 3.5 8
22 444 10 20 2007 2 10.5 7.0 3.5 8
23 444 10 20 2007 3 10.5 7.0 3.5 8
24 444 10 20 2007 4 10.5 7.0 3.5 8
25 444 10 20 2007 5 10.5 7.0 3.5 8
26 444 10 20 2007 6 10.5 7.0 3.5 8
27 444 10 20 2007 7 83.3 37.8 45.5 8(所影响的行数为 7 行)