--零条目数是个啥东西?
create table tb(djbh varchar(20),dchw varchar(20),ckjwh varchar(20),dj_sort int)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-02', 39)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 53)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-14', 65)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-12', 61)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 56)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 54)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 30)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-01', 33)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 51)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'Z' , 12)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 31)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-08', 46)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 48)
goselect djbh , dchw , left(ckjwh,2) 分组ckjwh, count(*) 页数 , min(dj_sort) min_sort , max(dj_sort) max_sort from tb group by djbh , dchw , left(ckjwh,2)drop table tb/*
djbh dchw 分组ckjwh 页数 min_sort max_sort
-------------------- -------------------- ------- ----------- ----------- -----------
CDBABC00077351 HWIABC00007 Z 1 12 12
CDBABC00077351 HWIABC00007 ZD 10 33 65
CDBABC00077351 HWIABC00008 ZC 2 30 31(所影响的行数为 3 行)
*/
create table tb(djbh varchar(20),dchw varchar(20),ckjwh varchar(20),dj_sort int)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-02', 39)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 53)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-14', 65)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-12', 61)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 56)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 54)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 30)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-01', 33)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 51)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'Z' , 12)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 31)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-08', 46)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 48)
goselect djbh , dchw , left(ckjwh,2) 分组ckjwh, count(*) 页数 , min(dj_sort) min_sort , max(dj_sort) max_sort from tb group by djbh , dchw , left(ckjwh,2)drop table tb/*
djbh dchw 分组ckjwh 页数 min_sort max_sort
-------------------- -------------------- ------- ----------- ----------- -----------
CDBABC00077351 HWIABC00007 Z 1 12 12
CDBABC00077351 HWIABC00007 ZD 10 33 65
CDBABC00077351 HWIABC00008 ZC 2 30 31(所影响的行数为 3 行)
*/
解决方案 »
- 两个sql很难。请高手写出来!谢谢!
- 储存过程问题
- sql 关于 identity的问题~~~ 急待解决
- 高手救命, 关于数据库的导出
- 求教各位,是关于SQL Server 安装的一个问题,急
- 能否通过某种手段,在添加记录时,让SQL SERVER通知程序(由自己开发)?各位帮忙。
- 一个数据库的处理解决问题??
- 怎么在存储过程中用sql语句创建一个指定在sql server服务器上路径,指定名称的文件夹?
- 一个难对应的考勤明细表的存储过程(400分的题,另开页加分300;完成后再加分,这个表对我来说太重要了,请来帮忙)
- 如何插入带有单引号或双引号的数据?
- 怎么插入两条数据啊。。
- 又是一个SQL server连oracle的问题,折腾我好久了!!
from table
group by djbh,dchw,left(ckjwh,2)
order by ....
create table tb(djbh varchar(20),dchw varchar(20),ckjwh varchar(20),dj_sort int)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-02', 39)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 53)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-14', 65)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-12', 61)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 56)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-10', 54)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 30)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-01', 33)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 51)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'Z' , 12)
insert into tb values('CDBABC00077351', 'HWIABC00008', 'ZC2-07', 31)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-08', 46)
insert into tb values('CDBABC00077351', 'HWIABC00007', 'ZD1-09', 48)
goselect djbh , dchw , left(ckjwh,2) 分组ckjwh, case when count(*)%8 = 0 then count(*)/8 else count(*)/8 + 1 end 页数 , count(*)%8 零条目数 , min(dj_sort) min_sort , max(dj_sort) max_sort from tb group by djbh , dchw , left(ckjwh,2)drop table tb/*
djbh dchw 分组ckjwh 页数 零条目数 min_sort max_sort
-------------------- -------------------- ------- ----------- ----------- ----------- -----------
CDBABC00077351 HWIABC00007 Z 1 1 12 12
CDBABC00077351 HWIABC00007 ZD 2 2 33 65
CDBABC00077351 HWIABC00008 ZC 1 2 30 31(所影响的行数为 3 行)
*/