22,209
社区成员
发帖
与我相关
我的任务
分享
SELECT name FROM tb where charindex(ltrim(id),'213')>0 ORDER BY CHARINDEX(LTRIM(na_id),'213')
select * from test where id = 2
union all
select * from test where id = 1
union all
select * from test where id = 3
/**
ID name
----------- ----------
2 n2
1 n1
3 n3
(所影响的行数为 3 行)
**/
CREATE TABLE TBTEST(ID INT, name VARCHAR(10))
INSERT TBTEST
SELECT 1 , 'n1' UNION ALL
SELECT 2 , 'n2' UNION ALL
SELECT 3 , 'n3'
DECLARE @VAR VARCHAR(50)
SET @VAR='2,1,3'
DECLARE @VAR1 nVARCHAR(2000)
SELECT @VAR1=ISNULL(@VAR1+',','')+NAME FROM (select top 2000 name from TBTEST ORDER BY charindex(ltrim(id),@var))as t
SELECT @VAR1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n2,n1,n3
(所影响的行数为 1 行)
--> Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-18 15:02:52
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[name] nvarchar(2))
Insert tb
select 1,N'n1' union all
select 2,N'n2' union all
select 3,N'n3'
Go
declare @id varchar(10)
set @id=',2,1,3,'
declare @s nvarchar(1000)
select * into # from tb where charindex(','+ltrim(id)+',',@id)>0
order by charindex(','+ltrim(id)+',',@id)
select @s=isnull(@s+',','')+[name] from #
select @s
drop table #
/*
----------------------
n2,n1,n3
(1 個資料列受到影響)
*/
--上面13楼市2005的方法
--这个是2000用函数的方法
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(ID int, name varchar(10))
go
insert tb SELECT
1, 'n1' UNION ALL SELECT
2, 'n2' UNION ALL SELECT
3, 'n3'
go
CREATE FUNCTION dbo.f_tb(@k int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + name FROM ko WHERE k=@k
RETURN STUFF(@str, 1, 1, '')
END
declare @s varchar(40)
set @s='2, 1, 3'
select name,1 as k
into ko
from tb
ORDER BY CHARINDEX(LTRIM(ID),@s)
select dbo.f_tb(1)
go
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n2,n1,n3
SELECT NAME FROM TBTEST ORDER BY CASE WHEN ID=2 THEN 0 ELSE 1 END
NAME
----------
n2
n1
n3
(所影响的行数为 3 行)
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(ID int, name varchar(10))
go
insert tb SELECT
1, 'n1' UNION ALL SELECT
2, 'n2' UNION ALL SELECT
3, 'n3'
go
declare @s varchar(40)
set @s='2, 1, 3'
select STUFF(
(select top 1000 ','+name
from tb
ORDER BY CHARINDEX(LTRIM(ID),@s) for XML path ('') ),1,1,'')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n2,n1,n3
go
--借小麦的数据
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(ID int, name varchar(10))
go
insert tb SELECT
1, 'n1' UNION ALL SELECT
2, 'n2' UNION ALL SELECT
3, 'n3'
go
declare @id varchar(10)
set @id=',2,1,3,'
select * from tb where charindex(','+ltrim(id)+',',@id)>0
order by charindex(','+ltrim(id)+',',@id)
/*ID name
----------- ----------
2 n2
1 n1
3 n3
(所影响的行数为 3 行)
*/
--> Title:生成測試數據
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-18 15:02:52
if not object_id('tb') is null
drop table tb
Go
Create table tb([ID] int,[name] nvarchar(2))
Insert tb
select 1,N'n1' union all
select 2,N'n2' union all
select 3,N'n3'
Go
declare @id varchar(10)
set @id=',2,1,3,'
select * from tb where charindex(','+ltrim(id)+',',@id)>0
order by charindex(','+ltrim(id)+',',@id)
/*
ID name
----------- ----
2 n2
1 n1
3 n3
(3 個資料列受到影響)
*/
select name from tb where id in (2,1,3)
declare @id varchar(10)
set @id=',2,1,3,'
select * from tb where charindex(','+ltrim(id)+',',@id)>0
order by charindex(','+ltrim(id)+',',@id)