我有一个表[tblFull1 04WoodsideTransportV335],所有数据都来自该表。我想得到[Data Location]和[load_case],其中应力是Max、Min或Abs。它适用于Max和Min。但由于某些原因,它不适用于Abs。它在ms Access 2013中运行。我使用vba以编程方式生成查询。
Abs和max,min运算符之间有什么区别吗?
当Abs查询运行时,出于某种原因,它会广告"As Expr1",为什么要这样做?
我知道SQL看起来很糟糕,但它有效。如果你对如何改进它有任何建议,请告诉我。
抱歉我英语不好,我是挪威人。
带MAX函数的SQL:
SELECT DISTINCT
t1.[load_case], t2.[Data Location] AS Nodenr,
t2.[MaxOfHoop Stress N/mm^2]
FROM
[tblFull1 04WoodsideTransportV335] AS t1,
(SELECT
[tblFull1 04WoodsideTransportV335].[Data Location],
MAX([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2]) AS [MaxOfHoop Stress N/mm^2]
FROM
[tblFull1 04WoodsideTransportV335]
GROUP BY
[tblFull1 04WoodsideTransportV335].[Data Location]
HAVING ((([tblFull1 04WoodsideTransportV335].[Data Location])
In (SELECT [Data Location]
FROM [tblFull1 04WoodsideTransportV335]
GROUP BY [Data Location]
HAVING Count(*) > 1))
AND ((MAX([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2])) Is Not Null))
ORDER BY
[tblFull1 04WoodsideTransportV335].[Data Location]) AS t2
WHERE
((t1.[Hoop Stress N/mm^2]) = [t2].[MaxOfHoop Stress N/mm^2])
ORDER BY
t1.[load_case] DESC;
带Abs函数的SQL:
SELECT DISTINCT
t1.load_case, t2.[Data Location] AS Nodenr,
t2.[AbsOfHoop Stress N/mm^2] AS Expr1
FROM
[tblFull1 04WoodsideTransportV335] AS t1,
(SELECT
[tblFull1 04WoodsideTransportV335].[Data Location],
ABS([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2]) AS[AbsOfHoop Stress N/mm^2]
FROM [tblFull1 04WoodsideTransportV335]
GROUP BY [tblFull1 04WoodsideTransportV335].[Data Location]
HAVING ((([tblFull1 04WoodsideTransportV335].[Data Location])
In (SELECT [Data Location]
FROM [tblFull1 04WoodsideTransportV335]
GROUP BY [Data Location]
HAVING Count(*) > 1))
AND ((ABS([tblFull1 04WoodsideTransportV335].[Hoop Stress N/mm^2])) Is Not Null))
ORDER BY
[tblFull1 04WoodsideTransportV335].[Data Location]) AS t2
WHERE
(((t1.[Hoop Stress N/mm^2])=[t2].[AbsOfHoop Stress N/mm^2]))
ORDER BY
t1.load_case DESC;
在SQL中,在"HAVING"语句中,只能使用在"GROUP BY"中定义的列。对于其他列,必须在聚合函数中使用then,它们是:-AVG()-返回平均值-COUNT()-返回行数-FIRST()-返回第一个值-LAST()-返回最后一个值-MAX()-返回最大值-MIN()-返回最小值-SUM()-返回的总和