解决方案 »
- SQL 里面自带的事件跟踪器 如何跟踪到某个IP对某几个表所有的事件
- sql视图查询结果优化求助!
- sp_prepexec
- 高手来解答: 该问题从何而来? 详见内贴
- 筛选字段里面全都是数字的记录,应该怎样实现?
- 求救:sql 2000 sp4 +window 2003 standard sp1临时表进中文的问题
- 将一个记录集的某一列作为一个新的记录集,怎么处理?
- 存储过程中用字段名作为参数应用在UPDATE里的问题
- 我要把excl文件中的两条记录用sel server 7.0导入到sql table 中,如何作?
- 数据的筛选问题。
- SQL 如何将 两个table 的 字段怎么 合并到一张表
- SQL server 2012 包含数据库参数设定
select top 1 * from tablename
where colname like'%68,70%'
order by len(colname) desc
可以like '%65%' and like'%66%' OK。你需求说清楚一点。
create table tb
(
id int identity(1,1) primary key,
sid nvarchar(4000)
)
go
insert into tb select '65,66,67'
union all select '65,66,67'
union all select '66,67'
union all select '65,66'
union all select '65'
union all select '68,69'
union all select '68,70'
union all select '70'
union all select '68,69,70'
create table tb
(
id int identity(1,1) primary key,
sid nvarchar(4000)
)
go
insert into tb select '65,66,67'
union all select '65,66,67'
union all select '66,67'
union all select '65,66'
union all select '65'
union all select '68,69'
union all select '68,70'
union all select '70'
union all select '68,69,70'
go
-- 你可以取下字段串长度最大的,
select top 1 id , sid
from tb where sid like '%65%' -- 输入 65
order by LEN(sid) desc
go
-- 不过象以面的两行数,你要哪一条? 都输入 65
-- 1. '66,65,67'
-- 2. '65,66,67'
如果输入68,70还要自己查看 用两个and like 如果有很多的情况,输入68,69,70,74...........这里就有很多and like了。效率会不会很低。
如果输入68,70还要自己查看 用两个and like 如果有很多的情况,输入68,69,70,74...........这里就有很多and like了。效率会不会很低。select top 1 * from tb where sid like '%68%' and sid like '%70%' order by id 这个结果是68,70,应该是'68,69,70'
不对的。
text varchar(100)
)
GOCREATE FUNCTION f_SplitTable(
@inTable TextTable READONLY,
@delimiter varchar(10)
)
RETURNS @outTable table(text varchar(100), n int, segment varchar(100))
AS
BEGIN WITH cte_split AS (
SELECT text,
1 iStart,
CHARINDEX(',',text) iEnd
FROM @inTable
UNION ALL
SELECT text,
iEnd+1,
CHARINDEX(',',text,iEnd+1)
FROM cte_split
WHERE iEnd <> 0
)
INSERT INTO @outTable
SELECT text,
ROW_NUMBER() OVER(PARTITION BY text ORDER BY iStart) n,
SUBSTRING(text,
iStart,
CASE WHEN iEnd=0 THEN
Len(text)+1-iStart
ELSE
iEnd-iStart
END)
FROM cte_split
ORDER BY text,n
RETURN
END
GODECLARE @testTable AS TextTable,
@searchValues AS TextTableINSERT INTO @testTable
SELECT '65,66,67' UNION ALL
SELECT '66,67' UNION ALL
SELECT '65,66' UNION ALL
SELECT '65' UNION ALL
SELECT '68,69' UNION ALL
SELECT '68,70' UNION ALL
SELECT '70'INSERT INTO @searchValues
SELECT '70' UNION ALL
SELECT '68,70' UNION ALL
SELECT '65' UNION ALL
SELECT '66,67' UNION ALL
SELECT '65,67' UNION ALL
SELECT '67,65'
;
WITH test AS (
SELECT * FROM f_SplitTable(@testTable,',')
),
search AS (
SELECT * FROM f_SplitTable(@searchValues,',')
),
tc AS (
SELECT text,
COUNT(DISTINCT segment) segmentCount
FROM test
GROUP BY text
),
sc AS (
SELECT text value,
COUNT(DISTINCT segment) valueCount
FROM search
GROUP BY text
),
mc AS (
SELECT s.text value,
t.text,
COUNT(DISTINCT s.segment) matchCount
FROM test t
JOIN search s ON t.segment = s.segment
GROUP BY s.text,t.text
),
result AS (
SELECT mc.value,
mc.text,
ROW_NUMBER() OVER(PARTITION BY mc.value ORDER BY tc.segmentCount DESC) Level
FROM mc
JOIN sc ON sc.value = mc.value
JOIN tc ON tc.text = mc.text
WHERE mc.matchCount = sc.valueCount
)
SELECT value, text
FROM result
WHERE level=1
value text
---------- ----------
65 65,66,67
65,67 65,66,67
66,67 65,66,67
67,65 65,66,67
68,70 68,70
70 68,70
如果输入68,70还要自己查看 用两个and like 如果有很多的情况,输入68,69,70,74...........这里就有很多and like了。效率会不会很低。
是不高,看你自己表里面实际存储的字符长度,就是最多存储几个数字。还有表里面实际行数。如果不大。这种方法没问题的。你自己表存储的结构是这样。存储的数据也没啥顺序有啥办法。
CREATE TYPE TextTable AS TABLE(
text varchar(100)
) 这种语法是哪个版本的?
然后将函数内的语句:
1)cte_split 改为对@testTable的操作,合并到外面的 WITH 语句中;
2)去掉INSERT部分;
3)把后一段SELECT移到外面 WITH 语句的 test() 内部。@searchValues 也要做一遍。我就是为了避免贴两遍语句才做成函数的。