select name,year(issuetime),sum(price) from tabelname group by name,year(issuetime)
select sum(price),year(IssueTime),name from tablename group by year(IssueTime),name
SQL: select year(issuetime),name,sum(price) from tabelname group by year(issuetime),nameORACLE: select to_char(issuetime,'yyyy'),name,sum(price) from tabelname group by to_char(issuetime,'yyyy'),name
用access生成SQL语句是很方便的
劳驾dearfws(方文书)详细说明,并举例
select year(issuetime),name,sum(price) from tabelname group by year (issuetime),name得到的结果集后只能得到一个‘竖’的表,我想得到的是一个 名称 | year1总金额 | year2总金额 | year3总金额 |... xx | 2132 | 43211 | 322532 |... 的二维统计表格,不知各位高手有没有办法?
这个问题有很多种方法!第一种是 select name, (select sum(price) from tablename where year(issuetime)=1999 and name=c.name), (select sum(price) from tablename where year(issuetime)=2000 and name=c.name) from tablename group by name 如果年也不定! 那要分两层! 先求出年份在进行操作! 1.结果放如临时表 aa select year(issuetime),name,sum(price) from tabelname group by year (issuetime),name 2.然后对临时表操作 select name, (select sum(price) from tablename where year(issuetime)=1999 and name=c.name), (select sum(price) from tablename where year(issuetime)=2000 and name=c.name) from tablename group by name 还可以用case when then else 解决 你自己想以下好了
to 疑难杂症:select语句运行出错,说c是非法前缀,其实c是什么意思,我用的是SQLserver
SELECT name, sum(CASE year(issuetime) WHEN '1999' THEN price ELSE 0 END) AS 1999, sum(CASE year(issuetime) WHEN '2000' THEN price ELSE 0 END) AS 2000, sum(CASE year(issuetime) WHEN '2001' THEN price ELSE 0 END) AS 2001 from tablename group by name
对不起!那时数据库别名! 这个问题有很多种方法!第一种是 select name, (select sum(price) from tablename where year(issuetime)=1999 and name=c.name), (select sum(price) from tablename where year(issuetime)=2000 and name=c.name) from tablename as c group by name 如果年也不定! 那要分两层! 先求出年份在进行操作! 1.结果放如临时表 aa select year(issuetime),name,sum(price) from tabelname group by year (issuetime),name 2.然后对临时表操作 select name, (select sum(price) from tablename where year(issuetime)=1999 and name=c.name), (select sum(price) from tablename where year(issuetime)=2000 and name=c.name) from aa as c group by name 还可以用case when then else 解决 你自己想以下好了 ysb的方法也是对的! 但是你要仅仅用于sql server数据库中!
group by name,year(issuetime)
from tablename
group by year(IssueTime),name
select year(issuetime),name,sum(price) from tabelname
group by year(issuetime),nameORACLE:
select to_char(issuetime,'yyyy'),name,sum(price) from tabelname
group by to_char(issuetime,'yyyy'),name
名称 | year1总金额 | year2总金额 | year3总金额 |...
xx | 2132 | 43211 | 322532 |...
的二维统计表格,不知各位高手有没有办法?
select name,
(select sum(price) from tablename where year(issuetime)=1999 and name=c.name),
(select sum(price) from tablename where year(issuetime)=2000 and name=c.name)
from tablename
group by name
如果年也不定!
那要分两层!
先求出年份在进行操作!
1.结果放如临时表 aa
select year(issuetime),name,sum(price)
from tabelname
group by year (issuetime),name
2.然后对临时表操作
select name,
(select sum(price) from tablename where year(issuetime)=1999 and name=c.name),
(select sum(price) from tablename where year(issuetime)=2000 and name=c.name)
from tablename
group by name
还可以用case when then else 解决
你自己想以下好了
sum(CASE year(issuetime) WHEN '1999' THEN price ELSE 0 END) AS 1999,
sum(CASE year(issuetime) WHEN '2000' THEN price ELSE 0 END) AS 2000,
sum(CASE year(issuetime) WHEN '2001' THEN price ELSE 0 END) AS 2001
from tablename
group by name
这个问题有很多种方法!第一种是
select name,
(select sum(price) from tablename where year(issuetime)=1999 and name=c.name),
(select sum(price) from tablename where year(issuetime)=2000 and name=c.name)
from tablename as c
group by name
如果年也不定!
那要分两层!
先求出年份在进行操作!
1.结果放如临时表 aa
select year(issuetime),name,sum(price)
from tabelname
group by year (issuetime),name
2.然后对临时表操作
select name,
(select sum(price) from tablename where year(issuetime)=1999 and name=c.name),
(select sum(price) from tablename where year(issuetime)=2000 and name=c.name)
from aa as c
group by name
还可以用case when then else 解决
你自己想以下好了
ysb的方法也是对的!
但是你要仅仅用于sql server数据库中!