;WITH split --拆分关键字
as
(
SELECT c.id,
c.keywords,
f.col
FROM @carrule c
CROSS apply dbo.fn_splitSTR(c.keywords,'、') f
)
--3.
SELECT s.id,
s.keywords
FROM split s
INNER JOIN
(
SELECT s.id,
s.keywords,
count(col) AS split_str_count --拆分成了几个关键字
FROM split s
GROUP BY s.id,
s.keywords
) ss
ON s.id = ss.id
WHERE charindex(s.col,'违反规定停放、临时停车且驾驶人不在现场或驾驶人虽在现场拒绝立即驶离，妨碍其他车辆、行人通行的就能获取到') > 0
GROUP BY s.id,
s.keywords
HAVING count(*) = max(ss.split_str_count) --比如第一条记录拆分成了2个关键词，那么在匹配时要2个都匹配上了，才算为匹配
2、统一改换查询出的字段。。这是不是想多了？
能不能这样
select A.* as A_*
from QAQuestion Q
inner join QAAnswer A ON A.QuestionID = Q.ID
简单地说，不想一个个地去给每个字段as别名
我如上去写只是打个比方。。实际运行不了的，想得到的查询结果是
A_字段1,A_字段2,A_字段3,A_字段4
有没有办法呢？
我的回复：
本质上来说，只有在sql server端，能把select a.* as a_*,也就是自动进行转换，才能支持。
因为在sql server端，你写的sql语句是各式各样的，要想实现你的A.* as A_*，实际上就是要改写查询，改为：
select a.字段1 as a_字段1，
a.字段2 as a_字段2，
a.字段3 as a_字段3，
from a
下面是通过动态语句来实现的：
[sql]
--先建个表
select * into wc_table
from sys.objects
/*
要实现
select a.* as a_*
from wc_table
的效果
*/
--动态生成语句为:
declare @sql varchar(max);
set @sql = '';
select @sql = @sql + ',' + c.name + ' as A_' + c.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = 'wc_table'
order by c.column_id
set @sql = 'select ' +
STUFF(@sql,1,1,'') +
' from wc_table A'
select @sql
/*
我把结果格式化了一下就是这样：
SELECT name AS A_name,
object_id AS A_object_id,
principal_id AS A_principal_id,
schema_id AS A_schema_id,
parent_object_id AS A_parent_object_id,
type AS A_type,
type_desc AS A_type_desc,
create_date AS A_create_date,
modify_date AS A_modify_date,
is_ms_shipped AS A_is_ms_shipped,
is_published AS A_is_published,
is_schema_published AS A_is_schema_published
FROM wc_table A
*/
exec(@sql)