SQL - 比较字符串,而不是整个字符串
问题描述:
select substring(datapath,1,5), COUNT(substring(datapath,1,5)) from batchinfo
where datapath like '%thc%'
group by datapath
having COUNT(substring(datapath,1,5))>1
我试图算多少每个子的有在表中,由于某种原因它计算整个字符串。我究竟做错了什么?SQL - 比较字符串,而不是整个字符串
答
您只需GROUP BY你想算,而不是完整数据路径的子字符串。也不需要在计数上重复子字符串函数。
select substring(datapath,1,5), COUNT(*)
from batchinfo
where datapath like '%thc%'
group by substring(datapath,1,5)
having COUNT(*)>1
答
尝试:
select d, count(*)
from
(
select substring(datapath,1,5) d from batchinfo
where datapath like '%thc%'
)
group by d
having COUNT(*) > 1
+0
我喜欢这个,因为你不重复子字符串 – BlackICE 2010-12-10 19:24:43
你能提供一个简单的例子吗? – Unreason 2010-12-10 19:18:33