为什么
select case when rn/10=0 then FModel else '' end FModel,
right(max(case when rn%10=0 then MakeNO else '' end),5) as MakeNO01,
right(max(case when rn%10=1 then MakeNO else '' end),5) as MakeNO02,
right(max(case when rn%10=2 then MakeNO else '' end),5) as MakeNO03,
right(max(case when rn%10=3 then MakeNO else '' end),5) as MakeNO04,
right(max(case when rn%10=4 then MakeNO else '' end),5) as MakeNO05,
right(max(case when rn%10=5 then MakeNO else '' end),5) as MakeNO06,
right(max(case when rn%10=6 then MakeNO else '' end),5) as MakeNO07,
right(max(case when rn%10=7 then MakeNO else '' end),5) as MakeNO08,
right(max(case when rn%10=8 then MakeNO else '' end),5) as MakeNO09,
right(max(case when rn%10=9 then MakeNO else '' end),5) as MakeNO10
from(
select MakeNO,FModel,
rn=(select count(1) from tb where FModel=t.FModel and MakeNO<t.MakeNO)
from tb t where headid=24 ) a
group by a.FModel,LEFT(MakeNO,4),SUBSTRING(MakeNO,5,2),a.rn/10 我加了个where条件后 出来的结果就不是连续的 而是有空白的?
select case when rn/10=0 then FModel else '' end FModel,
right(max(case when rn%10=0 then MakeNO else '' end),5) as MakeNO01,
right(max(case when rn%10=1 then MakeNO else '' end),5) as MakeNO02,
right(max(case when rn%10=2 then MakeNO else '' end),5) as MakeNO03,
right(max(case when rn%10=3 then MakeNO else '' end),5) as MakeNO04,
right(max(case when rn%10=4 then MakeNO else '' end),5) as MakeNO05,
right(max(case when rn%10=5 then MakeNO else '' end),5) as MakeNO06,
right(max(case when rn%10=6 then MakeNO else '' end),5) as MakeNO07,
right(max(case when rn%10=7 then MakeNO else '' end),5) as MakeNO08,
right(max(case when rn%10=8 then MakeNO else '' end),5) as MakeNO09,
right(max(case when rn%10=9 then MakeNO else '' end),5) as MakeNO10
from(
select MakeNO,FModel,
rn=(select count(1) from tb where FModel=t.FModel and MakeNO<t.MakeNO)
from tb t where headid=24 ) a
group by a.FModel,LEFT(MakeNO,4),SUBSTRING(MakeNO,5,2),a.rn/10 我加了个where条件后 出来的结果就不是连续的 而是有空白的?
WHEN rn/10=0 THEN FModel
ELSE ''
END FModel,
RIGHT(MAX(CASE WHEN rn%10=0 THEN MakeNO ELSE '' END), 5) AS MakeNO01,
RIGHT(MAX(CASE WHEN rn%10=1 THEN MakeNO ELSE '' END), 5) AS MakeNO02,
RIGHT(MAX(CASE WHEN rn%10=2 THEN MakeNO ELSE '' END), 5) AS MakeNO03,
RIGHT(MAX(CASE WHEN rn%10=3 THEN MakeNO ELSE '' END), 5) AS MakeNO04,
RIGHT(MAX(CASE WHEN rn%10=4 THEN MakeNO ELSE '' END), 5) AS MakeNO05,
RIGHT(MAX(CASE WHEN rn%10=5 THEN MakeNO ELSE '' END), 5) AS MakeNO06,
RIGHT(MAX(CASE WHEN rn%10=6 THEN MakeNO ELSE '' END), 5) AS MakeNO07,
RIGHT(MAX(CASE WHEN rn%10=7 THEN MakeNO ELSE '' END), 5) AS MakeNO08,
RIGHT(MAX(CASE WHEN rn%10=8 THEN MakeNO ELSE '' END), 5) AS MakeNO09,
RIGHT(MAX(CASE WHEN rn%10=9 THEN MakeNO ELSE '' END), 5) AS MakeNO10
FROM (
SELECT MakeNO, FModel, rn = (
SELECT COUNT(1)
FROM tb
WHERE FModel = t.FModel
AND MakeNO<t.MakeNO
AND headid = 24 --加上这个
)
FROM tb t
WHERE headid = 24
) a
GROUP BY a.FModel, LEFT(MakeNO, 4), SUBSTRING(MakeNO, 5, 2), a.rn/10
rn=(select count(1) from tb where headid=24 and FModel=t.FModel and MakeNO<t.MakeNO)
from tb t where headid=24
我现在得到了想要的数据 但是我想加上 主键查询不是 就是根据headid 得到了结果 里面就有空白的地方不连贯。
WHEN rn/10=0 THEN FModel
ELSE ''
END FModel,
RIGHT(MAX(CASE WHEN rn%10=0 THEN MakeNO ELSE '' END), 5) AS MakeNO01,
RIGHT(MAX(CASE WHEN rn%10=1 THEN MakeNO ELSE '' END), 5) AS MakeNO02,
RIGHT(MAX(CASE WHEN rn%10=2 THEN MakeNO ELSE '' END), 5) AS MakeNO03,
RIGHT(MAX(CASE WHEN rn%10=3 THEN MakeNO ELSE '' END), 5) AS MakeNO04,
RIGHT(MAX(CASE WHEN rn%10=4 THEN MakeNO ELSE '' END), 5) AS MakeNO05,
RIGHT(MAX(CASE WHEN rn%10=5 THEN MakeNO ELSE '' END), 5) AS MakeNO06,
RIGHT(MAX(CASE WHEN rn%10=6 THEN MakeNO ELSE '' END), 5) AS MakeNO07,
RIGHT(MAX(CASE WHEN rn%10=7 THEN MakeNO ELSE '' END), 5) AS MakeNO08,
RIGHT(MAX(CASE WHEN rn%10=8 THEN MakeNO ELSE '' END), 5) AS MakeNO09,
RIGHT(MAX(CASE WHEN rn%10=9 THEN MakeNO ELSE '' END), 5) AS MakeNO10
FROM (
SELECT MakeNO, FModel, rn = (
SELECT COUNT(1)
FROM tb
WHERE FModel = t.FModel
AND MakeNO<t.MakeNO
AND headid = 24 --加上这个
)
FROM tb t
WHERE headid = 24
) a
GROUP BY CASE
WHEN rn/10=0 THEN FModel
ELSE ''
END, LEFT(MakeNO, 4), SUBSTRING(MakeNO, 5, 2), a.rn/10这样试试,不对再把最后, LEFT(MakeNO, 4), SUBSTRING(MakeNO, 5, 2), a.rn/10去掉试试
47 23 2.2.2.07 20100600024 铜热电阻 Cu50WZC--020--50+150
48 23 2.2.2.07 20100600025 铜热电阻 Cu50WZC--020--50+150
49 23 2.2.2.07 20100600026 铜热电阻 Cu50WZC--020--50+150
50 23 2.2.2.07 20100600027 铜热电阻 Cu50WZC--020--50+150
51 23 2.2.2.07 20100600028 铜热电阻 Cu50WZC--020--50+150
52 23 2.2.2.07 20100600029 铜热电阻 Cu50WZC--020--50+150
53 23 2.2.2.07 20100600030 铜热电阻 Cu50WZC--020--50+150
54 23 2.2.2.07 20100600031 铜热电阻 Cu50WZC--020--50+150
55 24 2.2.2.07 20100600032 铜热电阻 Cu50WZC--020--50+150
56 24 2.2.2.07 20100600033 铜热电阻 Cu50WZC--020--50+150
57 24 2.2.2.07 20100600034 铜热电阻 Cu50WZC--020--50+150
58 24 2.2.2.07 20100600035 铜热电阻 Cu50WZC--020--50+150
59 24 2.2.2.07 20100600036 铜热电阻 Cu50WZC--020--50+150
60 24 2.2.2.07 20100600037 铜热电阻 Cu50WZC--020--50+150
61 24 2.2.2.08 20100600050 E型热电偶芯 L=150 φ27
62 24 2.2.2.08 20100600051 E型热电偶芯 L=150 φ27
63 24 2.2.2.08 20100600052 E型热电偶芯 L=150 φ27
64 24 2.2.2.07 20100600016 铜热电阻 Cu50WZC--020--50+150
65 24 2.1.1.1 20100500100 防水耐高温电缆 AFF46/260 8*1.5
66 24 2.1.1.1 20100500108 防水耐高温电缆 AFF46/260 8*1.5
67 24 2.1.1.1 20100500105 防水耐高温电缆 AFF46/260 8*1.5
68 24 2.1.1.1 20100500106 防水耐高温电缆 AFF46/260 8*1.5
69 24 2.1.1.1 20100500109 防水耐高温电缆 AFF46/260 8*1.5
70 24 2.1.1.1 20100500121 防水耐高温电缆 AFF46/260 8*1.5
71 24 2.1.1.1 20100500115 防水耐高温电缆 AFF46/260 8*1.5
72 24 2.1.1.1 20100500116 防水耐高温电缆 AFF46/260 8*1.5
73 24 2.1.1.1 20100500135 防水耐高温电缆 AFF46/260 8*1.5
74 24 2.1.1.1 20100500142 防水耐高温电缆 AFF46/260 8*1.5
75 24 2.1.1.1 20100500110 防水耐高温电缆 AFF46/260 8*1.5
76 24 2.1.1.1 20100500111 防水耐高温电缆 AFF46/260 8*1.5
77 24 2.1.1.1 20100500112 防水耐高温电缆 AFF46/260 8*1.5
78 24 2.2.2.07 20100601038 铜热电阻 Cu50WZC--020--50+150
79 24 2.2.2.07 20100601039 铜热电阻 Cu50WZC--020--50+150
80 24 2.2.2.07 20100601040 铜热电阻 Cu50WZC--020--50+150
81 24 2.1.1.1 20100501105 防水耐高温电缆 AFF46/260 8*1.5
82 24 2.1.1.1 20100501106 防水耐高温电缆 AFF46/260 8*1.5
83 24 2.1.1.1 20100501109 防水耐高温电缆 AFF46/260 8*1.5
84 24 2.1.1.1 20100501121 防水耐高温电缆 AFF46/260 8*1.5
85 24 2.1.1.1 20100501115 防水耐高温电缆 AFF46/260 8*1.5
86 24 2.1.1.1 20100501116 防水耐高温电缆 AFF46/260 8*1.5
87 24 2.1.1.1 20100501135 防水耐高温电缆 AFF46/260 8*1.5
88 24 2.1.1.1 20100501142 防水耐高温电缆 AFF46/260 8*1.5
89 24 2.1.1.1 20100501110 防水耐高温电缆 AFF46/260 8*1.5
90 24 2.1.1.1 20100501111 防水耐高温电缆 AFF46/260 8*1.5
91 24 2.1.1.1 20100501112 防水耐高温电缆 AFF46/260 8*1.5
92 24 2.2.2.07 20100601038 铜热电阻 Cu50WZC--020--50+150
93 24 2.2.2.07 20100601039 铜热电阻 Cu50WZC--020--50+150
94 24 2.2.2.07 20100601040 铜热电阻 Cu50WZC--020--50+150数据库里的数据
left(MakeNO,4) 年份 ,substring(MakeNO,5,2) 月份,
max(case when rn%10=0 then right(MakeNO,5) else '' end) as MakeNO01,
max(case when rn%10=1 then right(MakeNO,5) else '' end) as MakeNO02,
max(case when rn%10=2 then right(MakeNO,5) else '' end) as MakeNO03,
max(case when rn%10=3 then right(MakeNO,5) else '' end) as MakeNO04,
max(case when rn%10=4 then right(MakeNO,5) else '' end) as MakeNO05,
max(case when rn%10=5 then right(MakeNO,5) else '' end) as MakeNO06,
max(case when rn%10=6 then right(MakeNO,5) else '' end) as MakeNO07,
max(case when rn%10=7 then right(MakeNO,5) else '' end) as MakeNO08,
max(case when rn%10=8 then right(MakeNO,5) else '' end) as MakeNO09,
max(case when rn%10=9 then right(MakeNO,5) else '' end) as MakeNO10
from(
select MakeNO,FModel,headid,
rn=(select count(1) from tb where headid=t.headid and FModel=t.FModel and MakeNO<t.MakeNO)
from tb t ) a
where headid=24
group by a.FModel,left(MakeNO,4),substring(MakeNO,5,2),a.rn/10
order by a.FModel,a.rn/10难道可能是这样