我需要帮助将这两个查询合并。

huangapple go评论143阅读模式
英文:

I need help combining these two queries

问题

SELECT
    CASE
        WHEN C1 LIKE '% %' 
            THEN LEFT(c1, CHARINDEX(' ', c1) - 1)
            ELSE c1
    END AS c1,
    CASE
        WHEN c1 LIKE '% %' 
            THEN RIGHT(c1, CHARINDEX(' ', REVERSE(c1)) - 1)
    END AS c1_split,
    c2, c3, c4,
    c5, c6, c7
FROM 
    Table1 
JOIN 
    Table2 ON c3 = t2c2                          
WHERE 
    c4 = 'A' AND c5 = ' '
英文:
SELECT
    CASE
    	WHEN C1 LIKE '% %' 
            THEN LEFT(c1, CHARINDEX(' ', c1) -1)
    		ELSE c1
	END,
	CASE
		WHEN c1 LIKE '% %' 
            THEN RIGHT(c1, CHARINDEX(' ', REVERSE(c1)) -1)
	END
FROM
    Table1
SELECT
    c1, c2, c3, c4,
    c5, c6, c7
FROM 
    Table1 
JOIN 
    Table2 ON c3 = t2c2                          
WHERE 
    c4 = 'A' AND c5 = ' ' AND

Both of these queries work fine individually but I need the c1 column have that split the first query produces. I am just unsure how to add the function to the second query.

答案1

得分: 0

以下是翻译好的部分:

"I's assuming the 2 C1 references in the first block of code is a typo and it should have a different CASE but amend as you see fit:" -> "我假设在第一个代码块中的两个C1引用是一个打字错误,应该有不同的CASE,但请根据需要进行修正:"

"SELECT CASE" -> "选择 CASE"

"WHEN C1 LIKE '% %'" -> "当C1类似于'% %'"

"THEN LEFT(C1, Charindex(' ', C1) -1)" -> "然后 LEFT(C1, Charindex(' ', C1) -1)"

"ELSE C1" -> "否则 C1"

"END C1," -> "结束 C1,"

"CASE" -> "CASE"

"WHEN C1 LIKE '% %'" -> "当C1类似于'% %'"

"THEN RIGHT(C1, Charindex(' ', reverse(C1)) -1)" -> "然后 RIGHT(C1, Charindex(' ', reverse(C1)) -1)"

"END C1," -> "结束 C1,"

"C2, C3, C4, C5, C6, C7" -> "C2,C3,C4,C5,C6,C7"

"FROM Table1" -> "从 Table1"

"JOIN Table2 ON C3 = T2C2" -> "加入 Table2 ON C3 = T2C2"

"WHERE C4 = 'A'" -> "其中 C4 = 'A'"

"AND C5 = ' '" -> "并且 C5 = ' '"

"AND ???" -> "并且 ???"

英文:

I's assuming the 2 C1 references in the first block of code is a typo and it should have a different CASE but amend as you see fit:

SELECT CASE
       WHEN C1 LIKE '% %' 
       THEN LEFT(C1, Charindex(' ', C1) -1)
       ELSE C1
       END C1,
       CASE
       WHEN C1 LIKE '% %'
       THEN RIGHT(C1, Charindex(' ', reverse(C1)) -1)
       END C1,
C2, C3, C4, C5, C6, C7
FROM Table1 
JOIN Table2 ON C3 = T2C2                          
WHERE C4 = 'A' 
AND C5 = ' ' 
AND ???

答案2

得分: 0

如果您不希望在最终的选择列表中添加复杂的表达式以避免混杂,您可以使用CROSS APPLY来计算这些值作为中间结果。

SELECT
    A.C1Left
    ,A.C1Right
    ,c2
    ,c3
    ,c4
    ,c5
    ,c6
    ,c7
FROM Table1 T1
JOIN Table2 T2
    ON c3 = t2c2
CROSS APPLY (
    SELECT
        CASE WHEN T1.c1 LIKE '% %'
            THEN LEFT(T1.c1, CHARINDEX(' ', T1.c1) -1)
            ELSE T1.c1
            END AS C1Left,
        CASE WHEN T1.c1 LIKE '% %'
            THEN RIGHT(T1.c1, CHARINDEX(' ', REVERSE(T1.c1)) -1)
            END AS C1Right
) A
WHERE c4 = 'A' AND c5 = ' ' AND ...

CROSS APPLY类似于对子查询的连接,其中子查询可以引用FROM/JOIN序列的早期部分。然后可以在后续的连接条件、WHERE子句和选择列表中使用这些结果(如上所示)。甚至可以有多个交叉应用,每个都可以基于其他的结果构建。

作为一种附注,最好为所有的表引用取别名,然后使用这些别名明确地限定对这些表的所有引用。此外,我建议选择并坚持使用一致的大小写和缩进样式。稍后阅读您的代码的人会感激不已。混合使用不同样式会非常分散注意力。

英文:

If you prefer to not clutter up your final select list with complex expressions, you can use a CROSS APPLY to calculate these values as intermediate results.

SELECT
    A.c1Left
    ,A.c1Right
    ,c2
    ,c3
    ,c4
    ,c5
    ,c6
    ,c7
FROM Table1 T1
JOIN Table2 T2
    ON c3 = t2c2
CROSS APPLY (
    SELECT
        CASE WHEN T1.c1 LIKE '% %'
            THEN LEFT(T1.c1, CHARINDEX(' ', T1.c1) -1)
            ELSE T1.c1
            END AS C1Left,
        CASE WHEN T1.c1 LIKE '% %'
            THEN RIGHT(T1.c1, CHARINDEX(' ', REVERSE(T1.c1)) -1)
            END AS c1Right
) A
WHERE c4 = 'A' AND c5 = ' ' AND ...

A CROSS APPLY is like a join to a subselect, where that subselect can reference earlier parts of the FROM/JOIN sequence. The results can then be used on later join conditions, where clauses, and the select list (as above). You can even have multiple cross-applies that each build upon the results of one another.

As a side note, it is good practice to alias all of your table references and then use the aliases to explicitly qualify all references back to those tables. Also, I recommend picking and sticking to a consistent capitalization and indentation style. Those who try to later read your code will thank you. Having a mix is very distracting.

huangapple
  • 本文由 发表于 2023年6月29日 05:30:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76576830.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定