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

2015-05-14 11:22:13 240 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Excel FILTER函数：动态数组筛选，彻底告别VLOOKUP的复杂操作在数据处理领域，高效的数据查找与筛选是核心需求。传统方法如VLOOKUP函数，虽然能实现基础查找，但其过程式思维在应对一对多查询、多条件动态筛选时，往往需要复杂的数组公式配合，导致公式冗长且维护困难。现代数据分析更倾向于声明式的处理逻辑，即直接定义“需要什么数据”，而非“如何一步步获取”。Excel FILTER函数正是这一理念的杰出代表，它通过简洁的语法和动态数组特性，实现了数据筛选的范式革新。其核心价值在于，用户只需指定数据区域和筛选条件，函数便能返回所有匹配结果的动态数组，结果自动溢出，极大提升了报表阅读详情

建立表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'

问题在下面一个贴里

...全文

第一章-数据规范-数据分析报表设计标准 0 前言本笔记是对“潭州课堂”的“Excel数据处理与分析（湖南大学通识课）”课程的整理，建议大家到平台上通过视频进行学习，效果更好（老师超级有趣，课程干货满满）。 1 Excel数据应用层工作流程 1.1 Excel工作流程数据存储-数据处理-数据分析-数据的呈现 1.2 Excel相关名词解析工作簿：通常所说的Excel文件 1.3 认识鼠标的三大状态选择柄：用于选择单元格区域移动柄：用于移动或配合ctrl键盘复制单元格区域填充柄：复制、填充单元格区域，包括数据、公式等，双击有惊喜！ 1 阅读详情

RICHEER COCA

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

-小蕾- 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

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

相关推荐