SELECT d.id, COUNT(b.id) AS Expr1, SUM(a.number1) AS Expr2
FROM (SELECT projectid, COUNT(Id) AS number1
FROM CardInfo
GROUP BY projectid) a RIGHT OUTER JOIN
project b ON a.projectid = b.projectid INNER JOIN
sort d ON b.ismine = d.id
GROUP BY d.id WITH rollup id expr1 expr2
1 15 96
2 522 21043
3 37 1516
4 31 1335
5 49 63
6 76 908
7 98 2453
8 36 388
10 428 7052
12 8 246
13 1
1301 35100这是结果,还有一个表(sort),
id int 4 0
sortname varchar 50 1
这里的id对应出来是上一个结果的,然后显示sortname列(结果按照id排序)?
sortname expr1 expr2
s1 15 96
s2 522 21043
s3 37 1516
s4 31 1335
s5 49 63
s6 76 908
s7 98 2453
s8 36 388
s10 428 7052
s12 8 246
s3 1
1301 35100我想象中的是select d.sortname,c.expr1,c.expr2 from
(SELECT d.id, COUNT(b.id) AS Expr1, SUM(a.number1) AS Expr2
FROM (SELECT projectid, COUNT(Id) AS number1
FROM CardInfo
GROUP BY projectid) a RIGHT OUTER JOIN
project b ON a.projectid = b.projectid INNER JOIN
sort d ON b.ismine = d.id
GROUP BY d.id WITH rollup) c ,sort d
where c.id=d.id
order by c.id
但是WITH rollup始终不行,问怎么写这个sql
????
FROM (SELECT projectid, COUNT(Id) AS number1
FROM CardInfo
GROUP BY projectid) a RIGHT OUTER JOIN
project b ON a.projectid = b.projectid INNER JOIN
sort d ON b.ismine = d.id
GROUP BY d.id WITH rollup id expr1 expr2
1 15 96
2 522 21043
3 37 1516
4 31 1335
5 49 63
6 76 908
7 98 2453
8 36 388
10 428 7052
12 8 246
13 1
1301 35100这是结果,还有一个表(sort),
id int 4 0
sortname varchar 50 1
这里的id对应出来是上一个结果的,然后显示sortname列(结果按照id排序)?
sortname expr1 expr2
s1 15 96
s2 522 21043
s3 37 1516
s4 31 1335
s5 49 63
s6 76 908
s7 98 2453
s8 36 388
s10 428 7052
s12 8 246
s3 1
1301 35100我想象中的是select d.sortname,c.expr1,c.expr2 from
(SELECT d.id, COUNT(b.id) AS Expr1, SUM(a.number1) AS Expr2
FROM (SELECT projectid, COUNT(Id) AS number1
FROM CardInfo
GROUP BY projectid) a RIGHT OUTER JOIN
project b ON a.projectid = b.projectid INNER JOIN
sort d ON b.ismine = d.id
GROUP BY d.id WITH rollup) c ,sort d
where c.id=d.id
order by c.id
但是WITH rollup始终不行,问怎么写这个sql
????
解决方案 »
- 一个小问题
- 救命啊....Sql server2005 CLR function 返回值超过 varchar(8000)的限制?
- 登录卡在了 这句【If mrc.EOF Then ...】代码上......
- liangCK大人请进!
- 求一句简单的SQL语句
- sql里,字段的类型一个用char 一个用int,两个的值都是"123",他们所占的空间一样吗?
- 请教各位:自动产生日期字段
- 两个问题(10分)
- 大家好,DB2数据库的存储过程构建时要设置VC编译器的环境,请问如何设置(急!急!急!急!)
- 有关数据库设计。请教大家!
- 在线求助:SQL SERVER2000远程访问与VPN路由器设置
- 求合并表的SQL语句(200多张结构一样的表)
SELECT max(d.sortname) as sortname, /*选择d.sortname*/
COUNT(b.id) AS Expr1, SUM(a.number1) AS Expr2
FROM (SELECT projectid, COUNT(Id) AS number1
FROM CardInfo
GROUP BY projectid) a RIGHT OUTER JOIN
project b ON a.projectid = b.projectid INNER JOIN
sort d ON b.ismine = d.id
GROUP BY d.id WITH rollup
ORDER BY d.id /*按d.id排序*/