SELECT IDENTITY(int, 1,1) AS xh,clid , vehid, cardid, name , thid into #aa FROM table order by clid, vehid ,cardid, name , thid
select * from #aa order by xh
select * from #aa order by xh
select count(*) from tablename
where clid<a.clid
or (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and cardid<a.cardid)
or (clid=a.clid and vehid=a.vehid and cardid=a.cardid and name<a.name)
or (clid=a.clid and vehid=a.vehid and cardid=a.cardid and name=a.name and thid<a.thid)
) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid如果对同一个clid,vehid组合,保证cardid,name相同,可以写成:
SELECT (
select count(*) from tablename
where clid<a.clid
or (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and thid<a.thid)
) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid
select count(*) from tablename
where clid<a.clid
or (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and thid<a.thid)
),0) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid
clid vehid cardid name thid10946 10947 青A12936 陈道明 1
10946 10947 青A12936 陈道明 4
10946 10948 青A12930 赵若兵 1
10946 10948 青A12930 赵若兵 4
10947 10948 青A12930 赵若兵 4
10947 10948 青A12930 赵若兵 1
10947 10946 青A12923 饶家洪 1
10947 10946 青A12923 饶家洪 4
10947 10932 青A12907 林师宏 1 要求得到:
xh clid vehid cardid name thid
1 10946 10947 青A12936 陈道明 1
1 10946 10947 青A12936 陈道明 4
2 10946 10948 青A12930 赵若兵 1
2 10946 10948 青A12930 赵若兵 4
1 10947 10932 青A12907 林师宏 1
2 10947 10946 青A12923 饶家洪 1
2 10947 10946 青A12923 饶家洪 4
3 10947 10948 青A12930 赵若兵 4
3 10947 10948 青A12930 赵若兵 1
或
xh clid vehid cardid name thid
1 10946 10947 青A12936 陈道明 1
2 10946 10947 青A12936 陈道明 4
3 10946 10948 青A12930 赵若兵 1
4 10946 10948 青A12930 赵若兵 4
1 10947 10932 青A12907 林师宏 1
2 10947 10946 青A12923 饶家洪 1
3 10947 10946 青A12923 饶家洪 4
4 10947 10948 青A12930 赵若兵 4
5 10947 10948 青A12930 赵若兵 1
2、vehid 不同的一组,从1.2.3排下来
select count(*) from tablename
where (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and thid<a.thid)
),0) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid
第一种不好写!
select count(distinct vehid) from tablename
where (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and thid<a.thid)
),0) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid
SELECT 1+isnull((
select count(distinct vehid) from tablename
where clid=a.clid and vehid<a.vehid
),0) as xh,clid , vehid, cardid, name , thid into FROM tablename a
order by clid, vehid ,cardid, name , thid
CCEO()的第二种方法是:
SELECT 1+isnull((
select count(*) from v_qf_ht_dbtz
where (clid=a.clid and vehid<a.vehid)
or (clid=a.clid and vehid=a.vehid and htid<a.htid)
),0) as xh,clid , vehid, cardid, name , htid FROM v_qf_ht_dbtz a
order by clid, vehid ,cardid, name , htid
SELECT 1+isnull((
select count(distinct vehid) from v_qf_ht_dbtz
where clid=a.clid and vehid<a.vehid
),0) as xh,clid , vehid, cardid, name , htid FROM v_qf_ht_dbtz a
order by clid, vehid ,cardid, name , htid
getrow() - first(getrow() for group 1) + 1
select (select count(1)+1 from (select vehid from v_qf_ht_dbtz where clid=b.clid group by vehid) as a
where a.vehid<b.vehid) as xh,clid,vehid,cardid,name,htid,qysj
from v_qf_ht_dbtz b