画面上有2个非必需输入的输入框,输入的是codeFrom,codeTo 场景1
如果画面的输入框没有输入数据,选出以下数据
SELECT
TABLE.预定接收方code,
CASE
WHEN View.sysCode = 1 THEN
SELECT Table1.kouzaName1, 1 FROM Table1,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table1.kouzaCode1
WHEN View.sysCode = 2 THEN
SELECT Table2.kouzaName2, 2 FROM Table2,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table2.kouzaCode2
ELSE '',''
END AS 预定接受方名称,syscode
FROM TABLE 场景2
如果画面的输入框输入了数据,选出以下数据
SELECT
预定接收方code,
CASE
WHEN View.sysCode = 1 THEN
SELECT Table1.kouzaName1, 1 FROM Table1,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table1.kouzaCode1
WHEN View.sysCode = 2 THEN
SELECT Table2.kouzaName2, 2 FROM Table2,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table2.kouzaCode2
ELSE '',''
END AS 预定接受方名称,syscode
FROM TABLE
WHERE
if LEN(@画面输入codeFrom) >0
BEGIN
SET @SQL = @SQL + ' syscode = 1 AND 预定接收方code >= ' + CHAR(39) +@画面输入codeFrom + CHAR(39) + ' '
END
if LEN(@画面输入codeTo) >0
BEGIN
SET @SQL = @SQL + ' syscode = 1 AND 预定接收方code <= ' + CHAR(39) +@画面输入codeTO + CHAR(39) + ' '
END
不知道这么写对不对。
如果画面的输入框没有输入数据,选出以下数据
SELECT
TABLE.预定接收方code,
CASE
WHEN View.sysCode = 1 THEN
SELECT Table1.kouzaName1, 1 FROM Table1,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table1.kouzaCode1
WHEN View.sysCode = 2 THEN
SELECT Table2.kouzaName2, 2 FROM Table2,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table2.kouzaCode2
ELSE '',''
END AS 预定接受方名称,syscode
FROM TABLE 场景2
如果画面的输入框输入了数据,选出以下数据
SELECT
预定接收方code,
CASE
WHEN View.sysCode = 1 THEN
SELECT Table1.kouzaName1, 1 FROM Table1,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table1.kouzaCode1
WHEN View.sysCode = 2 THEN
SELECT Table2.kouzaName2, 2 FROM Table2,View WHERE View.code = TABLE.分类 AND View.kubun = 16 AND TABLE.预定接收方code = Table2.kouzaCode2
ELSE '',''
END AS 预定接受方名称,syscode
FROM TABLE
WHERE
if LEN(@画面输入codeFrom) >0
BEGIN
SET @SQL = @SQL + ' syscode = 1 AND 预定接收方code >= ' + CHAR(39) +@画面输入codeFrom + CHAR(39) + ' '
END
if LEN(@画面输入codeTo) >0
BEGIN
SET @SQL = @SQL + ' syscode = 1 AND 预定接收方code <= ' + CHAR(39) +@画面输入codeTO + CHAR(39) + ' '
END
不知道这么写对不对。
syscode = 1 AND 预定接收方code between @画面输入codeFrom and @画面输入codeTO
syscode = 1 AND 预定接收方code between @画面输入codeFrom and @画面输入codeTO and len(ltrim(@画面输入codeFrom)>0 and len(ltrim(@画面输入codeTO)>0
Sorry,我把代码反复看了三遍,没理解楼主最终想要实现的结果,真是爱莫能助.
(2)当这两个输入框中有任意一个或二个录入有值的时候,只能查出syscode=1的数据。
如果View的syscode是1,应该从tableB中查询name,
如果View的syscode是2,应该从tableC中查询name。
如果View的syscode是1,2以外的, 设置name为空。
如果画面上输入了codeFrom或codeTo,再过滤一遍结果集,使得只显示syscode=1的数据。