多数据表查找数据但无法得到筛选结果，请指点代码错误所在，谢谢!

RICHEER COCA 2015-05-14 11:22:13
建立表TB1、TB2、TB3、TB4
--建立表TB1

if object_id('tempdb..#tb1','U') is not null drop table #tb1

go 

create table #tb1(WNO nchar(2))  --检查 select * from  #tb1

go

insert into #tb1

SELECT'1'

UNION ALL SELECT'2'

UNION ALL SELECT'3'

UNION ALL SELECT'4'

UNION ALL SELECT'5'

UNION ALL SELECT'7'

UNION ALL SELECT'8'

UNION ALL SELECT'9'

UNION ALL SELECT'10'

UNION ALL SELECT'12'

--建立表TB2

if object_id('tempdb..#tb2','U') is not null drop table #tb2

go 

create table #tb2(WNO nchar(2),WValue int,BQY int)  --检查 select * from  #tb2

go

insert into #tb2

SELECT'1','3','1'

UNION ALL SELECT'2','4','2'

UNION ALL SELECT'3','10','3'

UNION ALL SELECT'4','1','4'

UNION ALL SELECT'5','3','1'

UNION ALL SELECT'6','15','2'

UNION ALL SELECT'7','1','3'

UNION ALL SELECT'8','2','4'

UNION ALL SELECT'9','6','1'

UNION ALL SELECT'10','5','2'

UNION ALL SELECT'11','12','3'

UNION ALL SELECT'12','7','4'

--建立表TB3

if object_id('tempdb..#TB3','U') is not null drop table #TB3

go 

create table #TB3(BLU varchar(5),BLU1 nchar(2),BLU2 nchar(2))  -- 检查 select * from  #TB3

go

insert into #TB3

SELECT'01 02 ','1	','2'

UNION ALL SELECT'01 03 ','1	','3'

UNION ALL SELECT'01 04 ','1	','4'

UNION ALL SELECT'01 05 ','1	','5'

UNION ALL SELECT'01 06 ','1	','6'

UNION ALL SELECT'01 07 ','1	','7'

UNION ALL SELECT'01 08 ','1	','8'

UNION ALL SELECT'01 09 ','1	','9'

UNION ALL SELECT'01 10 ','1	','10'

UNION ALL SELECT'01 11 ','1	','11'

UNION ALL SELECT'01 12 ','1	','12'

UNION ALL SELECT'02 03 ','2	','3'

UNION ALL SELECT'02 04 ','2	','4'

UNION ALL SELECT'02 05 ','2	','5'

UNION ALL SELECT'02 06 ','2	','6'

UNION ALL SELECT'02 07 ','2	','7'

UNION ALL SELECT'02 08 ','2	','8'

UNION ALL SELECT'02 09 ','2	','9'

UNION ALL SELECT'02 10 ','2	','10'

UNION ALL SELECT'02 11 ','2	','11'

UNION ALL SELECT'02 12 ','2	','12'

UNION ALL SELECT'03 04 ','3	','4'

UNION ALL SELECT'03 05 ','3	','5'

UNION ALL SELECT'03 06 ','3	','6'

UNION ALL SELECT'03 07 ','3	','7'

UNION ALL SELECT'03 08 ','3	','8'

UNION ALL SELECT'03 09 ','3	','9'

UNION ALL SELECT'03 10 ','3	','10'

UNION ALL SELECT'03 11 ','3	','11'

UNION ALL SELECT'03 12 ','3	','12'

UNION ALL SELECT'04 05 ','4	','5'

UNION ALL SELECT'04 06 ','4	','6'

UNION ALL SELECT'04 07 ','4	','7'

UNION ALL SELECT'04 08 ','4	','8'

UNION ALL SELECT'04 09 ','4	','9'

UNION ALL SELECT'04 10 ','4	','10'

UNION ALL SELECT'04 11 ','4	','11'

UNION ALL SELECT'04 12 ','4	','12'

UNION ALL SELECT'05 06 ','5	','6'

UNION ALL SELECT'05 07 ','5	','7'

UNION ALL SELECT'05 08 ','5	','8'

UNION ALL SELECT'05 09 ','5	','9'

UNION ALL SELECT'05 10 ','5	','10'

UNION ALL SELECT'05 11 ','5	','11'

UNION ALL SELECT'05 12 ','5	','12'

UNION ALL SELECT'06 07 ','6	','7'

UNION ALL SELECT'06 08 ','6	','8'

UNION ALL SELECT'06 09 ','6	','9'

UNION ALL SELECT'06 10 ','6	','10'

UNION ALL SELECT'06 11 ','6	','11'

UNION ALL SELECT'06 12 ','6	','12'

UNION ALL SELECT'07 08 ','7	','8'

UNION ALL SELECT'07 09 ','7	','9'

UNION ALL SELECT'07 10 ','7	','10'

UNION ALL SELECT'07 11 ','7	','11'

UNION ALL SELECT'07 12 ','7	','12'

UNION ALL SELECT'08 09 ','8	','9'

UNION ALL SELECT'08 10 ','8	','10'

UNION ALL SELECT'08 11 ','8	','11'

UNION ALL SELECT'08 12 ','8	','12'

UNION ALL SELECT'09 10 ','9	','10'

UNION ALL SELECT'09 11 ','9	','11'

UNION ALL SELECT'09 12 ','9	','12'

UNION ALL SELECT'10 11 ','10','11'

UNION ALL SELECT'10 12 ','10','12'

UNION ALL SELECT'11 12 ','11','12'

--建立表TB4

if object_id('tempdb..#TB4','U') is not null drop table #TB4

go 

create table #TB4(WNO nchar(2))  --检查 select * from  #TB4

go

insert into #TB4

SELECT'4'

UNION ALL SELECT'6'

