请教SQL求和语句？

[zpingfang]

请教给位老师，我想求每个商品是数量乘以价格，然后最后把总金额相加得到总数，请问应该怎么写呢？

[snlixing]

select pinming,zongjia from tb group by pinming with rollup

[吉普赛的歌]

就按你的， “一条”语句也能搞定， 不过需要比较高的版本 SQL Server2012+：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	pinming NVARCHAR(10),
	shuliang INT,
	jiage INT,
	zongjine AS shuliang*jiage	--计算列
)
GO
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('包子',5,2)
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('水饺',10,2)
---- 以上为测试表及测试数据 ------


--方法1：用计算列
SELECT 
CASE WHEN pinming IS NOT NULL THEN pinming ELSE '合计' END AS pinming
,CASE WHEN pinming IS NOT NULL THEN MAX(shuliang) ELSE NULL END AS shuliang
,CASE WHEN pinming IS NOT NULL THEN MAX(jiage) ELSE NULL END AS jiage
,sum(zongjine) AS zongjine 
FROM t group by rollup(pinming) 

--方法2：不用计算列
SELECT 
CASE WHEN pinming IS NOT NULL THEN pinming ELSE '合计' END AS pinming
,CASE WHEN pinming IS NOT NULL THEN MAX(shuliang) ELSE NULL END AS shuliang
,CASE WHEN pinming IS NOT NULL THEN MAX(jiage) ELSE NULL END AS jiage
,sum(shuliang*jiage) AS zongjine 
FROM t group by rollup(pinming) 

[zpingfang]

请教各位老师

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	pinming NVARCHAR(10),
	shuliang INT,
	jiage INT,
	zongjine AS shuliang*jiage	--计算列
)
GO
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('包子',5,2)
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('水饺',10,2)

--用计算列
SELECT * FROM t
UNION ALL
SELECT '合计' AS pinming,NULL,NULL,SUM(zongjine)
FROM t

--不用计算列
;WITH cte AS (
	SELECT pinming,shuliang,jiage,shuliang*jiage AS zongjine FROM t
)
SELECT * FROM cte
UNION ALL
SELECT '合计' AS pinming,NULL,NULL,SUM(zongjine)
FROM cte

[zpingfang]

引用 2 楼 yenange 的回复:

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	pinming NVARCHAR(10),
	shuliang INT,
	jiage INT,
	zongjine AS shuliang*jiage	--计算列
)
GO
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('包子',5,2)
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('水饺',10,2)

--用计算列
SELECT * FROM t
UNION ALL
SELECT '合计' AS pinming,NULL,NULL,SUM(zongjine)
FROM t

--不用计算列
;WITH cte AS (
	SELECT pinming,shuliang,jiage,shuliang*jiage AS zongjine FROM t
)
SELECT * FROM cte
UNION ALL
SELECT '合计' AS pinming,NULL,NULL,SUM(zongjine)
FROM cte

我是小白，老师好像是先乘以后求和，老师能不能用一条查询语句来执行吗？

[zpingfang]

引用 7 楼 yenange 的回复:

就按你的， “一条”语句也能搞定， 不过需要比较高的版本 SQL Server2012+：

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	pinming NVARCHAR(10),
	shuliang INT,
	jiage INT,
	zongjine AS shuliang*jiage	--计算列
)
GO
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('包子',5,2)
INSERT INTO t(pinming,shuliang,jiage)  VALUES ('水饺',10,2)
---- 以上为测试表及测试数据 ------


--方法1：用计算列
SELECT 
CASE WHEN pinming IS NOT NULL THEN pinming ELSE '合计' END AS pinming
,CASE WHEN pinming IS NOT NULL THEN MAX(shuliang) ELSE NULL END AS shuliang
,CASE WHEN pinming IS NOT NULL THEN MAX(jiage) ELSE NULL END AS jiage
,sum(zongjine) AS zongjine 
FROM t group by rollup(pinming) 

--方法2：不用计算列
SELECT 
CASE WHEN pinming IS NOT NULL THEN pinming ELSE '合计' END AS pinming
,CASE WHEN pinming IS NOT NULL THEN MAX(shuliang) ELSE NULL END AS shuliang
,CASE WHEN pinming IS NOT NULL THEN MAX(jiage) ELSE NULL END AS jiage
,sum(shuliang*jiage) AS zongjine 
FROM t group by rollup(pinming) 

老师，我用下面这条语句能实现求和，但是如何在最后一行加一个合计呢？ SELECT pinming,SUM(shuliang) AS shuliang,jiage,SUM(shuliang*jiage) as zongjine,max(riqi) AS riqi FROM assj GROUP BY pinming

[吉普赛的歌]

你先说下你用的是sqlserver的什么版本？

[zpingfang]

继续请教

[吉普赛的歌]

SELECT pinming,
       MAX(shuliang)          AS shuliang,
       MAX(jiage)             AS jiage,
       SUM(shuliang * jiage)  AS zongjine,
       MAX(riqi)              AS riqi
FROM   assj
GROUP BY ROLLUP(pinming)

最简单就是 :

SELECT pinming,
       SUM(shuliang * jiage)  AS zongjine
FROM   assj
GROUP BY ROLLUP(pinming)

如果你想深入学习SQL Server， 那就不要用 Navicat Premium。
最好是 SQL Server 自带的客户端 SSMS

还有你查一下你的 SQL Server 版本：

SELECT @@VERSION;

贴出结果截图或文本。

[二月十六]

select 
case when pinming is not null then pinming else '合计' end as pinming,
sum(shuliang) as shuliang,
sum(jiage) as jiage,
sum(shuliang*jiage) as jine 
from assj 
group by pinming with rollup

[ermuzi]

SELECT pinming,shuliang,jiage,shuliang*jiage AS zongjine FROM t UNION ALL SELECT '合计' AS pinming,NULL,NULL,SUM(shuliang*jiage) FROM t