create table tb(col1 int, col2 varchar(10), col3 varchar(10) , col4 int)
insert into tb values( 1 , 'A' , '2,3', 1 )
insert into tb values( 2 , 'B' , '3' , 1 )
insert into tb values( 3 , 'C' , '0' , 1 )
insert into tb values( 4 , 'D' , '1,2', 0 )
insert into tb values( 5 , 'E' , '0' , 0 )
goselect col1 , col2, [sign] = 1 from tb where col4 = 1
union all
select n.col1 , n.col2 , [sign] = 0 from
(select * from tb where col4 = 1) m,
(select * from tb where col4 = 1) n
where charindex(','+ cast(n.col1 as varchar) + ',' , ',' + m.col3 + ',') > 0drop table tb/*
col1 col2 sign
----------- ---------- -----------
1 A 1
2 B 1
3 C 1
2 B 0
3 C 0
3 C 0(所影响的行数为 6 行)
*/
insert into tb values( 1 , 'A' , '2,3', 1 )
insert into tb values( 2 , 'B' , '3' , 1 )
insert into tb values( 3 , 'C' , '0' , 1 )
insert into tb values( 4 , 'D' , '1,2', 0 )
insert into tb values( 5 , 'E' , '0' , 0 )
goselect col1 , col2, [sign] = 1 from tb where col4 = 1
union all
select n.col1 , n.col2 , [sign] = 0 from
(select * from tb where col4 = 1) m,
(select * from tb where col4 = 1) n
where charindex(','+ cast(n.col1 as varchar) + ',' , ',' + m.col3 + ',') > 0drop table tb/*
col1 col2 sign
----------- ---------- -----------
1 A 1
2 B 1
3 C 1
2 B 0
3 C 0
3 C 0(所影响的行数为 6 行)
*/
create table tb(col1 int, col2 varchar(10), col3 varchar(10) , col4 int)
insert into tb values( 1 , 'A' , '2,3', 1 )
insert into tb values( 2 , 'B' , '3' , 1 )
insert into tb values( 3 , 'C' , '0' , 1 )
insert into tb values( 4 , 'D' , '1,2', 0 )
insert into tb values( 5 , 'E' , '0' , 0 )
goselect col1 , col2, [sign] = 1 from tb where col4 = 1
union all
select n.col1 , n.col2 , [sign] = 0 from
(select * from tb where col4 = 1) m,
(select * from tb where col4 = 1) n
where charindex(','+ cast(n.col1 as varchar) + ',' , ',' + m.col3 + ',') > 0
order by col1drop table tb/*
col1 col2 sign
----------- ---------- -----------
1 A 1
2 B 1
2 B 0
3 C 1
3 C 0
3 C 0(所影响的行数为 6 行)
*/
UNION ALL
SELECT b.*,[sign]=0 FROM dbo.tables as a
INNER JOIN
dbo.tables as b
ON b.col1 IN(
(CAST(LEFT(a.col3,1) AS INT)),(CAST(SUBSTRING(a.col3,3,1) AS INT)))
WHERE a.col4=1
CROSS JOIN 交叉 万一 记录 很大 就糟糕了不过我的 也效率不太好 就最佳的方案
/*
col1 col2 sign
----------- ---------- -----------
1 A 1
2 B 1
2 B 0
3 C 0
3 C 0
3 C 1(所影响的行数为 6 行)
*/
use tempdb;
IF OBJECT_ID('dbo.tables') IS NOT NULL
DROP TABLE dbo.tables
GO
CREATE TABLE dbo.tables
(
col1 INT NOT NULL,
col2 VARCHAR(1) NOT NULL,
col3 VARCHAR(10) NOT NULL,
col4 INT
)
INSERT INTO dbo.tables
SELECT 1,'A','2,3',1
UNION ALL
SELECT 2,'B','3',1
UNION ALL
SELECT 3,'C','0',1
UNION ALL
SELECT 4,'D','1,2',0
UNION ALL
SELECT 5,'E','0',0
SELECT *,[sign]=1 FROM dbo.tables WHERE col4=1
UNION ALL
SELECT b.*,[sign]=0 FROM dbo.tables as a
INNER JOIN
dbo.tables as b
ON b.col1 IN(
(CAST(LEFT(a.col3,1) AS INT)),(CAST(SUBSTRING(a.col3,3,1) AS INT)))
WHERE a.col4=1
insert into tb values( 1 , 'A' , '2,3', 1 )
insert into tb values( 2 , 'B' , '3' , 1 )
insert into tb values( 3 , 'C' , '0' , 1 )
insert into tb values( 4 , 'D' , '1,2', 0 )
insert into tb values( 5 , 'E' , '0' , 0)
goselect col1 , col2, [sign] = 1 from tb where col4 = 1
union all
select n.col1 , n.col2 , [sign] = 0 from
(select * from tb where col4 = 1) m,
(select * from tb where col4 = 1) n
where charindex(','+ cast(n.col1 as varchar) + ',' , ',' + m.col3 + ',') > 0
order by col1drop table tb
结果应该是:
/*
col1 col2 sign
----------- ---------- -----------
1 A 1
2 B 1
2 B 0
3 C 0
3 C 0
3 C 1(所影响的行数为 6 行)
*/
刚才引用错了
select tt.*,[sign] =(Case when tt.col4=1 then 1 else 0 end ) from tb as tt ,
(select t.col3,t.col1 from tb as t where t.col4=1)
as ttt
where
( charindex(','+ cast(tt.col1 as varchar) + ',' , ',' + ttt.col3 + ',') > 0 or ttt.col1=tt.col1
)