其实我需要的语句是这样的:
1.取出TFAT_INVOICE表中的3个字段INVOICE_ID,INVOICE_MATURITYDATE
2.如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10
3.TFAT_INVOICE 和TFAT_CLIENT_PROFILE表中的关联字段是:SITE_CODE和CLIENT_CODE select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' 关键是"?"处的语句如何写,或许我的上面的sql语句有问题。请高手们帮忙!谢谢
1.取出TFAT_INVOICE表中的3个字段INVOICE_ID,INVOICE_MATURITYDATE
2.如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10
3.TFAT_INVOICE 和TFAT_CLIENT_PROFILE表中的关联字段是:SITE_CODE和CLIENT_CODE select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' 关键是"?"处的语句如何写,或许我的上面的sql语句有问题。请高手们帮忙!谢谢
Select Case When a.CLIENT_CODE In(select b.CLIENT_CODE from TFAT_CLIENT_PROFILE b
where b.DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and b.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR') Then b.invoice_grace(出错的地方)
Else 10
End
From TFAT_INVOICE a
发现有2ge 问题:
1.b是在when里面定义的,不能在外面访问
2.when条件是一个in条件,也就是子查询有可能返回多值,所以即便指定的b.invoice_grace在访问范围内,也是一个集合,这是不允许的,只能出现单值,否则数据库无法返回结果集了。不知道哪位高手帮助我来实现我的需求,只要能实现就可以。
select *,
(case when b.CLIENT_CODE is null then 10
else b.invoice_grace end) as invoice_grace
From TFAT_INVOICE a left join TFAT_CLIENT_PROFILE b
on (a.SITE_CODE=b.SITE_CODE and a.CLIENT_CODE=b.CLIENT_CODE )
From TFAT_INVOICE a left join TFAT_CLIENT_PROFILE b
on (a.SITE_CODE=b.SITE_CODE and a.CLIENT_CODE=b.CLIENT_CODE )
????????????????????????????????????????????
select distinct
a.INVOICE_ID
,a.INVOICE_MATURITYDATE
,isnull(b.invoice_grace,10) as invoice_grace
From TFAT_INVOICE a
left join TFAT_CLIENT_PROFILE b
on a.SITE_CODE=b.SITE_CODE
and a.CLIENT_CODE=b.CLIENT_CODE???????????????????????????????????????????????????????select distinct
a.INVOICE_ID
,a.INVOICE_MATURITYDATE
,(case
when exists(
select 1
from TFAT_CLIENT_PROFILE c
where a.CLIENT_CODE = c.CLIENT_CODE)
then b.invoice_grace
else 10 end
) as invoice_grace
From TFAT_INVOICE a
left join TFAT_CLIENT_PROFILE b
on a.SITE_CODE=b.SITE_CODE
and a.CLIENT_CODE=b.CLIENT_CODE
????????????????????????????????????????????????????????????????????
select distinct INVOICE_ID,INVOICE_MATURITYDATE,
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' “?”其中是一个条件限制,是一个数字(如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10)
from TFAT_INVOICE where ADVANCE_ID Is Not Null And INVOICE_MATURITYDATE + ? <= TO_DATE('2009-05-23','yyyy-mm-dd') and INVOICE_STATUS = 'D' 这里的"?" 肯定是一个SQL语句,我最初用我最初用下面的语句来替换"?"
Select Case When a.CLIENT_CODE In(select b.CLIENT_CODE from TFAT_CLIENT_PROFILE b
where b.DATA_ACTIVE='Y' And a.site_code = b.site_code And a.client_code=b.client_code
and b.SITE_CODE = 'FACTOR' and a.DEBTOR_CODE = 'FDEBTOR') Then b.invoice_grace(出错的地方)
Else 10
End
From TFAT_INVOICE a
发现有2ge 问题:
1.b是在when里面定义的,不能在外面访问
2.when条件是一个in条件,也就是子查询有可能返回多值,所以即便指定的b.invoice_grace在访问范围内,也是一个集合,这是不允许的,只能出现单值,否则数据库无法返回结果集了。 不知道“?”处的SQL语句如何写?????????
-------------------------------------------------
[1]CASE 简单表达式将某个表达式与一组简单表达式进行比较以确定结果: CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
-------------------------------------------------- [2]CASE 搜索计算表达式一组布尔表达式以确定结果:
CASE
WHEN Boolean_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
--------------------------------------------------
参见MSDN:http://msdn.microsoft.com/zh-cn/library/ms181765(SQL.90).aspx
应用例子:
[1] CASE 简单表达式:
select [workerID], [Name],[技能职称]=
case lower(ltrim(rtrim([Department])))
when 'engineer' then '工程师'
when 'office' then '办公室人员'
else '未知职称'
end
from tempdb..#xTable[2] CASE 搜索/计算表达式:select [workerID],[Name],[年龄分析判断]=
case
when [age]<18 then '童工'
when [age]>=18 and [age]<20 then '实习工'
when [age]>=20 and [age]<25 then '正式工'
when [age]>=25 and [age]<35 then '主力工'
when [age]>=35 and [age]<45 then '牛人'
else '老板'
end
from tempdb..#xTable表结构:
create table #xTable
(
[recID] int identity(1,1),
[workerID] char(6) not null primary key,
[Name] varchar(20) not null,
[Sex] varchar(4) default '男' not null,
[Age] tinyint default 20 check([Age]>0),
[weight] int check([weight]>0),
[Department] varchar(20) null,
[JobTitle] varchar(20) null,
[Salary] money check([Salary]>0),
[Increase] money default 0.2,
[workDay] tinyint default 30,
[Re] text null
)
1.我涉及的表就是2个TFAT_INVOICE和TFAT_CLIENT_PROFILE(关联字段是:SITE_CODE和CLIENT_CODE )
2.我想从TFAT_INVOICE表中取出3个字段INVOICE_ID,client_code,INVOICE_MATURITYDATE
3.其中有一个条件限制,就是TFAT_INVOICE表中 INVOICE_MATURITYDATE + "?" <= TO_DATE('2009-05-23','yyyy-mm-dd')
因为TFAT_INVOICE的CLIENT_CODE,有可能再TFAT_CLIENT_PROFILE中(如果存在,就把和CLIENT_CODE对应的invoice_grace取出来 ),也有可能不在TFAT_CLIENT_PROFILE中(如果不存在,就取10)
因此按我的理解,上面的 ”?“一定是一个 SQL (如果TFAT_INVOICE中的CLIENT_CODE存在于TFAT_CLIENT_PROFILE中,则取TFAT_CLIENT_PROFILE表中的invoice_grace,反之取10 )。帮帮忙 啊 ,不管用什么方法,只要能实现就可以 。
select distinct INVOICE_ID,INVOICE_MATURITYDATE
From TFAT_INVOICE a left join TFAT_CLIENT_PROFILE b
on (a.SITE_CODE=b.SITE_CODE and a.CLIENT_CODE=b.CLIENT_CODE )
where a.ADVANCE_ID Is Not Null And a.INVOICE_MATURITYDATE +isnull(b.CLIENT_CODE,10 ) <= TO_DATE('2009-05-23','yyyy-mm-dd') and a.INVOICE_STATUS = 'D' 試試.
1、条件式case 条件
when ..... then ...
when ......then ....
end
2、搜索式
case
when ......then ...else ... end
when ...... then ...else...end