如何在数据集里写IF语句

求帮忙看下哪里错了

${IF(统计条件="OUT","SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT 
FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID 
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID  AND C.UNITSTYPEID='0401'
INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL
INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501A E ON D.SETTLEID = E.SETTLEID
WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.SETTLEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.SETTLEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')
GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME",IF(统计条件="IN","SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT 
FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID 
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID  AND C.UNITSTYPEID='0401'
INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL
INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601A E ON d.CHARGEID = E.CHARGEID
WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.CHARGEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.CHARGEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')
GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME",IF(统计条件="ALL","SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT 
FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID 
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID  AND C.UNITSTYPEID='0401'
INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL
INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501A E ON D.SETTLEID = E.SETTLEID
WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.SETTLEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.SETTLEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')
GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME
UNION ALL
SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT 
FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID 
INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID  AND C.UNITSTYPEID='0401'
INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL
INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601A E ON d.CHARGEID = E.CHARGEID
WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.CHARGEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.CHARGEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')
GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME","")))}

FineReport Zod1ac 发布于 2021-1-5 10:14 （编辑于 2021-1-5 11:41）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共4回答

最佳回答

fangqingLv5初级互助
发布于2021-1-5 11:32（编辑于 2021-1-5 13:57）

${if(控件="门诊","SELECT * FROM A WHERE 时间大于开始日期小于结束日期",if(控件="住院","SELECT * FROM B WHERE 时间大于开始日期小于结束日期","SELECT * FROM C WHERE 时间大于开始日期小于结束日期"))}；

${IF(统计条件='OUT',

"SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501A E ON D.SETTLEID = E.SETTLEID

WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.SETTLEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.SETTLEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME",

IF(统计条件='IN',

"SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601A E ON d.CHARGEID = E.CHARGEID

WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01' AND D.UNITSQUANTITY>0 AND E.CHARGEDATETIME>=TO_DATE('"+开始日期+"','yyyy-mm-dd hh24:mi:ss') AND E.CHARGEDATETIME<TO_DATE('"+结束日期+"','yyyy-mm-dd hh24:mi:ss')

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME",

IF(统计条件='ALL',

"SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501A E ON D.SETTLEID = E.SETTLEID

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME

UNION ALL

SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601A E ON d.CHARGEID = E.CHARGEID

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME","")))}

demo1Lv6见习互助
发布于2021-1-5 10:17

${if(条件,"select * from a","select * from b")}

举报收起评论(3)

Zod1ac(提问者) 前面不需要写SELECT吗。。。那我怎么把数据填进单元格

2021-01-05 10:19

demo1 回复 Zod1ac(提问者) 前面有if判断，看执行哪一段sql语句

2021-01-05 10:21

Zod1ac(提问者) 回复 demo1 那三段条件怎么写呢

2021-01-05 10:26

luojian0323Lv7资深互助
发布于2021-1-5 10:18

你要先告诉我你的逻辑

${if(时间参数="值1","select 门诊 from 表",if(时间参数='值2',"select 住院 from 表",if(时间参数="值3","select 全部字段 from 表","")))}

举报收起评论(2)

Zod1ac(提问者) 是${if(控件=\'门诊\',\"SELECT * FROM A WHERE 时间大于开始日期小于结束日期\",\"SELECT * FROM B WHERE 时间大于开始日期小于结束日期\")}这样

2021-01-05 10:22

luojian0323 回复 Zod1ac(提问者) 那不就是这样吗？把我上面语句改一改就好了。

2021-01-05 10:26

huxiaolongLv4见习互助
发布于2021-1-5 14:16（编辑于 2021-1-5 14:26）

用帆软if函数判断条件在确定sql，语句看起来比较复杂。从给的内容看应该是条件=in，一个sql1，条件=out则是，sql2，条件all的话是 sql1 union all sql2。那么可以把if写到sql里，如下:

SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NOP0501A E ON D.SETTLEID = E.SETTLEID

WHERE A.ITEMTYPEID LIKE '01%' AND B.ESSENTIALDRUGTYPEID ='01'

AND D.UNITSQUANTITY>0

AND E.SETTLEDATETIME>=TO_DATE('${开始日期}','yyyy-mm-dd hh24:mi:ss')

AND E.SETTLEDATETIME<TO_DATE('${结束日期}','yyyy-mm-dd hh24:mi:ss')

${if(统计条件 in ('OUT', 'ALL'), "and 1=1", "and 0=1")}

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME

UNION ALL

SELECT DISTINCT A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME,SUM(D.UNITSQUANTITY) UNITSQUANTITY,SUM(D.ROUNDREALAMOUNT) ROUNDREALAMOUNT

FROM NEWAY_HIS_HNXX_1210.NBDITEMMAIN A

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMMEDPROPERTY B ON A.ITEMID=B.ITEMID

INNER JOIN NEWAY_HIS_HNXX_1210.NBDITEMUNITS C ON B.ITEMID=C.ITEMID AND C.UNITSTYPEID='0401'

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601B D ON C.ITEMID = D.ITEMID AND C.UNITSSERIAL=D.UNITSSERIAL

INNER JOIN NEWAY_HIS_HNXX_1210.NIP0601A E ON d.CHARGEID = E.CHARGEID

WHERE A.ITEMTYPEID LIKE '01%'

AND B.ESSENTIALDRUGTYPEID ='01'

AND D.UNITSQUANTITY>0

AND E.CHARGEDATETIME>=TO_DATE('${开始日期}','yyyy-mm-dd hh24:mi:ss')

AND E.CHARGEDATETIME<TO_DATE('${结束日期}','yyyy-mm-dd hh24:mi:ss')

${if(统计条件 in ('IN', 'ALL'), "and 1=1", "and 0=1")}

GROUP BY A.ITEMSPEC,A.ITEMID,A.ITEMNAME,C.UNITSNAME

这样看起来简洁些。

举报收起评论(4)

Zod1ac(提问者) 鹅。。。报错了

2021-01-05 14:22

huxiaolong 回复 Zod1ac(提问者) union all 下面个0=1 前面忘加and了

2021-01-05 14:25

Zod1ac(提问者) 回复 huxiaolong 还是报错。。。。帆软对多统计途径太不友好了吧。。。

2021-01-05 14:33

huxiaolong 回复 Zod1ac(提问者) 看着没什么问题呀，不知道报的什么错

2021-01-05 15:56

5关注人数

540浏览人数

最后回答于：2021-1-5 14:26

活动推荐更多

热门课程更多

请选择关闭问题的原因

确定取消

提问

悬赏者（2）

撤回悬赏（2）

添加悬赏

请选择关闭问题的原因