数据结构
表<data_ydxx_s>
收货日期 datetime
运单编号 nvarchar
收货人 nvarchar
往来单位 nvarchar
终点站 nvarchar
货号 nvarchar
月结 money
回单结 money
未付中转费 money表<data_hwlb_s>
运单编号 nvarchar
货物名称 nvarchar表<data_fclb>
运单编号 nvarchar
发车日期 datetime我写的是SQL语句,只能列表出来,怎么写个汇总数啊,即要显示明细,又要显示金额的汇总SELECT
data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称,
data_ydxx_s.月结,
data_ydxx_s.回单结,
(data_ydxx_s.回单结+data_ydxx_s.月结) as 应收款,
data_ydxx_s.未付中转费 as 应付款
FROM data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'
order by data_ydxx_s.收货日期,data_ydxx_s.货号
表<data_ydxx_s>
收货日期 datetime
运单编号 nvarchar
收货人 nvarchar
往来单位 nvarchar
终点站 nvarchar
货号 nvarchar
月结 money
回单结 money
未付中转费 money表<data_hwlb_s>
运单编号 nvarchar
货物名称 nvarchar表<data_fclb>
运单编号 nvarchar
发车日期 datetime我写的是SQL语句,只能列表出来,怎么写个汇总数啊,即要显示明细,又要显示金额的汇总SELECT
data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称,
data_ydxx_s.月结,
data_ydxx_s.回单结,
(data_ydxx_s.回单结+data_ydxx_s.月结) as 应收款,
data_ydxx_s.未付中转费 as 应付款
FROM data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'
order by data_ydxx_s.收货日期,data_ydxx_s.货号
解决方案 »
- SQL语句简单问题
- access数据库行转列问题,简单,速度来人啦
- sql server2008怎么导出所有作业的脚本?
- 仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 '#' 中为标识列指定显式值。
- 域内无法访问SQLServer服务器
- 30分请教一个逻辑常量在SQL语法中的表达式!
- 关于包含字符'-'的ORDER BY问题
- SQL数据库再次被注入,紧急求助!.网站使用ASP程序!
- 多条数据同时触发
- asp如何连接本地 sql server 数据库??(确实急的问题)
- ReportingServices2005中自定义用户权限问题!?
- 数据分组的问题,超难。。送100分
SELECT
data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称,
data_ydxx_s.月结,
data_ydxx_s.回单结,
(data_ydxx_s.回单结+data_ydxx_s.月结) as 应收款,
data_ydxx_s.未付中转费 as 应付款
FROM data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'
group by data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称 WITH ROLLUP
Try:
SELECT
data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称,
sum(data_ydxx_s.月结) as 月结,
sum(data_ydxx_s.回单结) as 回单结,
sum(data_ydxx_s.回单结+data_ydxx_s.月结) as 应收款,
sum(data_ydxx_s.未付中转费) as 应付款
FROM data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'
group by data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称 WITH ROLLUP
SELECT
data_ydxx_s.收货日期,
data_ydxx_s.收货人,
data_ydxx_s.终点站,
data_ydxx_s.货号,
data_hwlb_s.货物名称,
data_ydxx_s.月结,
data_ydxx_s.回单结,
(data_ydxx_s.回单结+data_ydxx_s.月结) as 应收款,
data_ydxx_s.未付中转费 as 应付款
FROM data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'
order by data_ydxx_s.收货日期,data_ydxx_s.货号
union all
select
null,
'合计',
'',
'',
'',
sum(data_ydxx_s.月结),
sum(data_ydxx_s.回单结),
sum(data_ydxx_s.回单结+data_ydxx_s.月结),
sum(data_ydxx_s.未付中转费)
from data_fclb INNER JOIN (data_ydxx_s INNER JOIN data_hwlb_s ON data_ydxx_s.运单编号=data_hwlb_s.运单编号)ON data_fclb.运单编号=data_ydxx_s.运单编号
WHERE (data_fclb.发车日期 BETWEEN '2007-06-09' AND '2007-06-30') AND (data_ydxx_s.月结<>0 OR data_ydxx_s.回单结<>0 OR data_ydxx_s.未付中转费<>0) AND data_ydxx_s.往来单位='大康'