欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

SqlServer

程序员文章站 2022-05-21 19:13:06
...

=背景介绍 -tableSales表 -第一次查询(2013销售员销售总额) select salesName, sum(extendedPrice) as sumprice13 from tableSales where salesTime between '2013-01-01' and '2013-12-31' group by salesName -第二次查询(2014销售员销售总额) select sale

=>背景介绍

->tableSales表

SqlServer

->第一次查询(2013销售员销售总额)

select salesName, sum(extendedPrice) as sumprice13 from tableSales where salesTime between '2013-01-01' and '2013-12-31' group by salesName
SqlServer

->第二次查询(2014销售员销售总额)

select salesName, sum(extendedPrice) as sumprice14 from tableSales where salesTime between '2014-01-01' and '2014-12-31' group by salesName
SqlServer
->如何获得销售员13、14年的销售总额?

A、首先,我想到的就是用union all,但不理想;

select salesName, sum(extendedPrice) as sumprice13, null as sumprice14 from tableSales where salesTime between '2013-01-01' and '2013-12-31' group by salesName
union all
select salesName, null as sumprice13, sum(extendedPrice) as sumprice14 from tableSales where salesTime between '2014-01-01' and '2014-12-31' group by salesName
SqlServer
B最后,通过fredrickhu版主大大的指点,修改如下:

SELECT
a.salesName, a.sumprice13, b.sumprice14
FROM
(SELECT
salesName, SUM(extendedPrice) AS sumprice13
FROM
tableSales
WHERE
salesTime BETWEEN '2013-01-01' AND '2013-12-31'
GROUP BY
salesName
) AS a
INNER JOIN
(SELECT
salesName, SUM(extendedPrice) AS sumprice14
FROM
tableSales
WHERE
salesTime BETWEEN '2014-01-01' AND '2014-12-31'
GROUP BY
salesName
) AS b
ON a.salesName=b.salesName
SqlServer

最后再次感谢fredrickhu大大!谢谢!..风雨兼程...