对于IN子句中不存在列,但SQL运行感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解sql不在in里面怎么写，并且为您提供关于JOINON子句中的T-SQLCase语句、join子句中表达式

对于IN子句中不存在列,但SQL运行感兴趣的读者，本文将提供您所需要的所有信息，我们将详细讲解sql 不在in里面怎么写，并且为您提供关于JOIN ON子句中的T-SQL Case语句、join子句中表达式之一的类型在Entity Framework中不正确、josn字段中不存在数据或者不存在某个key进行sql查询时报错问题解决、MySQL IN子句中的多列的宝贵知识。

• IN子句中不存在列,但SQL运行（sql 不在in里面怎么写）
• JOIN ON子句中的T-SQL Case语句
• join子句中表达式之一的类型在Entity Framework中不正确
• josn字段中不存在数据或者不存在某个key进行sql查询时报错问题解决
• MySQL IN子句中的多列

IN子句中不存在列,但SQL运行（sql 不在in里面怎么写）

SELECT *
  FROM table A
  JOIN table B
    ON A.ID = B.ID
 WHERE B.AnotherColumn IN (SELECT Column FROM tableC WHERE ID = 1)

CREATE TABLE #test_main (colA integer) 
CREATE TABLE #test_sub (colB integer)

-- Works,because colA is available to the sub-query from the outer query. However,-- it's probably not what you intended to do:
SELECT * FROM #test_main WHERE colA IN (SELECT colA FROM #test_sub)

-- Doesn't work,because colC is Nowhere in either query
SELECT * FROM #test_main WHERE colA IN (SELECT colC FROM #test_sub)

-- Doesn't work,because colA is not in table #test_sub,so at least you get
-- notified that what you were trying to do doesn't make sense.
SELECT * FROM #test_main WHERE colA IN (SELECT #test_sub.colA FROM #test_sub)

JOIN ON子句中的T-SQL Case语句

我正在尝试在JOIN ON子句中构建case / if语句。

LEFT JOIN [CTSTRC] [Statuses] ON RIGHT([Statuses].[STRID], 3) = [CTE].[F61]

问题在于该列[Statuses].[STRID]包含文本和数字。我要与之比较的列[CTE].[F61]是一个整数。

有没有一种方法可以检测列[Statuses].[STRID]是否包含字符或数字，然后将其设置为0，然后将其设置为0？

这是一个伪查询可以帮助您：

LEFT JOIN [CTSTRC] [Statuses]     ON RIGHT((        CASE [Statuses].[STRID]             WHEN TEXT THEN 0             ELSE CAST([Statuses].[STRID] AS INT) END), 3) = [CTE].[F61]

答案1

您正在寻找IsNumeric，但它并不总是有效（+，-和。是数字），因此您需要使用GBN所描述的解决方案，即在您的varchar中添加.0e0

LEFT JOIN [CTSTRC] [Statuses] ON     (CASE WHEN ISNUMERIC(RIGHT([Statuses].[STRID], 3) + ''.0e0) = 1           THEN  CAST(RIGHT([Statuses].[STRID], 3) AS INT)           ELSE 0  END) = [CTE].[F61]

join子句中表达式之一的类型在Entity Framework中不正确

var query = from dpr in ctx.DPR_MM
            join q in ctx.QOT on dpr.DPR_QOT_ID equals qot_id
            join p in ctx.PAY_MM on new { q.QOT_SEC_ID,dpr.DPR_TS } equals new { p.PAY_SEC_ID,p.PAY_DATE }
            where q.QOT_ID = qot_id
            select new
            {
                dpr.dpr_ts,dpr.dpr_close,pay.First().pay_dividend
            };

join p in ctx.PAY on new { sec_id = (Int32)(q.QOT_SEC_ID),dpr.DPR_TS } equals new { sec_id = (Int32)p.PAY_SEC_ID,p.PAY_DATE }

josn字段中不存在数据或者不存在某个key进行sql查询时报错问题解决

//获取面试结果为已通过的
Integer passInterviewList = baseMapper.selectCount(new QueryWrapper<NeoCrmCustomerInterview>()
        .and(ele -> ele.eq("interview_status_init", CustomerInterviewEnum.PASS_WAIT_FEEDBACK.getCode()).or()
                .eq("interview_status_init", CustomerInterviewEnum.PASS_FEEDBACK.getCode()))
        //优化sql，interviewer_json未null或key不存在指定数据时会报错--优化兼容不存在问题
        //.last("and interviewer_json -> ''$.interviewerId'' like ''%" + id + "%''"));
        .last("CASE WHEN JSON_VALID(interviewer_json) THEN JSON_UNQUOTE(JSON_EXTRACT(interviewer_json, ''$.interviewerId'')) like ''%" + id + "%'' ELSE null END"));

MySQL IN子句中的多列

(x0, y0, x1, y1) IN ((4, 3, 5, 6),(9, 3, 2, 1))

(  ( x0 = 4 AND y0 = 3 AND x1 = 5 AND y1 = 6 ) 
OR ( x0 = 9 AND y0 = 3 AND x1 = 2 AND y1 = 1 )
)

SELECT * FROM my_table WHERE (x0,y0,x1,y1) IN ((4,3,5,6),...,(9,2,1));