英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论