SELECT myid,VALUE
FROM (
SELECT myid,SUM(VALUE) AS VALUE,2 AS TYPE FROM TABLENAME
GROUP BY myid
UNION ALL
SELECT LEFT(myid,2) AS myid,SUM(VALUE) AS VALUE,1 AS TYPE FROM TABLENAME
GROUP BY LEFT(myid,2)
) AS X
ORDER BY LEFT(myid,2),TYPE,myid
FROM (
SELECT myid,SUM(VALUE) AS VALUE,2 AS TYPE FROM TABLENAME
GROUP BY myid
UNION ALL
SELECT LEFT(myid,2) AS myid,SUM(VALUE) AS VALUE,1 AS TYPE FROM TABLENAME
GROUP BY LEFT(myid,2)
) AS X
ORDER BY LEFT(myid,2),TYPE,myid
CREATE TABLE TABLENAME(
myid VARCHAR(20),
value INT
)
GOINSERT TABLENAME
SELECT
'151', 2
UNION ALL
SELECT
'152', 5
UNION ALL
SELECT
'152', 1
UNION ALL
SELECT
'152', 3
UNION ALL
SELECT
'163', 1
UNION ALL
SELECT
'163', 4
UNION ALL
SELECT
'164', 7
UNION ALL
SELECT
'164', 2
select left(myid,2) myid,sum(value) value from 你的表 group by left(myid,2)
union all
select myid,sum(value) from 你的表 group by myid
) tem order by myid
SELECT CASE WHEN (GROUPING(id1) = 1) THEN 'ALL'
ELSE ISNULL(id1, 'UNKNOWN')
END AS myid1,
CASE WHEN (GROUPING(id2) = 1) THEN 'ALL'
ELSE ISNULL(id2, 'UNKNOWN')
END AS myid2,
SUM(value) AS QtySum
FROM
(select case when len(myid)=2 then myid else '' end as id1 ,
case when len(myid)=3 then myid else '' end as id2 ,value from 表 ) a GROUP BY id1, id2 WITH ROLLUP
15 <<=====这一记录事先存在吗?
151 2
152 5
152 1
152 3
^^^在你的库里这里有没有空格
select ltrim(left(myid,2)) myid,sum(value) value from 你的表 group by left(myid,2)
union all
select myid,sum(value) from 你的表 group by myid) tem order by myid
select " "+myid myid, sum(value) value, myid id from yourtable group by myid
union
select left(myid, 2), sum(value), left(myid, 2) from yourtable group by left(myid,2)
) as A
order by myid
15 <<=====这一记录事先存在吗?
151 2
152 5
152 1
152 3
^^^在你的库里这里有没有空格15事先不存在
没有空格,空格是我为了体现结构自己加的myid的长度事先未知,由用户自己输入的
谢谢各位了
15
是存放在另一个表中的
另一个表保存了所有的myid和结构