SQL 查询关于ANY的问题。
问题1：
假设有两个表如下：
如果有个查询如下：
SELECT t1.* FROM table1 t1 where t1.amount > any (SELECT thresholdvalue FROM table2)
如果条件成立，查询到table1中记录3的值是110，正好大于表table2中的记录1的100。
能不能把table2表中的记录1的值100带到查询结果中？ 谢谢。
问题2：
如果两个表如下：
需要实现，table1，table2中任何记录如果他们type类型相同，table1中的amount如果大于相同类型table2中任何一条记录，就查询出来）
谢谢。
SQL?查询关于ANY的问题。
[解决办法]
大于任何一条记录，等同于大于其中的最小值，楼主按照这思路就可解决问题。
[解决办法]
create table table1 (id int,amount int)
insert into table1 values(1,50)
insert into table1 values(2,60)
insert into table1 values(3,110)
insert into table1 values(4,500)
create table table2 (id int ,thresholdvalue int)
insert into table2 values(1,100)
insert into table2 values(2,200)
insert into table2 values(3,300)
select table1.id,table1.amount ,thresholdvalue=MIN(table2.thresholdvalue)
from table1,table2
where table1.amount>(select MIN(thresholdvalue)from table2)
group by table1.id,table1.amount
/*
idamountthresholdvalue
3110100
4500100
*/
create table table1 (id int,amount int,type nvarchar(5))
insert into table1 values(1,200,'A')
insert into table1 values(2,80,'B')
insert into table1 values(3,100,'C')
create table table2 (id int ,thresholdvalue int,type nvarchar(5))
insert into table2 values(1,100,'A')
insert into table2 values(2,1000,'A')
insert into table2 values(3,50,'B')
insert into table2 values(4,500,'B')
insert into table2 values(5,90,'C')
select a.id,
a.amount,
a.type,
b.thresholdvalue
from table1 a
INNER JOIN table2 b
on a.type=b.type
where a.amount>b.thresholdvalue
/*
idamounttypethresholdvalue
1200A100
280B50
3100C90
*/
create table table1 (id int,amount int,type nvarchar(5))
insert into table1 values(1,200,'A')
insert into table1 values(2,80,'B')
insert into table1 values(3,100,'C')
create table table2 (id int ,thresholdvalue int,type nvarchar(5))
insert into table2 values(1,100,'A')
insert into table2 values(2,1000,'A')
insert into table2 values(3,50,'B')
insert into table2 values(4,500,'B')
insert into table2 values(5,90,'C')
SELECT * FROM table1
SELECT * FROM table2
--#1.
SELECT t1.*, t2.thresholdvalue FROM table1 t1
CROSS APPLY
(SELECT TOP(1) * FROM table2 m WHERE m.thresholdvalue < t1.amount) t2
/*
idamounttypethresholdvalue
1200A100
280B50
3100C50
*/
--#2.
SELECT t1.* FROM table1 t1
where t1.amount > any (SELECT thresholdvalue FROM table2 m WHERE m.TYPE = t1.type)
/*
idamounttype
1200A
280B
3100C
*/
