select 品名,日期,数量 from table_name union all select '合计','2004-03-03',sum(数量) from table_name where 品名='A' and 日期='2004-03-28'
select 品名,日期,数量 from table_name union all select '合计','2004-03-03',sum(数量) from table_name where 品名='A' and convert(varchar(6),日期,112)='200403'
select 品名,日期,数量 from table_name where 日期=today() union all select 品名,'2004-03-03',sum(数量) from table_name where month() =month(日期) and year(日期)=year() group by 品名
如果你的合计值要在列上显示,可用如下办法: select 品名,日期,数量,合计 as 0 from table_name where 日期='2004-03-03' union all select 品名 ,'2004-03-03',数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名
select 品名,数量,合计 as 0 from table_name where 日期='2004-03-03' union all select 品名 ,数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名
select 品名,数量,sum(数量) from aaa union all select 品名,数量,sum(数量) from table_name where month()=month(日期)
select 品名,数量,'0' as 合计 into #tmpTable from table_name where 日期=datetime update #tmpTable set 合计=(select sum(数量) from table_name where month(datetime)=month(日期) group by 品名) select * from #tmpTable
select 品名,日期,[数量] = (select sum(数量) from table_name where month(日期)=month(datetime) group by 品名) from table_name where 日期='2004-03-03' group by 品名,日期
不好意思,写错一个地方 select 品名,数量,0 as 合计 from table_name where 日期='2004-03-03' union all select 品名 ,数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名
select 品名 ,数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名 出现错误 列 '数量' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
我觉的应该这样写 select 品名 ,0 as 数量 ,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名,数量
sorry,我的修正: select 品名 ,0 as 数量 ,sum(数量) as 合计 from table_name where 日期<='2004-03-03' group by 品名
union all
select '合计','2004-03-03',sum(数量) from table_name where 品名='A' and 日期='2004-03-28'
union all
select '合计','2004-03-03',sum(数量) from table_name where 品名='A' and
convert(varchar(6),日期,112)='200403'
union all
select 品名,'2004-03-03',sum(数量) from table_name where month() =month(日期) and year(日期)=year() group by 品名
union all
select 品名 ,'2004-03-03',数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03'
group by 品名
union all
select 品名 ,数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03'
group by 品名
union all
select 品名,数量,sum(数量) from table_name where month()=month(日期)
select 品名,数量,'0' as 合计 into #tmpTable from table_name where 日期=datetime
update #tmpTable set 合计=(select sum(数量) from table_name
where month(datetime)=month(日期)
group by 品名)
select * from #tmpTable
group by 品名)
from table_name where 日期='2004-03-03'
group by 品名,日期
select 品名,数量,0 as 合计 from table_name where 日期='2004-03-03'
union all
select 品名 ,数量 as 0,sum(数量) as 合计 from table_name where 日期<='2004-03-03'
group by 品名
group by 品名
出现错误
列 '数量' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。
select 品名 ,0 as 数量 ,sum(数量) as 合计 from table_name where 日期<='2004-03-03'
group by 品名,数量
select 品名 ,0 as 数量 ,sum(数量) as 合计 from table_name where 日期<='2004-03-03'
group by 品名