关于日期的Excel函数(Excel function about Date)
我想知道从excel中的2016年1月9日开始获得.01.09.2016的函数。我使用了这个函数“= DATE(LEFT(A1,4),MID(A1,4,20),RIGHT(A1,2 ))“但是这会给出从09/01/2016起的结果17.08.4243,并且还将这两个单元定制为dd / mm / yyyy和dd.mm.yyyy。
i want to know the function to get .01.09.2016 from 1/9/2016 in excel.I applied this function "=DATE(LEFT(A1,4),MID(A1,4,20),RIGHT(A1,2))" but this gives result 17.08.4243 from 09/01/2016 and also customized these two cells as dd/mm/yyyy and dd.mm.yyyy.
原文:https://stackoverflow.com/questions/40500912
最满意答案
就像错误所说的那样,你不能执行聚合函数(在这种情况下,在子查询中使用
sum()
)。 相反,将其他表连接到FROM
子句:SELECT Sum(IsNull(CircuitCourtFeeCap, 0) + IsNull(CircuitCourtCourtTime, 0) + IsNull(CircuitCourtWaiverFee, 0) + IsNull(CircuitCourtExpenseFee, 0)) AS TotalApproved, Sum(IsNull(e.FeeAmountCap, 0)) AS TotalFeeCap, Sum(IsNull(ct.CourtTime, 0)) AS TotalCourtTime FROM DocumentNames dn LEFT OUTER JOIN (SELECT DocumentNameID, sum(claimed) as CourtTime FROM [dbo].[CourtTimes] GROUP BY DocumentNameID) ct ON ct.DocumentNameID = dn.DocumentNameID LEFT OUTER JOIN Expenses e ON dn.DocumentNameID = e.DocumentNameID;
更新了此答案,将[CourtTimes]表连接移动到子查询中,以便在加入之前可以在DocumentNameID级别预先聚合。
Just like the error says, you can't perform an aggregate function (
sum()
in this case on a subquery). Instead, join that other table in on yourFROM
clause:SELECT Sum(IsNull(CircuitCourtFeeCap, 0) + IsNull(CircuitCourtCourtTime, 0) + IsNull(CircuitCourtWaiverFee, 0) + IsNull(CircuitCourtExpenseFee, 0)) AS TotalApproved, Sum(IsNull(e.FeeAmountCap, 0)) AS TotalFeeCap, Sum(IsNull(ct.CourtTime, 0)) AS TotalCourtTime FROM DocumentNames dn LEFT OUTER JOIN (SELECT DocumentNameID, sum(claimed) as CourtTime FROM [dbo].[CourtTimes] GROUP BY DocumentNameID) ct ON ct.DocumentNameID = dn.DocumentNameID LEFT OUTER JOIN Expenses e ON dn.DocumentNameID = e.DocumentNameID;
Updated this answer to move the [CourtTimes] table join into a subquery so it could be pre-aggregated at the DocumentNameID level before join.
相关问答
更多-
QueryDSL子查询不起作用(QueryDSL subquery not working)[2022-08-05]
修复。 我只需要改变.where(qRoom.unit.eq(subQuery)); .where(qRoom.unit.in(subQuery.list(qRuimte.unit))); Fixed it. I just needed to change .where(qRoom.unit.eq(subQuery)); too .where(qRoom.unit.in(subQuery.list(qRuimte.unit))); -
您可以使用函数而不是“子查询”,例如: SELECT SUM(CASE WHEN CRMBNB_DATE_1.AccountingFiscalPeriod = **Fn_XXX()** THEN BL_Amount ELSE 0 END) AS [MTD Billing] FROM CRMBNB_BILLINGS INNER JOIN CRMBNB_DATE AS CRMBNB_DATE_1 ...
-
为什么这个查询在oracle和mysql中有错误是正确的 因为MySQL对混合聚合和非聚合列很容易。 引用文档 : 在标准SQL中,包含GROUP BY子句的查询不能引用选择列表中未在GROUP BY子句中命名的非聚合列。 [...] MySQL扩展了GROUP BY的使用,因此选择列表可以引用GROUP BY子句中未命名的非聚合列。 [...] 这主要适用于每个未在GROUP BY中命名的非聚合列中的所有值对于每个组都相同的情况。 服务器可以自由选择每个组中的任何值,因此除非它们相同,否则所选的值是不确定 ...
-
使用子查询的sum函数(sum function using subquery)[2022-01-09]
尝试这个: select c.customer_id, c.first_name, a.account_id, sum(case when r.transaction_type='deposit' then r.Amount else 0 end) as Debit, sum(case when r.transaction_type='withdrawal' then r.Amount else 0 ... -
删除额外的CASE关键字 SELECT @TotalDaysInThisYear = COALESCE(SUM( CASE WHEN t.DateFrom < @BeginOfTheYear AND t.DateTo >= @EndOfTheYear THEN DATEDIFF(day, @BeginOfTheYear, t.DateTo) + 1 ...
-
MySQL子查询SUM限制(MySQL Subquery SUM Limit)[2021-11-14]
不幸的是,MySQL限制了表别名的范围。 Oracle是另一个执行此操作的数据库。 您可以将查询短语称为复杂的连接: select t1.id, sum(t2.number) from t1 p join t2 on p.id = t2.u_id where 30 >= (select count(*) from t2 t22 where t22.u_id = t2.u_id and t22.tim ... -
使用GROUP BY时不需要DISTINCT 格式化代码 不需要相关的子查询,只需简单的聚合函数SUM LiveDemo CREATE TABLE #table1(field1 INT, field2 INT, field3 INT, field4 INT); INSERT INTO #table1 VALUES(1, 201510, 3, 1111); INSERT INTO #table1 VALUES(1, 201510, 3, 1111); INSERT INTO #table1 VALUES(1 ...
-
子查询和SUM(Subquery and SUM)[2022-03-22]
Select chain_name, sum(price) From chain a Inner join (Select a.hotel_no, sum(room_price) as price, b.chainid From room a Inner join hotel b On a.hotel_no = b.hotel_no Group by a.hotel_no) b On a.Id =b.chainid 按chain_name分组 Just to answer myself ... -
就像错误所说的那样,你不能执行聚合函数(在这种情况下,在子查询中使用sum() )。 相反,将其他表连接到FROM子句: SELECT Sum(IsNull(CircuitCourtFeeCap, 0) + IsNull(CircuitCourtCourtTime, 0) + IsNull(CircuitCourtWaiverFee, 0) + IsNull(CircuitCourtExpenseFee, 0)) AS TotalApproved, Sum(IsNull(e.FeeAmou ...
-
我想你可以在你要计算的字段上使用带有DISTINCT COUNT : COUNT(DISTINCT CASE WHEN soc.tobacco_user_c IN (1, 2, 4, 5) THEN pat.pat_id END) AS Compliant I think you can use COUNT with a DISTINCT on the field that you want to count: COUNT(DISTINCT CASE WHEN soc.tobacco_user_c IN ...