接受伴水建议 select code from table union select left(code,1) as code from table group by left(code,1) union select left(code,3) as code from table group by left(code,3) union select left(code,6) as code from table group by left(code,6) union select left(code,9) as code from table group by left(code,9)
并且也一定要汇总地
现举例: id code 姓名 工资 级别
... 402002 老大 5600.00 2
... 402001002 赵一 1200.00 1
... 402001001001 张三 1500.00 1
... 402001001001 李四 2500.00 2
... 402001001001 王五 1800.00 1
..............
现要求:统计结果为
code 级别 工资
4 1 8200
4 2 0
402 1 8200
402 0 0
402001 1 7000
402001 2 0
402002 1 1200
402001001 1 3300
402001001 2 2500
402001002 1 1200
402001001001 1 3300
402001001001 2 2500
现我已部分用存储过程 了,可要写太多的存储过程了
各位大虾有没有高招啊
先谢了
select code from table
union
select left(code,1) as code from table group by left(code,1)
union
select left(code,3) as code from table group by left(code,3)
union
select left(code,6) as code from table group by left(code,6)
union
select left(code,9) as code from table group by left(code,9)