FieldInit表
ValueInit ValueMem
☆ =0 And 0=
★ >=1 And 5>=
★★ >5 And 8>=
★★★ >8 And 20>=
★★★★ >20 And 999999999>=GoodsInfo表
GoodsID GoodsLibNumber
50 8
51 5
52 200
53 0
54 20生成结果如下,求SQL语句,谢谢!
GoodsID GoodsLibNumber ValueInit
50 8 ★★
51 5 ★
52 200 ★★★★
53 0 ☆
54 20 ★★★
ValueInit ValueMem
☆ =0 And 0=
★ >=1 And 5>=
★★ >5 And 8>=
★★★ >8 And 20>=
★★★★ >20 And 999999999>=GoodsInfo表
GoodsID GoodsLibNumber
50 8
51 5
52 200
53 0
54 20生成结果如下,求SQL语句,谢谢!
GoodsID GoodsLibNumber ValueInit
50 8 ★★
51 5 ★
52 200 ★★★★
53 0 ☆
54 20 ★★★
解决方案 »
- 这句sql语句怎么错了,在线等答案
- SQL SERVER 2000 求函數
- 这样一个正则表达式要怎么写?
- 50分问一个很简单的问题。(40分个第一个回答正确的,10分给友情UP的)
- 关于数据库加密的未公开函数pwdencrypt及pwdcompare的迷惑!!请高手解决!
- 如何开启XP_cmdshell
- 又一道面试题(月薪5000-6000):举例说明inner join和子查询什么时候可通用,什么时候又不能代替对方。
- update select问题
- 为何将SQLSERVER 2000改为MSDE后无法使用?
- SQL trn文件 备份还原问题,急求解答
- 在服务器端,我想查看所有正在连接到服务器端的用户的IP地址,怎样查看呀???
- 数据库owner创建表的问题
FieldInit表直接写到SQL语句里呢?SELECT GoodsID, GoodsLibNumber,
'ValueInit' = CASE WHEN GoodsLibNumber = 0 THEN '☆' WHEN GoodsLibNumber >= 1
AND 5 >= GoodsLibNumber THEN '★' WHEN GoodsLibNumber > 5 AND
8 >= GoodsLibNumber THEN '★★' WHEN GoodsLibNumber > 8 AND
20 >= GoodsLibNumber THEN '★★★' WHEN GoodsLibNumber > 20 AND
999999999 >= GoodsLibNumber THEN '★★★★★' END
FROM GoodsInfo
FieldInit表
ValueInit ValueMem
☆ =0 And 0=
★ >=1 And 5>=
★★ >5 And 8>=
★★★ >8 And 20>=
★★★★ >20 And 999999999>=
数据有问题。
应该
FieldInit表
ValueInit ValueMem
☆ =0 And 0=
★ >=1 And 5<=
★★ >5 And 8<=
★★★ >8 And 20<=
★★★★ >20 And 999999999<=
这个结果我认为不可取,既然直接case 出,那楼主还要 FieldInit 表干嘛?
把FieldInit表 里的 ValueMem 拼接进来
select GoodsID,(select ValueInit from FieldInit where (
(GoodsLibNumber =0 And 0= GoodsLibNumber and ValueMem='=0 And 0=') or
(GoodsLibNumber >=1 And 5>= GoodsLibNumber and ValueMem='>=1 And 5>=') or
(GoodsLibNumber >5 And 8>= GoodsLibNumber and ValueMem='>5 And 8>=') or
(GoodsLibNumber >8 And 20>= GoodsLibNumber and ValueMem='>8 And 20>=') or
(GoodsLibNumber >20 And 999999999>= GoodsLibNumber and ValueMem='>20 And 999999999>=')))
from GoodsInfo
select GoodsID,GoodsLibNumber,(select ValueInit from FieldInit where (
(GoodsLibNumber =0 And 0= GoodsLibNumber and ValueMem='=0 And 0=') or
(GoodsLibNumber >=1 And 5>= GoodsLibNumber and ValueMem='>=1 And 5>=') or
(GoodsLibNumber >5 And 8>= GoodsLibNumber and ValueMem='>5 And 8>=') or
(GoodsLibNumber >8 And 20>= GoodsLibNumber and ValueMem='>8 And 20>=') or
(GoodsLibNumber >20 And 999999999>= GoodsLibNumber and ValueMem='>20 And 999999999>='))) as '星级'
from GoodsInfo
设n=7
If n>5 And 8>=n Then Response.write'★★' End If
成立吧
理解,其实说句实话,FieldInit设计有问题。要我 我就会设计成为:
ValueInit ValueMem minnuber maxnuber数据结构:
☆ =0 And 0= 0 1
★ >=1 And 5>= 1 6
★★ >5 And 8>= 6 9
★★★ >8 And 20>= 9 21
★★★★ >20 And 999999999>= 21 100000000
insert into FieldInit
select '☆','=0 And 0=',0,1 union all
select '★','>=1 And 5>=',1,6 union all
select '★★','>5 And 8>=',6,9 union all
select '★★★','>8 And 20>=',9,21 union all
select '★★★★','>20 And 999999999>=',21,1000000000Create table GoodsInfo(GoodsID int,GoodsLibNumber int)
insert into GoodsInfo
select 50, 8 union all
select 51, 5 union all
select 52, 200 union all
select 53, 0 union all
select 54, 20 select GoodsID,GoodsLibNumber,
(select ValueInit from FieldInit where
GoodsLibNumber >=minNuber and GoodsLibNumber< maxNuber )
from GoodsInfo
/*
--------------结果------------
50 8 ★★
51 5 ★
52 200 ★★★★
53 0 ☆
54 20 ★★★
*/
是呀,当时FieldInit表是之前就有,作其他用途的,如果不修改表结构的情况下是否可行?当时在ValueMem里写表达式也是无奈之举
结果出来了select GoodsID,GoodsLibNumber,( select top 1 ValueInit from
(select ValueInit,Substring(Replace(Replace(Replace(ValueMem,'=',''),'>',''),' and ',','),0,
Charindex(',',Replace(Replace(Replace(ValueMem,'=',''),'>',''),' and ',','))) as minNuber,
Substring(Replace(Replace(Replace(ValueMem,'=',''),'>',''),' and ',','),
Charindex(',',Replace(Replace(Replace(ValueMem,'=',''),'>',''),' and ',','))+1,
Len(Replace(Replace(Replace(ValueMem,'=',''),'>',''),' and ',',')))+1 as maxNuber
from FieldInit) as a
where GoodsLibNumber>=minNuber and GoodsLibNumber<maxNuber)
from GoodsInfo
如果将FieldInit表里的数据修改下,再写一回
ValueInit ValueMem
☆ 0,0
★ 1,5
★★ 5,8
★★★ 8,20
★★★★ 20,999999999
(select ValueInit,
minv =case when charindex('=',left(valuemem,2)) >0 then convert(int,substring(replace(replace(valuemem,'=',''),'>',''),0,charindex('and',replace(replace(valuemem,'=',''),'>',''))))-1
else convert(int,substring(replace(replace(valuemem,'=',''),'>',''),0,charindex('and',replace(replace(valuemem,'=',''),'>','')))) end,
maxv = case when charindex('=',right(valuemem,2)) >0 then
convert(int,substring(replace(replace(valuemem,'=',''),'>',''),charindex('and',replace(replace(valuemem,'=',''),'>',''))+3,len(replace(replace(valuemem,'=',''),'>',''))))+1
else convert(int,substring(replace(replace(valuemem,'=',''),'>',''),charindex('and',replace(replace(valuemem,'=',''),'>',''))+3,len(replace(replace(valuemem,'=',''),'>','')))) end from tb1) t
inner join tb2 on tb2.goodslibnumber > minv and tb2.goodslibnumber < maxv
MSN联系我吧,和你一起商讨下。
MSN:[email protected]
-----数据----
create table FieldInit(ValueInit nvarchar(50),ValueMem nvarchar(100))
insert into FieldInit
select '☆','=0 And 0='union all
select '★','>=1 And 5>='union all
select '★★','>5 And 8>='union all
select '★★★','>8 And 20>=' union all
select '★★★★','>20 And 999999999>='Create table GoodsInfo(GoodsID int,GoodsLibNumber int)
insert into GoodsInfo
select 50, 8 union all
select 51, 5 union all
select 52, 200 union all
select 53, 0 union all
select 54, 20 ----定义2个自定义函数:---
---分别为:-----
---返回 minNuber-----
Create function f_test(@a nvarchar(1000))
returns nvarchar(1000)
as
begin
declare @Sum int
set @Sum=0
if Substring(@a,0,3) <> '>=' and Substring(@a,0,2) <> '='
begin
set @Sum=1
end
set @a=Substring(Replace(Replace(Replace(@a,'=',''),'>',''),' and ',','),0,Charindex(',',Replace(Replace(Replace(@a,'=',''),'>',''),' and ',',')))
set @a=cast((cast(@a as int)+@Sum)as nvarchar(1000))
return @a
end
---返回 maxNuber-----
Create function f_test2(@a nvarchar(1000))
returns nvarchar(1000)
as
begin
set @a=Substring(Replace(Replace(Replace(@a,'=',''),'>',''),' and ',','),
Charindex(',',Replace(Replace(Replace(@a,'=',''),'>',''),' and ',','))+1,
Len(Replace(Replace(Replace(@a,'=',''),'>',''),' and ',',')))+1
return @a
end----SQL---------
select GoodsID,GoodsLibNumber,( select ValueInit from FieldInit
where GoodsLibNumber>=dbo.f_test(ValueMem) and GoodsLibNumber<dbo.f_test2(ValueMem))
from GoodsInfo-----结果--------
50 8 ★★
51 5 ★
52 200 ★★★★
53 0 ☆
54 20 ★★★