UNION ALL SELECT'7'

UNION ALL SELECT'9'

UNION ALL SELECT'10'

UNION ALL SELECT'11'

UNION ALL SELECT'12'


问题在下面一个贴里
...全文
153 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
-小蕾- 2015-05-15
  • 打赏
  • 举报
 回复
要不然你就直接把你的sql语句中 ((Select top 1 WValue From #TB2 where BLU1=WNO)+(Select top 1 WValue From #TB2 where BLU2=WNO)) as WSum 改为： (cast((Select top 1 WValue From #TB2 where BLU1=WNO) as int)+cast((Select top 1 WValue From #TB2 where BLU2=WNO) as int)) as WSum 也可以。
-小蕾- 2015-05-15
  • 打赏
  • 举报
 回复 

select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (3,6)
and BLU2 in (9)
and 
(
blu1 not in (4,6,7,9,10,11,12)   ----修改3 加了一个'not'
and   ---修改4 把'or'改为'and'
blu2 in (4,6,7,9,10,11,12)  ---修改5 去掉not
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from  #TB1)
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO group by BLU,blu1,blu2
我上面的就是满足blue1=table2.WNO的 ，相当于你的Select top 1 WValue From #TB2 where BLU2=WNO blue2同理， 是相当于把你其他的条件都满足以后查询出来的数据，跟#tb2 里满足BLU2=WNO以及BLU2=WNO的数据进行关联，并且得到WValue 相加的值。
在路上_- 2015-05-15
  • 打赏
  • 举报
 回复
create table #TB3(BLU varchar(5), BLU1 int, BLU2 int)
RICHEER COCA 2015-05-15
  • 打赏
  • 举报
 回复
学习了，严重感谢。。。。。。。。。。。。。。。。。。。。。。。结贴给分！
-小蕾- 2015-05-15
  • 打赏
  • 举报
 回复
引用 15 楼 u011709039 的回复:
如果要WSum =10，尝试加在这里，但是错误的。请问这个条件可以加在哪里？谢谢 
WITH #tb1(WNO) AS (
SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02','1','2'
UNION ALL SELECT'01 03','1','3'  
UNION ALL SELECT'01 04','1','4'
UNION ALL SELECT'01 05','1','5'
UNION ALL SELECT'01 06','1','6'
UNION ALL SELECT'01 07','1','7'
UNION ALL SELECT'01 08','1','8'
UNION ALL SELECT'01 09','1','9'
UNION ALL SELECT'01 10','1','10'
UNION ALL SELECT'01 11','1','11'
UNION ALL SELECT'01 12','1','12'
UNION ALL SELECT'02 03','2','3'
UNION ALL SELECT'02 04','2','4'
UNION ALL SELECT'02 05','2','5'
UNION ALL SELECT'02 06','2','6'
UNION ALL SELECT'02 07','2','7'
UNION ALL SELECT'02 08','2','8'
UNION ALL SELECT'02 09','2','9'
UNION ALL SELECT'02 10','2','10'
UNION ALL SELECT'02 11','2','11'
UNION ALL SELECT'02 12','2','12'
UNION ALL SELECT'03 04','3','4'
UNION ALL SELECT'03 05','3','5'
UNION ALL SELECT'03 06','3','6'
UNION ALL SELECT'03 07','3','7'
UNION ALL SELECT'03 08','3','8'
UNION ALL SELECT'03 09','3','9'
UNION ALL SELECT'03 10','3','10'
UNION ALL SELECT'03 11','3','11'
UNION ALL SELECT'03 12','3','12'
UNION ALL SELECT'04 05','4','5'
UNION ALL SELECT'04 06','4','6'
UNION ALL SELECT'04 07','4','7'
UNION ALL SELECT'04 08','4','8'
UNION ALL SELECT'04 09','4','9'
UNION ALL SELECT'04 10','4','10'
UNION ALL SELECT'04 11','4','11'
UNION ALL SELECT'04 12','4','12'
UNION ALL SELECT'05 06','5','6'
UNION ALL SELECT'05 07','5','7'
UNION ALL SELECT'05 08','5','8'
UNION ALL SELECT'05 09','5','9'
UNION ALL SELECT'05 10','5','10'
UNION ALL SELECT'05 11','5','11'
UNION ALL SELECT'05 12','5','12'
UNION ALL SELECT'06 07','6','7'
UNION ALL SELECT'06 08','6','8'
UNION ALL SELECT'06 09','6','9'
UNION ALL SELECT'06 10','6','10'
UNION ALL SELECT'06 11','6','11'
UNION ALL SELECT'06 12','6','12'
UNION ALL SELECT'07 08','7','8'
UNION ALL SELECT'07 09','7','9'
UNION ALL SELECT'07 10','7','10'
UNION ALL SELECT'07 11','7','11'
UNION ALL SELECT'07 12','7','12'
UNION ALL SELECT'08 09','8','9'
UNION ALL SELECT'08 10','8','10'
UNION ALL SELECT'08 11','8','11'
UNION ALL SELECT'08 12','8','12'
UNION ALL SELECT'09 10','9','10'
UNION ALL SELECT'09 11','9','11'
UNION ALL SELECT'09 12','9','12'
UNION ALL SELECT'10 11','10','11'
UNION ALL SELECT'10 12','10','12'
UNION ALL SELECT'11 12','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)
select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 ---需要同时满足对TB2的条件设定
where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7
)
and BLU2 in (
SELECT WNO FROM #TB2 -----需要同时满足对TB2的条件设定
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)  
and 
(
blu1 not in (
select wno from  #TB4---需要同时满足对TB4的条件设定
)
AND
blu2 in (
select wno from  #TB4---需要同时满足对TB4的条件设定
)
)
and BLU1 in (select WNO from #TB1)---需要同时满足在TB1出现
and BLU2 in (select WNO from #TB1)---需要同时满足在TB1出现
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO 
--and  WSum =10  --如果要WSum =10，尝试加在这里，但是错误的。请问这个条件可以加在哪里？谢谢
group by BLU,blu1,blu2
如果要WSum =10，试试 

select * from 
(
Select BLU,blu1,blu2,
(cast((Select top 1 WValue From  #TB2 where BLU1=WNO) as int)+cast((Select top 1 WValue From  #TB2 where BLU2=WNO) as int)) as WSum  
--into #TB5 
FROM  #TB3  ---查询表TB3，但需要同时满足以下对TB1、TB2、TB4的条件设定
where 1=1 
and blu1 in (
	SELECT WNO FROM  #TB2 ---需要同时满足对TB2的条件设定，就是结果里blu1='3'
	where 
	WValue between 2 and 22 and BQY in ('1','2','3','4') 
	and WNO between 1 and 7
)
and BLU2 in (
	SELECT WNO FROM #TB2 -----需要同时满足对TB2的条件设定，就是结果里blu2='9'
	where WValue between 6 and 6 and BQY in ('1','2','3','4') 
	and WNO  between 3 and 12
)  
and 
(
blu1 not in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu1='3'
)
AND
blu2 in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu2='9'
)
)
and BLU1 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu1='3'
and BLU2 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu2='9'
)as table1
where WSum=10
-小蕾- 2015-05-15
  • 打赏
  • 举报
 回复
引用 14 楼 u011709039 的回复:
可能是我没有说明白
你把你in 里面的条件换乘你的条件就行了。我原先也是用的你的条件，不过看语句太多太乱才给替成数字了
RICHEER COCA 2015-05-15
  • 打赏
  • 举报
 回复
如果要WSum =10，尝试加在这里，但是错误的。请问这个条件可以加在哪里？谢谢 
WITH #tb1(WNO) AS (
SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02','1','2'
UNION ALL SELECT'01 03','1','3'  
UNION ALL SELECT'01 04','1','4'
UNION ALL SELECT'01 05','1','5'
UNION ALL SELECT'01 06','1','6'
UNION ALL SELECT'01 07','1','7'
UNION ALL SELECT'01 08','1','8'
UNION ALL SELECT'01 09','1','9'
UNION ALL SELECT'01 10','1','10'
UNION ALL SELECT'01 11','1','11'
UNION ALL SELECT'01 12','1','12'
UNION ALL SELECT'02 03','2','3'
UNION ALL SELECT'02 04','2','4'
UNION ALL SELECT'02 05','2','5'
UNION ALL SELECT'02 06','2','6'
UNION ALL SELECT'02 07','2','7'
UNION ALL SELECT'02 08','2','8'
UNION ALL SELECT'02 09','2','9'
UNION ALL SELECT'02 10','2','10'
UNION ALL SELECT'02 11','2','11'
UNION ALL SELECT'02 12','2','12'
UNION ALL SELECT'03 04','3','4'
UNION ALL SELECT'03 05','3','5'
UNION ALL SELECT'03 06','3','6'
UNION ALL SELECT'03 07','3','7'
UNION ALL SELECT'03 08','3','8'
UNION ALL SELECT'03 09','3','9'
UNION ALL SELECT'03 10','3','10'
UNION ALL SELECT'03 11','3','11'
UNION ALL SELECT'03 12','3','12'
UNION ALL SELECT'04 05','4','5'
UNION ALL SELECT'04 06','4','6'
UNION ALL SELECT'04 07','4','7'
UNION ALL SELECT'04 08','4','8'
UNION ALL SELECT'04 09','4','9'
UNION ALL SELECT'04 10','4','10'
UNION ALL SELECT'04 11','4','11'
UNION ALL SELECT'04 12','4','12'
UNION ALL SELECT'05 06','5','6'
UNION ALL SELECT'05 07','5','7'
UNION ALL SELECT'05 08','5','8'
UNION ALL SELECT'05 09','5','9'
UNION ALL SELECT'05 10','5','10'
UNION ALL SELECT'05 11','5','11'
UNION ALL SELECT'05 12','5','12'
UNION ALL SELECT'06 07','6','7'
UNION ALL SELECT'06 08','6','8'
UNION ALL SELECT'06 09','6','9'
UNION ALL SELECT'06 10','6','10'
UNION ALL SELECT'06 11','6','11'
UNION ALL SELECT'06 12','6','12'
UNION ALL SELECT'07 08','7','8'
UNION ALL SELECT'07 09','7','9'
UNION ALL SELECT'07 10','7','10'
UNION ALL SELECT'07 11','7','11'
UNION ALL SELECT'07 12','7','12'
UNION ALL SELECT'08 09','8','9'
UNION ALL SELECT'08 10','8','10'
UNION ALL SELECT'08 11','8','11'
UNION ALL SELECT'08 12','8','12'
UNION ALL SELECT'09 10','9','10'
UNION ALL SELECT'09 11','9','11'
UNION ALL SELECT'09 12','9','12'
UNION ALL SELECT'10 11','10','11'
UNION ALL SELECT'10 12','10','12'
UNION ALL SELECT'11 12','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)
select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 ---需要同时满足对TB2的条件设定
where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7
)
and BLU2 in (
SELECT WNO FROM #TB2 -----需要同时满足对TB2的条件设定
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)  
and 
(
blu1 not in (
select wno from  #TB4---需要同时满足对TB4的条件设定
)
AND
blu2 in (
select wno from  #TB4---需要同时满足对TB4的条件设定
)
)
and BLU1 in (select WNO from #TB1)---需要同时满足在TB1出现
and BLU2 in (select WNO from #TB1)---需要同时满足在TB1出现
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO 
--and  WSum =10  --如果要WSum =10，尝试加在这里，但是错误的。请问这个条件可以加在哪里？谢谢
group by BLU,blu1,blu2
RICHEER COCA 2015-05-15
  • 打赏
  • 举报
 回复
可能是我没有说明白
RICHEER COCA 2015-05-15
  • 打赏
  • 举报
 回复
引用 11 楼 zyl_leilei 的回复:

select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (3,6)
and BLU2 in (9)
and 
(
blu1 not in (4,6,7,9,10,11,12)   ----修改3 加了一个'not'
and   ---修改4 把'or'改为'and'
blu2 in (4,6,7,9,10,11,12)  ---修改5 去掉not
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from  #TB1)
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO group by BLU,blu1,blu2
我上面的就是满足blue1=table2.WNO的 ，相当于你的Select top 1 WValue From #TB2 where BLU2=WNO blue2同理， 是相当于把你其他的条件都满足以后查询出来的数据，跟#tb2 里满足BLU2=WNO以及BLU2=WNO的数据进行关联，并且得到WValue 相加的值。
继续请教：大神的代码有2个问题请教 一是：没有查询#TB2和#TB4的数据（表#TB3数据固定的，#TB1、#TB2、#TB4是变化的，取#TB1、#TB2、#TB4的数据仅仅是''in,或则'not in'），可以加入吗？ 二是：
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum
这个语句是个过程，真正需要的满足条件是取WSum值的大小，而不是计算关联数据，简单地说，这个语句是计算好了所有的WSum的值，我需要取WSum，
 WHERE WSum  Between 16 and 17 or WSum  Between 17 and 22
不同的WSum对应的的WNO是不同的，如何加入这个查询条件？谢谢指点。
-小蕾- 2015-05-14
  • 打赏
  • 举报
 回复 

WITH #tb1(WNO) AS (
    SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
    SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02 ','1    ','2'
UNION ALL SELECT'01 03 ','1    ','3'
UNION ALL SELECT'01 04 ','1    ','4'
UNION ALL SELECT'01 05 ','1    ','5'
UNION ALL SELECT'01 06 ','1    ','6'
UNION ALL SELECT'01 07 ','1    ','7'
UNION ALL SELECT'01 08 ','1    ','8'
UNION ALL SELECT'01 09 ','1    ','9'
UNION ALL SELECT'01 10 ','1    ','10'
UNION ALL SELECT'01 11 ','1    ','11'
UNION ALL SELECT'01 12 ','1    ','12'
UNION ALL SELECT'02 03 ','2    ','3'
UNION ALL SELECT'02 04 ','2    ','4'
UNION ALL SELECT'02 05 ','2    ','5'
UNION ALL SELECT'02 06 ','2    ','6'
UNION ALL SELECT'02 07 ','2    ','7'
UNION ALL SELECT'02 08 ','2    ','8'
UNION ALL SELECT'02 09 ','2    ','9'
UNION ALL SELECT'02 10 ','2    ','10'
UNION ALL SELECT'02 11 ','2    ','11'
UNION ALL SELECT'02 12 ','2    ','12'
UNION ALL SELECT'03 04 ','3    ','4'
UNION ALL SELECT'03 05 ','3    ','5'
UNION ALL SELECT'03 06 ','3    ','6'
UNION ALL SELECT'03 07 ','3    ','7'
UNION ALL SELECT'03 08 ','3    ','8'
UNION ALL SELECT'03 09 ','3    ','9'
UNION ALL SELECT'03 10 ','3    ','10'
UNION ALL SELECT'03 11 ','3    ','11'
UNION ALL SELECT'03 12 ','3    ','12'
UNION ALL SELECT'04 05 ','4    ','5'
UNION ALL SELECT'04 06 ','4    ','6'
UNION ALL SELECT'04 07 ','4    ','7'
UNION ALL SELECT'04 08 ','4    ','8'
UNION ALL SELECT'04 09 ','4    ','9'
UNION ALL SELECT'04 10 ','4    ','10'
UNION ALL SELECT'04 11 ','4    ','11'
UNION ALL SELECT'04 12 ','4    ','12'
UNION ALL SELECT'05 06 ','5    ','6'
UNION ALL SELECT'05 07 ','5    ','7'
UNION ALL SELECT'05 08 ','5    ','8'
UNION ALL SELECT'05 09 ','5    ','9'
UNION ALL SELECT'05 10 ','5    ','10'
UNION ALL SELECT'05 11 ','5    ','11'
UNION ALL SELECT'05 12 ','5    ','12'
UNION ALL SELECT'06 07 ','6    ','7'
UNION ALL SELECT'06 08 ','6    ','8'
UNION ALL SELECT'06 09 ','6    ','9'
UNION ALL SELECT'06 10 ','6    ','10'
UNION ALL SELECT'06 11 ','6    ','11'
UNION ALL SELECT'06 12 ','6    ','12'
UNION ALL SELECT'07 08 ','7    ','8'
UNION ALL SELECT'07 09 ','7    ','9'
UNION ALL SELECT'07 10 ','7    ','10'
UNION ALL SELECT'07 11 ','7    ','11'
UNION ALL SELECT'07 12 ','7    ','12'
UNION ALL SELECT'08 09 ','8    ','9'
UNION ALL SELECT'08 10 ','8    ','10'
UNION ALL SELECT'08 11 ','8    ','11'
UNION ALL SELECT'08 12 ','8    ','12'
UNION ALL SELECT'09 10 ','9    ','10'
UNION ALL SELECT'09 11 ','9    ','11'
UNION ALL SELECT'09 12 ','9    ','12'
UNION ALL SELECT'10 11 ','10','11'
UNION ALL SELECT'10 12 ','10','12'
UNION ALL SELECT'11 12 ','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)


Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
--into #TB5 
FROM  #TB3
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7)
and BLU2 in (
SELECT WNO FROM #TB2 --检查数据  select * from  #TB2
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)
and 
(
blu1 in (
select wno from  #TB4
)
or
blu2 not in (
select wno from  #TB4
)
)
--and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from #TB1)
你试试看。 and BLU1 in (select WNO from #TB1)这个是你倒数第二个条件，我测试时发现这个条件不符合你要的结果
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
补充：执行语句 
Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
---into #TB5 
FROM  #TB3
无法有这样的结果 
BLU	blu1	blu2	WSum
01 02 	01	02	7
01 03 	01	03	13
01 04 	01	04	4
01 05 	01	05	6
01 06 	01	06	18
01 07 	01	07	4
01 08 	01	08	5
01 09 	01	09	9
01 10 	01	10	8
01 11 	01	11	15
01 12 	01	12	10
02 03 	02	03	14
02 04 	02	04	5
02 05 	02	05	7
02 06 	02	06	19
02 07 	02	07	5
02 08 	02	08	6
02 09 	02	09	10
02 10 	02	10	9
02 11 	02	11	16
02 12 	02	12	11
03 04 	03	04	11
03 05 	03	05	13
03 06 	03	06	25
03 07 	03	07	11
03 08 	03	08	12
03 09 	03	09	16
03 10 	03	10	15
03 11 	03	11	22
03 12 	03	12	17
04 05 	04	05	4
04 06 	04	06	16
04 07 	04	07	2
04 08 	04	08	3
04 09 	04	09	7
04 10 	04	10	6
04 11 	04	11	13
04 12 	04	12	8
05 06 	05	06	18
05 07 	05	07	4
05 08 	05	08	5
05 09 	05	09	9
05 10 	05	10	8
05 11 	05	11	15
05 12 	05	12	10
06 07 	06	07	16
06 08 	06	08	17
06 09 	06	09	21
06 10 	06	10	20
06 11 	06	11	27
06 12 	06	12	22
07 08 	07	08	3
07 09 	07	09	7
07 10 	07	10	6
07 11 	07	11	13
07 12 	07	12	8
08 09 	08	09	8
08 10 	08	10	7
08 11 	08	11	14
08 12 	08	12	9
09 10 	09	10	11
09 11 	09	11	18
09 12 	09	12	13
10 11 	10	11	17
10 12 	10	12	12
11 12 	11	12	19
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复 
--测试，筛选符合要求的数据
if object_id('tempdb..#tb5','U') is not null drop table #tb5
go 
if object_id('tempdb..#tb6','U') is not null drop table #tb6
go 
Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
into #TB5 
FROM  #TB3
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 
where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7
)
and BLU2 in (
SELECT WNO FROM #TB2 --检查数据  select * from  #TB2
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)  
and 
(
blu1 in (
select wno from  #TB4
)
or
blu2 not in (
select wno from  #TB4
)
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from #TB1)

select *into #TB6 from #TB5 WHERE WSum  Between 16 and 17 or WSum  Between 17 and 22
select * from #TB6
想得到的结果是 
BLU	blu1	blu2	WSum
03 09 	03	09	16
问题：无法得到这样的结果，不知代码错误所在，请大神指点，谢谢
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
--测试，筛选符合要求的数据 if object_id('tempdb..#tb5','U') is not null drop table #tb5 go if object_id('tempdb..#tb6','U') is not null drop table #tb6 go Select BLU,blu1,blu2, ((Select top 1 WValue From #TB2 where BLU1=WNO)+(Select top 1 WValue From #TB2 where BLU2=WNO)) as WSum into #TB5 FROM #TB3 where 1=1 and blu1 in ( SELECT WNO FROM #TB2 where WValue between 2 and 22 and BQY in ('1','2','3','4') and WNO between 1 and 7 ) and BLU2 in ( SELECT WNO FROM #TB2 --检查数据 select * from #TB2 where WValue between 6 and 6 and BQY in ('1','2','3','4') and WNO between 3 and 12 ) and ( blu1 in ( select wno from #TB4 ) or blu2 not in ( select wno from #TB4 ) ) and BLU1 in (select WNO from #TB1) and BLU2 in (select WNO from #TB1) select *into #TB6 from #TB5 WHERE WSum Between 16 and 17 or WSum Between 17 and 22 select * from #TB6 先得到的结果是 
BLU	blu1	blu2	WSum
03 09 	03	09	16
问题：无法得到
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
引用 8 楼 zyl_leilei 的回复:

WITH #tb1(WNO) AS (
SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02','1','2'
UNION ALL SELECT'01 03','1','3'   --- ----修改1 修改数据格式
UNION ALL SELECT'01 04','1','4'
UNION ALL SELECT'01 05','1','5'
UNION ALL SELECT'01 06','1','6'
UNION ALL SELECT'01 07','1','7'
UNION ALL SELECT'01 08','1','8'
UNION ALL SELECT'01 09','1','9'
UNION ALL SELECT'01 10','1','10'
UNION ALL SELECT'01 11','1','11'
UNION ALL SELECT'01 12','1','12'
UNION ALL SELECT'02 03','2','3'
UNION ALL SELECT'02 04','2','4'
UNION ALL SELECT'02 05','2','5'
UNION ALL SELECT'02 06','2','6'
UNION ALL SELECT'02 07','2','7'
UNION ALL SELECT'02 08','2','8'
UNION ALL SELECT'02 09','2','9'
UNION ALL SELECT'02 10','2','10'
UNION ALL SELECT'02 11','2','11'
UNION ALL SELECT'02 12','2','12'
UNION ALL SELECT'03 04','3','4'
UNION ALL SELECT'03 05','3','5'
UNION ALL SELECT'03 06','3','6'
UNION ALL SELECT'03 07','3','7'
UNION ALL SELECT'03 08','3','8'
UNION ALL SELECT'03 09','3','9'
UNION ALL SELECT'03 10','3','10'
UNION ALL SELECT'03 11','3','11'
UNION ALL SELECT'03 12','3','12'
UNION ALL SELECT'04 05','4','5'
UNION ALL SELECT'04 06','4','6'
UNION ALL SELECT'04 07','4','7'
UNION ALL SELECT'04 08','4','8'
UNION ALL SELECT'04 09','4','9'
UNION ALL SELECT'04 10','4','10'
UNION ALL SELECT'04 11','4','11'
UNION ALL SELECT'04 12','4','12'
UNION ALL SELECT'05 06','5','6'
UNION ALL SELECT'05 07','5','7'
UNION ALL SELECT'05 08','5','8'
UNION ALL SELECT'05 09','5','9'
UNION ALL SELECT'05 10','5','10'
UNION ALL SELECT'05 11','5','11'
UNION ALL SELECT'05 12','5','12'
UNION ALL SELECT'06 07','6','7'
UNION ALL SELECT'06 08','6','8'
UNION ALL SELECT'06 09','6','9'
UNION ALL SELECT'06 10','6','10'
UNION ALL SELECT'06 11','6','11'
UNION ALL SELECT'06 12','6','12'
UNION ALL SELECT'07 08','7','8'
UNION ALL SELECT'07 09','7','9'
UNION ALL SELECT'07 10','7','10'
UNION ALL SELECT'07 11','7','11'
UNION ALL SELECT'07 12','7','12'
UNION ALL SELECT'08 09','8','9'
UNION ALL SELECT'08 10','8','10'
UNION ALL SELECT'08 11','8','11'
UNION ALL SELECT'08 12','8','12'
UNION ALL SELECT'09 10','9','10'
UNION ALL SELECT'09 11','9','11'
UNION ALL SELECT'09 12','9','12'
UNION ALL SELECT'10 11','10','11'
UNION ALL SELECT'10 12','10','12'
UNION ALL SELECT'11 12','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)

select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (3,6)
and BLU2 in (9)
and 
(
blu1 not in (4,6,7,9,10,11,12)   ----修改3 加了一个'not'
and   ---修改4 把'or'改为'and'
blu2 in (4,6,7,9,10,11,12)  ---修改5 去掉not
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from  #TB1)
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO group by BLU,blu1,blu2
查询表TB3，但需要同时满足对TB1、TB2、TB4的条件设定，不能设定特殊的blu1和blu2的值，因为TB1、TB2、TB4是变化的 谢谢大神的指点，但WSum的条件一定要有，见以下代码，所以WSum的值要正确。盼望指点，谢谢 
--测试，查询表TB3，但需要同时满足对TB1、TB2、TB4的条件设定，筛选符合要求的结果
if object_id('tempdb..#tb5','U') is not null drop table #tb5
go 
if object_id('tempdb..#tb6','U') is not null drop table #tb6
go 
Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
into #TB5 
FROM  #TB3  ---查询表TB3，但需要同时满足以下对TB1、TB2、TB4的条件设定
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 ---需要同时满足对TB2的条件设定，就是结果里blu1='3'
where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7
)
and BLU2 in (
SELECT WNO FROM #TB2 -----需要同时满足对TB2的条件设定，就是结果里blu2='9'
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)  
and 
(
blu1 not in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu1='3'
)
AND
blu2 in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu2='9'
)
)
and BLU1 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu1='3'
and BLU2 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu2='9'

select *into #TB6 from #TB5 WHERE WSum  Between 16 and 17 or WSum  Between 17 and 22---需要#TB5同时满足16=<WSum=<17
select * from #TB6
-小蕾- 2015-05-14
  • 打赏
  • 举报
 回复
结果为： 03 09 3 9 16
-小蕾- 2015-05-14
  • 打赏
  • 举报
 回复 

WITH #tb1(WNO) AS (
SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02','1','2'
UNION ALL SELECT'01 03','1','3'   --- ----修改1 修改数据格式
UNION ALL SELECT'01 04','1','4'
UNION ALL SELECT'01 05','1','5'
UNION ALL SELECT'01 06','1','6'
UNION ALL SELECT'01 07','1','7'
UNION ALL SELECT'01 08','1','8'
UNION ALL SELECT'01 09','1','9'
UNION ALL SELECT'01 10','1','10'
UNION ALL SELECT'01 11','1','11'
UNION ALL SELECT'01 12','1','12'
UNION ALL SELECT'02 03','2','3'
UNION ALL SELECT'02 04','2','4'
UNION ALL SELECT'02 05','2','5'
UNION ALL SELECT'02 06','2','6'
UNION ALL SELECT'02 07','2','7'
UNION ALL SELECT'02 08','2','8'
UNION ALL SELECT'02 09','2','9'
UNION ALL SELECT'02 10','2','10'
UNION ALL SELECT'02 11','2','11'
UNION ALL SELECT'02 12','2','12'
UNION ALL SELECT'03 04','3','4'
UNION ALL SELECT'03 05','3','5'
UNION ALL SELECT'03 06','3','6'
UNION ALL SELECT'03 07','3','7'
UNION ALL SELECT'03 08','3','8'
UNION ALL SELECT'03 09','3','9'
UNION ALL SELECT'03 10','3','10'
UNION ALL SELECT'03 11','3','11'
UNION ALL SELECT'03 12','3','12'
UNION ALL SELECT'04 05','4','5'
UNION ALL SELECT'04 06','4','6'
UNION ALL SELECT'04 07','4','7'
UNION ALL SELECT'04 08','4','8'
UNION ALL SELECT'04 09','4','9'
UNION ALL SELECT'04 10','4','10'
UNION ALL SELECT'04 11','4','11'
UNION ALL SELECT'04 12','4','12'
UNION ALL SELECT'05 06','5','6'
UNION ALL SELECT'05 07','5','7'
UNION ALL SELECT'05 08','5','8'
UNION ALL SELECT'05 09','5','9'
UNION ALL SELECT'05 10','5','10'
UNION ALL SELECT'05 11','5','11'
UNION ALL SELECT'05 12','5','12'
UNION ALL SELECT'06 07','6','7'
UNION ALL SELECT'06 08','6','8'
UNION ALL SELECT'06 09','6','9'
UNION ALL SELECT'06 10','6','10'
UNION ALL SELECT'06 11','6','11'
UNION ALL SELECT'06 12','6','12'
UNION ALL SELECT'07 08','7','8'
UNION ALL SELECT'07 09','7','9'
UNION ALL SELECT'07 10','7','10'
UNION ALL SELECT'07 11','7','11'
UNION ALL SELECT'07 12','7','12'
UNION ALL SELECT'08 09','8','9'
UNION ALL SELECT'08 10','8','10'
UNION ALL SELECT'08 11','8','11'
UNION ALL SELECT'08 12','8','12'
UNION ALL SELECT'09 10','9','10'
UNION ALL SELECT'09 11','9','11'
UNION ALL SELECT'09 12','9','12'
UNION ALL SELECT'10 11','10','11'
UNION ALL SELECT'10 12','10','12'
UNION ALL SELECT'11 12','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)

select table1.*,sum(cast(WValue as int)) as WValue from
(
Select BLU,blu1,blu2
FROM  #TB3
where 1=1 
and blu1 in (3,6)
and BLU2 in (9)
and 
(
blu1 not in (4,6,7,9,10,11,12)   ----修改3 加了一个'not'
and   ---修改4 把'or'改为'and'
blu2 in (4,6,7,9,10,11,12)  ---修改5 去掉not
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from  #TB1)
) as table1
,#tb2 as table2
where blu1=table2.WNO or blu2=table2.WNO group by BLU,blu1,blu2
-小蕾- 2015-05-14
  • 打赏
  • 举报
 回复
(Select top 1 WValue From #TB2 where BLU1=WNO)+(Select top 1 WValue From #TB2 where BLU2=WNO)) as WSum 这一句就有问题吧？ #TB2这个表里根本没有BLU1和Blu2列
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
03和 09的WValue分别是10 、6 那么WSum=16，我修改了多次，但代码执行结果WSum=106？？？WSum的条件一定要有，见以下代码，所以WSum的值要正确。盼望指点，谢谢 
--测试，查询表TB3，但需要同时满足对TB1、TB2、TB4的条件设定，筛选符合要求的结果
if object_id('tempdb..#tb5','U') is not null drop table #tb5
go 
if object_id('tempdb..#tb6','U') is not null drop table #tb6
go 
Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
into #TB5 
FROM  #TB3  ---查询表TB3，但需要同时满足以下对TB1、TB2、TB4的条件设定
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 ---需要同时满足对TB2的条件设定，就是结果里blu1='3'
where 
WValue between 2 and 22 and BQY in ('1','2','3','4') 
and WNO between 1 and 7
)
and BLU2 in (
SELECT WNO FROM #TB2 -----需要同时满足对TB2的条件设定，就是结果里blu2='9'
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)  
and 
(
blu1 not in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu1='3'
)
AND
blu2 in (
select wno from  #TB4---需要同时满足对TB4的条件设定，就是结果里blu2='9'
)
)
and BLU1 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu1='3'
and BLU2 in (select WNO from #TB1)---需要同时满足在TB1出现，就是结果里blu2='9'

select *into #TB6 from #TB5 WHERE WSum  Between 16 and 17 or WSum  Between 17 and 22---需要#TB5同时满足16=<WSum=<17
select * from #TB6
RICHEER COCA 2015-05-14
  • 打赏
  • 举报
 回复
引用 4 楼 zyl_leilei 的回复:
你试试看。 and BLU1 in (select WNO from #TB1)这个是你倒数第二个条件，我测试时发现这个条件不符合你要的结果
非常感谢楼上的大神，我修改了5处，可见注释，请教中...... 
WITH #tb1(WNO) AS (
SELECT'1'
UNION ALL SELECT'2'
UNION ALL SELECT'3'
UNION ALL SELECT'4'
UNION ALL SELECT'5'
UNION ALL SELECT'7'
UNION ALL SELECT'8'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'12'
)
,#tb2(WNO,WValue,BQY) AS (
SELECT'1','3','1'
UNION ALL SELECT'2','4','2'
UNION ALL SELECT'3','10','3'
UNION ALL SELECT'4','1','4'
UNION ALL SELECT'5','3','1'
UNION ALL SELECT'6','15','2'
UNION ALL SELECT'7','1','3'
UNION ALL SELECT'8','2','4'
UNION ALL SELECT'9','6','1'
UNION ALL SELECT'10','5','2'
UNION ALL SELECT'11','12','3'
UNION ALL SELECT'12','7','4'
)
,#TB3(BLU,BLU1,BLU2) AS (
SELECT'01 02','1','2'
UNION ALL SELECT'01 03','1','3'   --- ----修改1 修改数据格式
UNION ALL SELECT'01 04','1','4'
UNION ALL SELECT'01 05','1','5'
UNION ALL SELECT'01 06','1','6'
UNION ALL SELECT'01 07','1','7'
UNION ALL SELECT'01 08','1','8'
UNION ALL SELECT'01 09','1','9'
UNION ALL SELECT'01 10','1','10'
UNION ALL SELECT'01 11','1','11'
UNION ALL SELECT'01 12','1','12'
UNION ALL SELECT'02 03','2','3'
UNION ALL SELECT'02 04','2','4'
UNION ALL SELECT'02 05','2','5'
UNION ALL SELECT'02 06','2','6'
UNION ALL SELECT'02 07','2','7'
UNION ALL SELECT'02 08','2','8'
UNION ALL SELECT'02 09','2','9'
UNION ALL SELECT'02 10','2','10'
UNION ALL SELECT'02 11','2','11'
UNION ALL SELECT'02 12','2','12'
UNION ALL SELECT'03 04','3','4'
UNION ALL SELECT'03 05','3','5'
UNION ALL SELECT'03 06','3','6'
UNION ALL SELECT'03 07','3','7'
UNION ALL SELECT'03 08','3','8'
UNION ALL SELECT'03 09','3','9'
UNION ALL SELECT'03 10','3','10'
UNION ALL SELECT'03 11','3','11'
UNION ALL SELECT'03 12','3','12'
UNION ALL SELECT'04 05','4','5'
UNION ALL SELECT'04 06','4','6'
UNION ALL SELECT'04 07','4','7'
UNION ALL SELECT'04 08','4','8'
UNION ALL SELECT'04 09','4','9'
UNION ALL SELECT'04 10','4','10'
UNION ALL SELECT'04 11','4','11'
UNION ALL SELECT'04 12','4','12'
UNION ALL SELECT'05 06','5','6'
UNION ALL SELECT'05 07','5','7'
UNION ALL SELECT'05 08','5','8'
UNION ALL SELECT'05 09','5','9'
UNION ALL SELECT'05 10','5','10'
UNION ALL SELECT'05 11','5','11'
UNION ALL SELECT'05 12','5','12'
UNION ALL SELECT'06 07','6','7'
UNION ALL SELECT'06 08','6','8'
UNION ALL SELECT'06 09','6','9'
UNION ALL SELECT'06 10','6','10'
UNION ALL SELECT'06 11','6','11'
UNION ALL SELECT'06 12','6','12'
UNION ALL SELECT'07 08','7','8'
UNION ALL SELECT'07 09','7','9'
UNION ALL SELECT'07 10','7','10'
UNION ALL SELECT'07 11','7','11'
UNION ALL SELECT'07 12','7','12'
UNION ALL SELECT'08 09','8','9'
UNION ALL SELECT'08 10','8','10'
UNION ALL SELECT'08 11','8','11'
UNION ALL SELECT'08 12','8','12'
UNION ALL SELECT'09 10','9','10'
UNION ALL SELECT'09 11','9','11'
UNION ALL SELECT'09 12','9','12'
UNION ALL SELECT'10 11','10','11'
UNION ALL SELECT'10 12','10','12'
UNION ALL SELECT'11 12','11','12'
)
,#TB4(WNO) AS (
SELECT'4'
UNION ALL SELECT'6'
UNION ALL SELECT'7'
UNION ALL SELECT'9'
UNION ALL SELECT'10'
UNION ALL SELECT'11'
UNION ALL SELECT'12'
)
Select BLU,blu1,blu2,
((Select top 1 WValue From  #TB2 where BLU1=WNO)+(Select top 1 WValue From  #TB2 where BLU2=WNO)) as WSum  
FROM  #TB3
where 1=1 
and blu1 in (
SELECT WNO FROM  #TB2 where 
WValue between 9 and 22 and BQY in ('1','2','3','4') ----修改2 '2'改为'9'
and WNO between 1 and 7)
and BLU2 in (
SELECT WNO FROM #TB2 --检查数据  select * from  #TB2
where WValue between 6 and 6 and BQY in ('1','2','3','4') 
and WNO  between 3 and 12
)
and 
(
blu1 not in (    ----修改3 加了一个'not'
select wno from  #TB4
)
and   ---修改4 把'or'改为'and'
blu2 in (  ---修改5 去掉not
select wno from  #TB4
)
)
and BLU1 in (select WNO from  #TB1)
and BLU2 in (select WNO from #TB1)
执行结果是 
BLU	blu1	blu2	WSum
03 09	3	9	106
03和 09的WValue分别是10 、6 那么WSum=16，我修改了多次，但代码执行结果WSum=106？？？盼望指点，谢谢 附： 我需要的结果是 
BLU	blu1	blu2	WSum
03 09 	03	09	16
数据结构基础系列(8)：查找
数据结构课程是计算机类专业的专业基础课程，在...系列课程包含11个部分，本课为第8部分查找，介绍查找的基本概念，重点是线性表上顺序查找、二分查找和分块查找，二叉排序树、AVL树和B-树的各种树表，以及哈希表查找。
C语言数据结构与算法编程、链表、树、栈、队列、排序算法
黄老师通过对数据结构核心知识点： 顺序表、链表、栈、队列、树、二叉树、遍历、插入、删除、排序、查找算法 的细心讲解，结合生动的C语言实战代码演练、帮助学员快速掌握数据结构的核心内容、为STL、Boost标准库的...
图解Java数据结构和算法
2.网上数据结构和算法的课程不少，但存在两个问题：1)授课方式单一，大多是照着代码念一遍，数据结构和算法本身就比较难理解，对基础好的学员来说，还好一点，对基础不好的学生来说，基本上就是听天书了2)说是讲数据...
数据结构基础系列套餐：应用篇（查找、排序、文件）
本套餐是数据结构应用篇，详细介绍在计算机科学与工程中广泛使用的查找、排序（内排序和外排序）、文件，除了介绍解决问题的方法外，也为前面内容的应用方法做出示范。在课程主页...
java数据结构算法
做一门精致,全面详细的 java数据结构与算法!!! 让天下没有难学的数据结构, 让天下没有难学的算法, 不吹不黑,我们的讲师及其敬业,可以看到课程视频,课件,代码的录制撰写,都是在深夜,如此用心,其心可鉴,他不掉头发,谁...
疑难问题

22,209

社区成员

121,730

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章