select fdname1 from tbname where fdname1 in('a','c','b','k') ORDER BY CASE fdname1 WHEN 'a' then 1 WHEN 'c' then 2 WHEN 'b' then 3 WHEN 'k' then 4 end
为什么不在IN中按需要的顺序排列 ORDER BY fdname1
上百项算不算多? 当然是由程序生成的语句。 我的语句大概如下: Select fd1, sum(fd2) from tbname where fd1 in ('d','b','c') Group by fd1 Union Select '-', sum(fd2) from tbname order by fd1 现在要求按照d,b,c的顺序排列 怎么改来改去都出错阿
select * from ( Select fd1,sum(fd2) fd2 from tbname where fd1 in ('d','b','c') Group by fd1Union Select '-' as fd1 , sum(fd2) fd2 from tbname ) aorder by case fd1 when 'd' then 1 when 'b' then 2 when 'c' then 3 end
这样就比较通用了。:) select fdname1 from tbname where fdname1 in('a','c','b','k') group by charindex(fdname1,'''a'',''c'',''b'',''k''')
to CrazyFor: 这样出错: SELECT name, SUM(number) FROM spt_values WHERE name IN ('pub', 'rpc') GROUP BY name UNION SELECT '-', SUM(number) FROM spt_values WHERE name IN ('pub', 'rpc') ORDER BY CHARINDEX(name, '''pub'',''rpc''') 没有union就可以
还有,用case when和charindex哪一个效率高?
这样改就对了: SELECT name, SUM(number), CHARINDEX(name, '''pub'',''rpc''') AS o FROM spt_values WHERE name IN ('pub', 'rpc') GROUP BY name UNION SELECT '-', SUM(number), '9' FROM spt_values WHERE name IN ('pub', 'rpc') ORDER BY o 但我不想要那个o显示,给客户看了总是莫名其妙,怎么办? 谢谢阿
SELECT name, SUM(number) FROM spt_values WHERE name IN ('pub', 'rpc') GROUP BY name UNION SELECT '-', SUM(number), '9' FROM spt_values WHERE name IN ('pub', 'rpc') ORDER BY CHARINDEX(name, '''pub'',''rpc''')
更正一下: select fdname1 from tbname where fdname1 in('a','c','b','k') order by charindex(''''+fdname1+'''','''a'',''c'',''b'',''k''')
SELECT name, SUM(number) FROM spt_values WHERE name IN ('pub', 'rpc') GROUP BY name UNION SELECT '-', SUM(number) FROM spt_values WHERE name IN ('pub', 'rpc') ORDER BY CHARINDEX(name, '''pub'',''rpc''')
可以排序了,结贴。 to CrazyFor: 排序正常了,但还是有点显示的小问题,请继续(100分): http://expert.csdn.net/Expert/topic/1792/1792390.xml?temp=.3494684
ORDER BY CASE fdname1 WHEN 'a' then 1
WHEN 'c' then 2
WHEN 'b' then 3
WHEN 'k' then 4
end
ORDER BY fdname1
当然是由程序生成的语句。
我的语句大概如下:
Select fd1,
sum(fd2)
from tbname
where fd1 in ('d','b','c')
Group by fd1
Union
Select '-',
sum(fd2)
from tbname
order by fd1
现在要求按照d,b,c的顺序排列
怎么改来改去都出错阿
from (
Select fd1,sum(fd2) fd2 from tbname where fd1 in ('d','b','c')
Group by fd1Union
Select '-' as fd1 , sum(fd2) fd2 from tbname ) aorder by
case fd1 when 'd' then 1
when 'b' then 2
when 'c' then 3
end
select fdname1 from tbname where fdname1 in('a','c','b','k')
group by charindex(fdname1,'''a'',''c'',''b'',''k''')
这样出错:
SELECT name, SUM(number)
FROM spt_values
WHERE name IN ('pub', 'rpc')
GROUP BY name
UNION
SELECT '-', SUM(number)
FROM spt_values
WHERE name IN ('pub', 'rpc')
ORDER BY CHARINDEX(name, '''pub'',''rpc''')
没有union就可以
SELECT name, SUM(number), CHARINDEX(name, '''pub'',''rpc''') AS o
FROM spt_values
WHERE name IN ('pub', 'rpc')
GROUP BY name
UNION
SELECT '-', SUM(number), '9'
FROM spt_values
WHERE name IN ('pub', 'rpc')
ORDER BY o
但我不想要那个o显示,给客户看了总是莫名其妙,怎么办?
谢谢阿
FROM spt_values
WHERE name IN ('pub', 'rpc')
GROUP BY name
UNION
SELECT '-', SUM(number), '9'
FROM spt_values
WHERE name IN ('pub', 'rpc')
ORDER BY CHARINDEX(name, '''pub'',''rpc''')
select fdname1 from tbname where fdname1 in('a','c','b','k')
order by charindex(''''+fdname1+'''','''a'',''c'',''b'',''k''')
FROM spt_values
WHERE name IN ('pub', 'rpc')
GROUP BY name
UNION
SELECT '-', SUM(number)
FROM spt_values
WHERE name IN ('pub', 'rpc')
ORDER BY CHARINDEX(name, '''pub'',''rpc''')
to CrazyFor:
排序正常了,但还是有点显示的小问题,请继续(100分):
http://expert.csdn.net/Expert/topic/1792/1792390.xml?temp=.3494684