我有一个用SQL开发的查询,并且运行良好,但是当我将查询放在SQL Server(或.Net)查询生成器中时,它会建立错误的查询。这是一个例子,就像我写的查询
这就是我写的(并且有效)
Select Case when Table1.[Col 1] is null then Table2.[Col 1] Else Table1.[Col 1] END as 'col1' From
(Select Sum(Table3.[Col 1]) as 'col 1', Table3.[groupby col] from Table3 Group by Table3.[groupbycol]) as Table1 FULL OUTER JOIN (Select Sum(Table3.[Col 1]) as 'col 1', 'Total' as 'groupby col' from Table3) as Table2 ON Table1.[groupby col] = Table2.[groupby col]
但是,如果我在sql或asp.net查询构建器中打开此“改进”,它将使查询中断,现在看起来像这样
Select
Case
when Table1. 'Col 1' is null
then Table2. 'Col 1'
Else Table1. 'Col 1'
END as 'col1'
From
(Select
Sum(Table3.[Col 1]) as 'col 1',
Table3.[groupby col]
from
Table3
group by
Table3.[groupbycol]) as Table1
FULL OUTER JOIN
(Select
Sum(Table3.[Col 1]) as 'col 1',
'Total' as 'groupby col'
from Table3 as Table3_1) as Table2 ON Table1. 'groupby col' = Table2. 'groupby col'
它不再起作用,因为它将[]替换为',并在列名称前添加了一个空格,并在第二个查询中为表3创建了别名,然后在使用该表的其他时间不将该别名分配给其他人
任何人都知道是什么原因造成的,因为如果每次我打开查询构建器时,它都可能使查询混乱,这可能会很烦人
谢谢
作为列别名的字符串文字在SQL Server的弃用列表中。尽管不是可移植的语法,但我一直是Alias = Expression
SQL Server语法的忠实拥护者。亚伦·伯特兰德(Aaron Bertrand)在他的文章“踢坏习惯:用AS代替=代替列别名”中也有一个很好的例子。
考虑到这一点,我将整个查询重写为:
SELECT Col1 = ISNULL(table1.Col1, table2.Col1)
FROM ( SELECT Col1 = SUM(table3.[Col 1]),
GroupByCol = Table3.[groupby col]
FROM Table3
) table1
FULL OUTER JOIN
( SELECT Col1 = SUM(table3.[Col 1]),
GroupByCol = 'Total'
FROM Table3
) table2
ON Table1.GroupByCol = Table2.GroupByCol;
或者
SELECT ISNULL(table1.Col1, table2.Col1) AS Col1
FROM ( SELECT SUM(table3.[Col 1]) AS Col1,
Table3.[groupby col] AS GroupByCol
FROM Table3
) AS table1
FULL OUTER JOIN
( SELECT SUM(table3.[Col 1]) AS Col1,
'Total' AS GroupByCol
FROM Table3
) AS table2
ON Table1.GroupByCol = Table2.GroupByCol;
我还怀疑您使用ROLLUP后实际上可以达到您想要的结果:
SELECT Col1 = SUM(table3.[Col 1]),
GroupByCol = ISNULL(Table3.[groupby col], 'Total')
FROM Table3
GROUP BY Table3.[groupby col]
WITH ROLLUP;
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句