我举个例子哈:有一个表(R),然后有这几个字段:金额(Money)、税务(Tax)、类型(Type)。要求:有A级对应的金额和税务,类型(Type)为:A, B级对应的金额和税务,类型(Type)为B, C级对应的金额和税务,类型(Type)为:C 。那么要求查询A、B、C级分别对应的税务和金额是多少?用一条SQL实现。我分三条写的话是这样子的:
Select Money,Tax from R where Type = 'A';
Select Money,Tax from R where Type = 'B';
Select Money,Tax from R where Type = 'C';
那么,合成一条SQL呢?
Select Money,Tax from R where Type = 'A';
Select Money,Tax from R where Type = 'B';
Select Money,Tax from R where Type = 'C';
那么,合成一条SQL呢?
Select Money,Tax from R where Type in ('A','B','C')
不知道对不?
金额 税务 级别
$10 200 A
$15 229 B
$20 260 C
.....那么大概的语句是这么的:select Money,Tax,
(case Type when 'A' then 'A级别',
when 'B' then 'B级别' ,
when 'C' then 'C级别'
else '数据错误' end ) as type_Level
from R
SELECT CASE WHEN type = 'a' THEN [money]
WHEN type = 'b' THEN [money]
WHEN type = 'c' THEN [money]
END [Money] ,
CASE WHEN type = 'a' THEN Tax
WHEN type = 'b' THEN Tax
WHEN type = 'c' THEN Tax
END Tax
FROM R
Select Money,Tax from R where Type = 'B'union all
Select Money,Tax from R where Type = 'C';
select U_LCInvAmt,U_LCTaxAmt
(case U_Format when '31' then 'A级别' ,
when '35' then 'B级别' ,
when '32' then 'C级别' ,
when '36' then 'D级别'
else '数据错误' end ) as type_Level from [@cps_tbl_OTAX]
系统会提示:“,”处有语法错误,是第一个'A级别',这个逗号处有错误!!!请问是很么原因?
非常感谢你的回复,不过有点错误,第一个case完全正确,可是再用一个case时就会提示第二个case处有语法错误,也就是说去掉第二个case...END 是完全正确的,难道只能用一个Case?那只能查一个字段了怎么办啊?
(case U_Format when '31' then 'A级别'
when '35' then 'B级别'
when '32' then 'C级别'
when '36' then 'D级别'
else '数据错误' end ) as type_Level from [@cps_tbl_OTAX]
这个吧你应该知道是什么问题的 不能有逗号
这样结果相当于你那三条语句
select r.* from R where typ in ('A','B','C') 可以达到要求!
我测试的结果如下:
create table R (money decimal(10,2) not null,tax nvarchar(10) not null,typ nvarchar(5) not null)
insert into R values(10.5,'200','A')
insert into R values(210.5,'200','B')
insert into R values(130.5,'200','c')
insert into R values(410.5,'200','d')
insert into R values(1140.5,'200','e')
insert into R values(210.5,'400','a')
insert into R values(130.5,'300','b')
insert into R values(110.5,'400','c')
insert into R values(1120.15,'300','d')
insert into R values(1120.5,'200','e')
insert into R values(1012.5,'200','a')
select r.* from R where typ in ('A','B','C')
SELECT CASE WHEN [type] = 'a' THEN [money]
WHEN [type]= 'b' THEN [money]
WHEN [type]= 'c' THEN [money]
END [Money] ,
CASE WHEN [type]= 'a' THEN Tax
WHEN [type]= 'b' THEN Tax
WHEN [type]= 'c' THEN Tax
END Tax
FROM R