qq_27498287
诸葛浪
采纳率0%
2019-01-22 16:56 阅读 6.5k

sql可以有if else那种判断吗?

需要根据查询情况对数据进行插入
插入的数据是根据某个子查询的结果进行插入,可不可以有那种ifelse的sql,当子查询的结果多于一条的时候就不执行插入,
当子查询结果等于一条则将这个查询结果插入,如果没有就不进行插入?
这是插入的代码,如果通配符匹配到了多个纪录则插入失败,怎么解决这个问题呢?

INSERT INTO temp (quancheng, tyshxydm)
VALUES
    (
        (
            SELECT
                distname
            FROM
                municipaldepartmentslist
            WHERE
                DISTNAME LIKE 'fweFD%'
        ),
        (
            SELECT
                deptid
            FROM
                municipaldepartmentslist
            WHERE
                DISTNAME LIKE 'EFW%'
        )
    )
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

3条回答 默认 最新

  • en_joker 孤芳不自賞 2019-01-22 17:16

    用case when 就可以实现if else。

    点赞 评论 复制链接分享
  • qq_36720114 德玛洗牙 2019-01-22 17:39

    一个case when 的实例:
    SELECT t1.P_DATE,t1.AREA,IFNULL(t2.I_CODE,'M_INDEX0009') AS I_CODE,
    case WHEN SumHour!=0 THEN ROUND((IFNULL(t2.M_E_CT,0))/((t1.SumHour)/10000),2) ELSE NULL END AS INDEX_VAL,
    case WHEN SumHour1!=0 THEN ROUND((IFNULL(t2.MY_E_CT,0))/((t1.SumHour1)/10000),2) ELSE NULL END AS TB_INDEX_VAL,
    case WHEN SumHour2!=0 THEN ROUND((IFNULL(t2.ML_E_CT,0))/((t1.SumHour2)/10000),2) ELSE NULL END AS HB_INDEX_VAL
    FROM tmp_q_fixed_index_001 t1
    LEFT JOIN
    tmp_q_fixed_index_002 t2
    ON
    t1.P_DATE = t2.P_DATE AND t1.AREA=t2.AREA
    WHERE t1.YXLX='1';

    点赞 评论 复制链接分享
  • CJHBDGB CJHBDGB 2019-01-22 18:46

    不知道你要达到什么效果,你的sql语句看起来很怪,正常来讲可以直接写:

    INSERT INTO temp (quancheng, tyshxydm)
    select distname,deptid  
    FROM  municipaldepartmentslist
    WHERE  +条件
    

    如果非要这样处理的话,SqlServer可以这样处理,mysql的话应该差不多吧:

      if( (select COUNT( distname)FROM municipaldepartmentslist WHERE  DISTNAME LIKE 'fweFD%' )>1 
            or (select COUNT( deptid)FROM municipaldepartmentslist WHERE  DISTNAME LIKE 'EFW%' )>1  )  
      begin
        INSERT INTO temp (quancheng, tyshxydm)
        VALUES
            (
                (
                    SELECT
                        distname
                    FROM
                        municipaldepartmentslist
                    WHERE
                        DISTNAME LIKE 'fweFD%'
                ),
                (
                    SELECT
                        deptid
                    FROM
                        municipaldepartmentslist
                    WHERE
                        DISTNAME LIKE 'EFW%'
                )
            )
    
      end
    
    点赞 评论 复制链接分享

相关推荐