CREATE TABLE TBTEST(ID INT, name VARCHAR(10)) INSERT TBTESTSELECT 1 , 'n1' UNION ALL SELECT 2 , 'n2' UNION ALL SELECT 3 , 'n3' DECLARE @VAR VARCHAR(50) SET @VAR='2,1,3'SELECT NAME FROM TBTEST ORDER BY CHARINDEX(LTRIM(ID),@VAR) NAME ---------- n2 n1 n3(所影响的行数为 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)
declare @id varchar(100) set @id='2,1,3' select name from tb order by (ID,@id)
-- ========================================= -- -----------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 * from tb ORDER BY CHARINDEX(LTRIM(ID),@s)ID name ----------- ---------- 2 n2 1 n1 3 n3 go
改一下: charindex掉了 最好加个,号 declare @id varchar(10) set @id=',2,1,3,' select * from tb where charindex(','+ltrim(id)+',',@id)>0 order by charindex(','+ltrim(id)+',',@id)
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 個資料列受到影響) */
--借小麦的数据 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 行) */
这个是我想要的SQL, 但只是结果不对.
晕.这个是我想要的SQL, 但只是结果不对.
-- ========================================= -- -----------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
SELECT NAME FROM TBTEST ORDER BY CASE WHEN ID=2 THEN 0 ELSE 1 END NAME ---------- n2 n1 n3(所影响的行数为 3 行)这样吗,???
--上面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
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 個資料列受到影響) */
CREATE TABLE TBTEST(ID INT, name VARCHAR(10)) INSERT TBTESTSELECT 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 tSELECT @VAR1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- n2,n1,n3(所影响的行数为 1 行)还是这样
in 的结果是按ID排序的.而不是n2, n1, n3 因为ID上有索引,所以我想知道,有没有可能直接得出来. 但如果SQL也要编程才能实现的话,取出来,在程序里排序.也是一样的.谢谢大家的热心解答.
where charindex(','+ltrim(id)+',',@id)>0 order by charindex(','+ltrim(id)+',',@id) 这句不就是按你要求么 ?
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 行)**/
SELECT name FROM tb where charindex(ltrim(id),'213')>0 ORDER BY CHARINDEX(LTRIM(na_id),'213')你这里只有3条数据,刚好搜3条。。我这你可以通用。。你可以搜索其中几条。。按搜索条件排序。。
INSERT TBTESTSELECT 1 , 'n1' UNION ALL
SELECT 2 , 'n2' UNION ALL
SELECT 3 , 'n3' DECLARE @VAR VARCHAR(50)
SET @VAR='2,1,3'SELECT NAME FROM TBTEST ORDER BY CHARINDEX(LTRIM(ID),@VAR)
NAME
----------
n2
n1
n3(所影响的行数为 3 行)
set @id=',2,1,3,'
select * from tb where charindex(','+ltrim(id)+',',@id)>0
order by charindex(','+ltrim(id)+',',@id)
set @id='2,1,3'
select name from tb order by (ID,@id)
-- -----------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 *
from tb
ORDER BY CHARINDEX(LTRIM(ID),@s)ID name
----------- ----------
2 n2
1 n1
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)
select name from tb where id in (2,1,3)
-->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 個資料列受到影響)
*/
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 行)
*/
-- -----------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
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
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
-->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 個資料列受到影響)
*/
INSERT TBTESTSELECT 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 tSELECT @VAR1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
n2,n1,n3(所影响的行数为 1 行)还是这样
因为ID上有索引,所以我想知道,有没有可能直接得出来.
但如果SQL也要编程才能实现的话,取出来,在程序里排序.也是一样的.谢谢大家的热心解答.
order by charindex(','+ltrim(id)+',',@id)
这句不就是按你要求么 ?
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 行)**/
SELECT name FROM tb where charindex(ltrim(id),'213')>0 ORDER BY CHARINDEX(LTRIM(na_id),'213')你这里只有3条数据,刚好搜3条。。我这你可以通用。。你可以搜索其中几条。。按搜索条件排序。。