- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
前几天我在 SQL Server 中编写了一个查询,并且必须得到一个添加到查询中的列的总和。
现在,我必须添加 sum 列的查询已经有大约 20 个选定的列。
为了使总和(聚合函数)起作用,我必须为每个预先选择的列添加 GROUP BY
。
这最终成为一项极其乏味和无聊的任务(请参阅下面的查询)。我必须在查询中添加大约 50 行新行,才能让一列的 SUM 起作用。
有没有办法使用 SQL Server Management Studio 自动编写此类查询?
其他数据库(如PL/SQL、Oracle、MySQL)也是这样吗?
这就是查询的样子。
SELECT
mvLogisticContracts.[mvLogisticContract_id]
,mvLogisticContracts.[CONTRACTNUMBER]
,mvLogisticContracts.[CUSTNMBR]
,mvLogisticContracts.[DateCreated]
,mvLogisticContracts.[TruckAllocatedTotal] ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal] ,mvLogisticContractLineItems.[LineItemQuantity] ,mvLogisticContractLineItems.[LineItemNo]
,mvLogisticContractLineItems.[OffloadRequired]
,mvLogisticContractLineItems.[CarrierRequested] ,mvLogisticContractLineItems.[RequestedSerialNo]
,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
,mvLogisticContractLineItems.[DateReadyBy] ,mvLogisticContractLineItems.[DateCustomerRequested],
mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
mvLogisticServiceTypes.ServiceType,
mvLogisticStatuses.Description,
mvLogisticSKUs.[SKU], mvLogisticSKUs.[Length],
a1.CITY as \"CityStart\", a1.AddressName as \"AddressNameStart\", a1.Address1 as \"Address1Start\", a1.STATE as \"StateStart\", a1.ZIP as \"ZipStart\",
a2.CITY as \"CityEnd\", a2.AddressName as \"AddressNameEnd\", a2.Address1 as \"Address1End\", a2.STATE as \"StateEnd\", a2.ZIP as \"ZipEnd\",
mvLogisticContracts.[mvLogisticAddress_id_Start] AS aa1,
mvLogisticContracts.[mvLogisticAddress_id_End] AS aa2,
SUM(mvLogisticReleases.ReleaseQuantity) as ReleaseQtySum
FROM mvLogisticContractLineItems
LEFT JOIN mvLogisticContracts ON mvLogisticContractLineItems.mvLogisticContract_id = mvLogisticContracts.mvLogisticContract_id
LEFT JOIN mvLogisticSalespersons ON mvLogisticContracts.[mvLogisticSalesperson_id] = mvLogisticSalespersons.[mvLogisticSalesperson_id]
LEFT JOIN mvLogisticServiceTypes ON mvLogisticContractLineItems.mvLogisticServiceType_id = mvLogisticServiceTypes.mvLogisticServiceType_id
LEFT JOIN mvLogisticStatuses ON mvLogisticContractLineItems.mvLogisticStatus_id = mvLogisticStatuses.mvLogisticStatus_id
LEFT JOIN mvLogisticSKUs ON mvLogisticContractLineItems.[SKU_id] = mvLogisticSKUs.[SKU_id]
LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContractLineItems.[mvLogisticAddress_id_End]
/*LEFT JOIN mvLogisticAddresses a1 ON a1.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_Start]
LEFT JOIN mvLogisticAddresses a2 ON a2.[mvLogisticAddress_id] = mvLogisticContracts.[mvLogisticAddress_id_End]*/
LEFT JOIN mvLogisticReleases ON mvLogisticContractLineItems.mvLogisticContractLineItem_id = mvLogisticReleases.mvLogisticContractLineItem_id
GROUP BY
mvLogisticContracts.[mvLogisticContract_id]
,mvLogisticContracts.[CONTRACTNUMBER]
,mvLogisticContracts.[CUSTNMBR]
,mvLogisticContracts.[DateCreated]
,mvLogisticContracts.[TruckAllocatedTotal] ,mvLogisticContracts.[mvLOgisticAddress_id_StartOriginal]
,mvLogisticContracts.[mvLogisticAddress_id_StartOriginal] ,mvLogisticContractLineItems.[LineItemQuantity] ,mvLogisticContractLineItems.[LineItemNo]
,mvLogisticContractLineItems.[OffloadRequired]
,mvLogisticContractLineItems.[CarrierRequested] ,mvLogisticContractLineItems.[RequestedSerialNo]
,mvLogisticContractLineItems.[mvLogisticAddress_id_Start]
,mvLogisticContractLineItems.[DateReadyBy] ,mvLogisticContractLineItems.[DateCustomerRequested],
mvLogisticContractLineItems.[mvLogisticContractLineItem_id]
,mvLogisticSalespersons.[FirstName],mvLogisticSalespersons.[LastName],
mvLogisticServiceTypes.ServiceType,
mvLogisticStatuses.Description,
mvLogisticSKUs.[SKU],
mvLogisticSKUs.[Length],
a1.CITY, a1.AddressName, a1.Address1, a1.STATE, a1.ZIP,
a2.CITY, a2.AddressName, a2.Address1, a2.STATE, a2.ZIP,
mvLogisticContracts.[mvLogisticAddress_id_Start],
mvLogisticContracts.[mvLogisticAddress_id_End]
最佳答案
子查询和公用表表达式 (CTE) 是将聚合隔离到易于连接到主 SELECT 语句的本地化点的两种方法。
相关/同步子查询示例:
SELECT c.contract_id, c.contractnum, c.customernum,
( SELECT SUM( li.price * li.orderquantity )
FROM lineitems li
WHERE li.contract_id = c.contract_id
) AS grand_total
FROM contracts c
WHERE ...
在您的 SELECT 列表中引用相关子查询是返回单个值的便捷方式。
加入 CTE:
WITH ordersummary AS
( SELECT li.contract_id,
COUNT( DISTINCT li.partnum ) AS distinctproducts,
SUM( li.orderquantity ) AS itemcount,
SUM( li.price * li.orderquantity) AS totalprice,
MIN( p.ship_ready_date ) AS earliest_partial_ship_date,
MAX( p.ship_ready_date ) AS earliest_complete_ship_date
FROM lineitems li
INNER JOIN parts p
ON p.partnum = li.partnum
GROUP BY li.contract_id
)
SELECT c.contract_id, c.contractnum, c.customernum,
s.distinctproducts, s.itemcount, s.totalprice,
s.earliest_partial_ship_date, s.earliest_complete_ship_date
FROM contracts c
INNER JOIN ordersummary s
ON s.contract_id = c.contract_id
WHERE ...
加入表达式:
SELECT c.contract_id, c.contractnum, c.customernum,
s.distinctproducts, s.itemcount, s.totalprice,
s.earliest_partial_ship_date, s.earliest_complete_ship_date
FROM contracts c
INNER JOIN
( SELECT li.contract_id,
COUNT( DISTINCT li.partnum ) AS distinctproducts,
SUM( li.orderquantity ) AS itemcount,
SUM( li.price * li.orderquantity) AS totalprice,
MIN( p.ship_ready_date ) AS earliest_partial_ship_date,
MAX( p.ship_ready_date ) AS earliest_complete_ship_date
FROM lineitems li
INNER JOIN parts p
ON p.partnum = li.partnum
GROUP BY li.contract_id
) AS s
ON s.contract_id = c.contract_id
WHERE ...
关于SQL 服务器 : aggregate functions and GROUP BY in long queries,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12486079/
我找到了long int long和 int long long可以编译为变量类型。 long int long有什么区别吗, int long long , long long和 long long
我无法找出为什么“加密”函数仍然将“消息”读取为字符串,尽管我已经使用不同的方法将数据类型更改为字节。 错误消息是“Prince 类型中的方法 Encrypt(long, long, long, lo
这个问题在这里已经有了答案: Is "long long" = "long long int" = "long int long" = "int long long"? (4 个答案) 关闭 6 年
我正在从 Java 过渡到 C++,并且对 long 数据类型有一些疑问。在 Java 中,要保存大于 232 的整数,您只需编写 long x;。但是,在 C++ 中,long 似乎既是数据类型又是
clang-tidy 12.0.1 报告了一个相当奇怪的警告。在以下代码中: #include int main() { std::vector v1; const auto a =
我创建了一个 pair 和 long long int 的映射 - map,long long int >; 和一个交互器 - map, long long int >::iterator it1;
我想知道 unsigned long long 和 unsigned long long int 的主要区别。它们可以互换使用吗? 对于像 9223372036854775807 这样的大十进制数的计
我看到的大多数代码都使用缩写类型来声明变量,例如 long long x; // long long int x short y; // short int y 我浏览了 C++11 标准(第 3.9
common_type::type是 unsigned long因为关于积分提升后的操作数,标准说... [...] if the operand that has unsigned integer
long long int A = 3289168178315264; long long int B = 1470960727228416; double D = sqrt(5); long lon
这些新数据类型的目的是什么?我通常只使用“int”或“long”,但为什么会存在这些呢?它们带来了什么新功能或用途? 最佳答案 long int一直是long的全称,只是很少用而已。 long lon
我正在运行以下for循环 for(unsigned long long int i = N-1; i >= 0; i--){ cin>>L[i]; } 当程序到达这个代码段时,它停止响应。但是
最近问了一个关于递归导致这个问题的问题 注意-> count() 函数返回键 K 在 map 容器中出现的次数。如果键存在于容器中,则返回 1,因为映射仅包含唯一键。如果 map 容器中不存在键,则返
好的,所以我正在尝试实现客户端 - 服务器程序(套接字编程)。 我的客户发送一个嵌入字符串中的 long long int,如下所示: char copy[10]; sprintf(send_data
如果我有任务 Long c = a + b; 有没有一种简单的方法来检查 a + b 不大于/小于 Long.MAX_VALUE/Long.MIN_VALUE? 最佳答案 使用 Guava , 就这么
我需要制作一个 Comparator 来根据它的 long 类型的变量之一对我的对象列表进行排序。 public class ParticipantIndexComparator implements
假设我有这两种类型: typedef unsigned long long uint64; typedef signed long long sint64; 我有这些变量: uint64 a = ..
long long 和 long 有什么区别?而且它们都不适用于 12 位数字 (600851475143),我是不是忘记了什么? #include using namespace std; int
当结果将大于 C 中的 long long int 时,是否有可能对两个不同的 long long int 变量求和? 最佳答案 由于 OP 想要“在屏幕上打印结果”,因此将数字分成两部分:Most-
实际上我必须找到从源顶点到所有其他顶点的最短路径。为此,我获得了下面给出的代码模板。我想实现“Bellman–Ford algorithm”。 #include #include #include
我是一名优秀的程序员,十分优秀!