select top 10 n.pname , m.username from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 order by m.date desc
--1 select top 10 n.pname , m.username from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 order by m.date desc --2select top 10 n.pname , m.username from [user] m , product n where ',' + n.uid + ',' like '%,' + cast(m.userid as varchar) + ',%' order by m.date desc
select top 10 * from [User] a where exists(select 1 from Product where charindex(','+ltrim(a.userId)+',',','+uId+',')>0) order by date desc
select top 10 username,pname from user m,product n where charindex(','+m.userid+',',','+n.uid+',')>0 order by date desc
select top 10 n.pname , m.username from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 and m.date = (select max(date) from [user] t where t.userid = m.userid) order by m.date descselect top 10 n.pname , m.username from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 and not exists (select 1 from [user] t where t.userid = m.userid and date > m.date) order by m.date desc
或者这样. select t1.* from ( select n.pname , m.username , m.date from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 ) t1 where date = (select max(date) from ( select n.pname , m.username , m.date from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 ) t2 where t2.pname = t1.name)select t1.* from ( select n.pname , m.username , m.date from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 ) t1 where not exists (select 1 from ( select n.pname , m.username , m.date from [user] m , product n where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 ) t2 where t2.pname = t1.name and t2.date > t1.date)
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
order by m.date desc
select top 10 n.pname , m.username
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
order by m.date desc
--2select top 10 n.pname , m.username
from [user] m , product n
where ',' + n.uid + ',' like '%,' + cast(m.userid as varchar) + ',%'
order by m.date desc
select top 10 * from [User] a
where exists(select 1 from Product where charindex(','+ltrim(a.userId)+',',','+uId+',')>0)
order by date desc
select top 10 username,pname
from user m,product n
where charindex(','+m.userid+',',','+n.uid+',')>0
order by date desc
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 and
m.date = (select max(date) from [user] t where t.userid = m.userid)
order by m.date descselect top 10 n.pname , m.username
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0 and
not exists (select 1 from [user] t where t.userid = m.userid and date > m.date)
order by m.date desc
返回字符串中指定表达式的起始位置。
语法
CHARINDEX ( e­xpression1 , e­xpression2 [ , start_location ] )
参数
e­xpression1
一个表达式,其中包含要寻找的字符的次序。e­xpression1 是一个短字符数据类型分类的表达式。
e­xpression2
一个表达式,通常是一个用于搜索指定序列的列。e­xpression2 属于字符串数据类型分类。
start_location
在 e­xpression2 中搜索 e­xpression1 时的起始字符位置。如果没有给定 start_location,而是一个负数或零,则将从 e­xpression2 的起始位置开始搜索。
返回类型
int
注释
如果 e­xpression1 或 e­xpression2 之一属于 Unicode 数据类型(nvarchar 或 nchar)而另一个不属于,则将另一个转换为 Unicode 数据类型。
如果 e­xpression1 或 e­xpression2 之一为 NULL 值,则当数据库兼容级别为 70 或更大时,CHARINDEX 返回 NULL 值。当数据库兼容级别为 65 或更小时,CHARINDEX 仅在 e­xpression1 和 e­xpression2 都为 NULL 时返回 NULL 值。
如果在 e­xpression2 内没有找到 e­xpression1,则 CHARINDEX 返回 0。
-----------------------------------------
例一:
CustomName包含客户的First Name和Last Name,它们之间被一个空格隔开。我们用CHARINDX函数确定两个名字中间空格的位置。通过这个方法,我们可以分析ContactName列的空格位置,这样可以只显示这个列的last name部分。
select top 5 substring(ContactName,charindex(' ',ContactName)+1,len(ContactName)) as [Last Name] from customers
CHARINDEX函数找到First Name和Last Name之间的空格,所以SUBSTRING函数可以分开ContactName列,这样就只有Last Name被选出。在CHARINDEX函数返回的整数上加1,这样Last Name不是从空格开始。
例二:
计算Northwind.dbo.Customer表中Addresses字段中包含单词Road或者它的缩写Rd的记录数,选择语句类似这样:
Select count(*) from Northwind.dbo.Customers
Where CHARINDEX('Rd',Address) > 0 or CHARINDEX('Road',Address)> 0
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281
select t1.* from
(
select n.pname , m.username , m.date
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
) t1 where date = (select max(date) from
(
select n.pname , m.username , m.date
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
) t2 where t2.pname = t1.name)select t1.* from
(
select n.pname , m.username , m.date
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
) t1 where not exists (select 1 from
(
select n.pname , m.username , m.date
from [user] m , product n
where charindex(',' + cast(m.userid as varchar) + ',' , ',' + n.uid + ',') > 0
) t2 where t2.pname = t1.name and t2.date > t1.date)