一个sql语句中,要两次用到同一个参数的值,比如:
select * from table1 where ( name = :NAME) and (age = select max(age) from table1 where name = :NAME)
大概其就是这个意思,可却无法得到正确结果。如果换成形如:
select * from table1 where ( name = :NAME1) and (age = select max(age) from table1 where name = :NAME2)
写成两个参数就行了,求教各位大侠为什么会这样?
select * from table1 where ( name = :NAME) and (age = select max(age) from table1 where name = :NAME)
大概其就是这个意思,可却无法得到正确结果。如果换成形如:
select * from table1 where ( name = :NAME1) and (age = select max(age) from table1 where name = :NAME2)
写成两个参数就行了,求教各位大侠为什么会这样?
select * from table1 a where ( a.name = :NAME) and (a.age = select max(b.age) from table1 b where b.name = a.NAME)这样你就只要传一个参数呀.
select field from table1
where name = :NAME
union
select field from table2
where name = :NAME
union
select field from table3
where name = :NAME
union
select field from table4
where name = :NAME
...............
这样在table2中就不知道table1中name的值了
在数据集的sql里写:
select field from table1
where name = :NAME1
union
select field from table2
where name = :NAME2
union
select field from table3
where name = :NAME3
union
select field from table4
where name = :NAME4
...............然后在程序里
aqryx.close;
aqryx.Parameters.ParamByName('NAME1').value := 'tom';
aqryx.Parameters.ParamByName('NAME2').value := 'tom';
aqryx.Parameters.ParamByName('NAME3').value := 'tom';
aqryx.Parameters.ParamByName('NAME4').value := 'tom';
...................
aqryx.open;
Oracle 中的语法是
begin
str_username varchar2(2);
str_username := :username;
select ...... where a.username=str_username....end;
Sql server 的变量前则需要@这样,你就能实现只传一个参数而可以到处使用了。当然,用Procedure和Function则更好处理了
只能在T_SQL中实现了declare @name varchar2(20)
@name= :name --此处为参数
select .... from ... where a.username=@name and ....以上是SQL Server 的实现方法。
至于Oracle如何实现,我也不清楚了,不只哪位高人知晓。